An error log is something that is a critical tool to both development and to troubleshoot errors in production. When everything runs well, it’s a tool that sits in the background without a second thought. Using web push notifications, email, or some other medium, alerts can be pushed out to staff when the unexpected errors occur.
By default, my web server wasn’t configured to log errors. I configured the server to not only log errors, but also log them in a place that I could access. phpinfo will often convey how error logging is configured.
- display_errors: Off
- display_startup_errors: Off
- error_append_string: no value
- error_log: error_log
- error_log_mode: 0644
- error_prepend_string: no value
- error_reporting: 22527
- html_errors: On
- ignore_repeated_errors: Off
- ignore_repeated_source: Off
- log_errors: Off
- xmlrpc_error_number: 0
- xmlrpc_errors: Off
- intl.error_level: 0
- intl.use_exceptions: Off
- opcache.error_log: no value
Error Log Prompt: Microsoft Designer
show how a web server is able to parse errors from a centralized log file. Each entry contains a timestamp, error type, message, file, and line number. Some entries contain a stack-trace outlining classes and methods of where the exception was thrown from that includes file names and line number. Great care was taken to allow the system to scale by only reading a small part of the error log, rather than the entire file, as it quickly grew to almost one gigabyte during testing. A database was also created that normalized the error logs into multiple tables for more optimization. The focus should be that there are a ton of errors and warnings to sort through and manage by engineers. The errors are stored in one giant database dedicated to holding nothing else except errors. Errors usually have icons like stop signs, red letter “X”, a red exclamation sign, or a yellow triangle with a black exclamation for warnings.
We start off with modifying the php.ini file. Hostinger does not permit me to do this directly with a shared host, but they have setup a web interface to modify a few fields.
With the recent server transfer, in addition to resetting my PHP extensions to their default values, it looks like they reset all of my PHP options as well. Luckily I hadn’t noticed as I override these settings in my .htaccess file. However, let’s go ahead and reconfigure them.
We want to turn on logErrors and change errorReporting to E_ALL. Scroll down to the bottom and save the settings.
By default, the PHP system logger puts error logs in a folder that I can’t access. I needed to configure where the errors are saved by overriding the error_log directive. You can do this with an .htaccess file in your websites root folder. As a temporary solution, I logged them as .error_log.txt files. This ends up creating a .error_log.txt file in many folders. If an error occurred in /secrets/get.php, it would create a /secrets/.error_log.txt file. I added an extra safeguard so that any file that began with a dot couldn’t be requested.
ErrorLog error.log php_flag log_errors on php_value error_reporting 32767 # E_ALL php_value error_log ".error_log.txt" # Prevent accessing any file beginning wit a dot <FilesMatch "^\."> Order allow,deny Deny from all </FilesMatch>
It’s been convenient tracking down errors with this method. However, I’ve come to a situation where it’s not as convenient as it once was. Since the logs can be fairly spammy with various warnings, I have to keep deleting the error logs to clear them and diagnose the current issue. I’m also getting a ton of deprecation warnings that I’d like to ignore, but I’m still interested in any other warnings that may pop up.
PHP Error Log
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:46 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:30:47 UTC] PHP Warning: Undefined variable $total in /home/u1/domains/periplux.io/public_html/dev-api/secrets/transfer-keys.php on line 119
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:01 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:12 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:12 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:34:12 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:07 UTC] PHP Fatal error: Uncaught Error: Call to undefined method Secrets::key() in /home/u1/domains/periplux.io/public_html/dev-api/secrets/transfer-keys.php:96
Stack trace:
#0 /home/u1/domains/periplux.io/public_html/dev-api/secrets/transfer-keys.php(199): restore_keys()
#1 {main}
thrown in /home/u1/domains/periplux.io/public_html/dev-api/secrets/transfer-keys.php on line 96
[19-Jun-2024 10:38:30 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
[19-Jun-2024 10:38:30 UTC] PHP Deprecated: Creation of dynamic property Memcache::$connection is deprecated in /home/u1/domains/periplux.io/public_html/dev-api/common/Secrets.php on line 185
It’s time to move the logs to a database.
Can I setup a global hander with .htaccess to execute a php script to process the errors? No. Logs are either written to files, or not at all. You can’t setup a global handler to process them as they occur. You can manually setup an php file to handle errors that is required by all php scripts to handle errors in-process.
Rather than distributing error logs all throughout the file system, let’s consolidate the errors to one place.
php_value error_log "/home/u1/domains/periplux.io/errors/dev-api.txt"
Our error logs are now stored outside of our public_html folder as one file. As an added measure, I’ve setup the configuration to be controlled by the build servers secrets since development and production save to a different file, and may have two different levels of error reporting.
- name: Set Environemnt in .htaccess
run: |
echo "" >> .htaccess
echo "# CONTENT APPENDED BY BUILD SERVER" >> .htaccess
echo "" >> .htaccess
echo "php_flag log_errors ${{vars.DEV_ERROR_LOGGED }}" >> .htaccess
echo "php_value error_log \"${{vars.DEV_ERROR_FILE }}\"" >> .htaccess
echo "php_value error_reporting ${{vars.DEV_ERROR_TYPES }}" >> .htaccess
That’s a start. Before moving them to be stored within a database, let’s parse what we’ve got in the file and create a simple viewer. Problem… PHP doesn’t know where the errors are located. We can expose that as an environment variable:
echo "SetEnv ERROR_LOG \"${{vars.DEV_ERROR_FILE }}\"" >> .htaccess
Now we are ready. Error logs can be fairly big. Let’s assume that our file is 1 GB in size. We can’t read it into memory. Let’s read it line-by line and setup pagination. PHP has a few methods that allow us to work with large files without using a great deal of memory that let us jump to a specific part of a file and read a line of text. (fopen, fseek, feof, fgets, ftell, feof, fclose)
PHP: Read N lines from large files
<?php
require_once "../common/Show.php";
$path = getenv("ERROR_LOG");
$offset = isset($_GET["offset"]) ? (int) $_GET["offset"] : 1;
$size = isset($_GET["size"]) ? (int) $_GET["size"] : 10;
$file_size = filesize($path);
if ($offset > $file_size) {
Show::error("Offset greater than fize size: $file_size bytes");
exit;
}
$file = fopen($path, "r");
fseek($file, $offset);
$lines = [];
for ($i = 0;!feof($file) && $i < $size; $i++) {
$line = fgets($file);
if ($line === false) {
break;
}
$lines[] = $line;
}
$next_offset = ftell($file);
$has_more = !feof($file);
fclose($file);
Show::data([
'lines' => $lines,
'offset' => $offset,
'next_offset' => $next_offset,
'has_more' => $has_more,
]);
The script that I setup allows me to request a set number of lines starting at a specific byte position. The response not only provides me with the lines, but also lets me know if there are more lines available, and the offset to begin at.
Usually with pagination, you are working with page and size parameters. Due to the nature of the log files format, we don’t have fixed width records where we can randomly jump to a specific record.
This leads into the next problem. Some logs take up multiple lines (ie stack trace). We now need to identify where an individual log begins and ends. We could just assume that any line that begins with a date within brackets signals a new log. Can the subsequent lines within the same error log also be prefixed with dates? This may be where error_prepend_string and error_append_string come into play. Let’s try to leave the error log looking as normal as possible before prepending/appending it. Someone else may be reading it later and be thrown off with odd tags.
Somehow I ended up with an infinite loop and a 900 MB log file for failing to supply a valid regular expression. Horrible. It’s not that difficult to run into large log files unintentionally. I had 900 MB of the following repeated error:
[20-Jun-2024 00:43:45 UTC] PHP Warning: preg_match(): No ending delimiter ‘/’ found in /home/u1/domains/periplux.io/public_html/dev-api/errors/view.php on line 25
Eventually I worked out where I went wrong and addressed a few other problems along the way to handle missing error files and offsets out of range. I looked for a pattern of [date_string] error_type: error_message, parsed the date string into a timestamp and trimmed the error type and message. Any lines that didn’t match this format were added as details.
Parse Multi-Line Error Logs
<?php
require_once "../common/Show.php";
$path = getenv("ERROR_LOG");
$offset = isset($_GET["offset"]) ? (int) $_GET["offset"] : 0;
$size = isset($_GET["size"]) ? (int) $_GET["size"] : 10;
if (!file_exists($path)) {
Show::error("Log file does not exist.");
exit;
}
$file_size = filesize($path);
if ($file_size === false) {
Show::error("Unable to get file size of log file.");
exit;
}
if ($offset > $file_size) {
Show::error("Offset greater than fize size: $file_size bytes");
exit;
}
$file = fopen($path, "r");
if ($offset > 0) {
fseek($file, $offset);
}
$logs = [];
$next_offset = ftell($file);
$has_more = !feof($file);
$max = $size * 10; // no more than 10 lines per log on average
for ($i = 0; $i < $max && $has_more; $i++) {
$line = fgets($file);
if ($line === false) {
$next_offset = 0;
$has_more = false;
break;
}
$found_match = $result = preg_match('/^\\[([^\\]]+)\\]\\s*([^:]+):\s*(.*)/', $line, $matches);
if ($found_match !== 0 && $found_match !== false) {
if (count($logs) === $size) {
break;
}
$timestamp = strtotime($matches[1]);
$type = $matches[2];
$message = $matches[3];
if ($type !== null) {
$type = trim($type);
}
if ($message !== null) {
$message = trim($message);
}
$logs[] = [
'timestamp' => $timestamp,
'type' => $type,
'message' => $message,
];
} else if (count($logs) === 0) {
$logs[] = [
'message' => $line,
];
} else {
$last_index = count($logs) - 1;
$last_log = $logs[$last_index];
if (array_key_exists('details', $last_log)) {
$last_log['details'][] = $line;
} else {
$last_log['details'] = [$line];
}
$logs[$last_index] = $last_log;
}
$has_more = !feof($file);
if ($has_more === false) {
$next_offset = 0;
} else {
$next_offset = ftell($file);
}
}
fclose($file);
$result = [
'logs' => $logs,
'has_more' => $has_more,
];
if ($has_more === true) {
$result['next_offset'] = $next_offset;
}
Show::data($result);
Let’s separate the file and line number from the message. I’m seeing a further pattern:
[date_string] error_type: error_message in file_path on line line_number
The regex can be expanded to match this pattern:
preg_match('/^\\[([^\\]]+)\\]\\s*([^:]+):\s*(.*) in (.+) on line (\d+)/', $line, $matches)
The format changes. In some cases, the line number is specified as file_path:line_number. Specifically when an unhandled exception is thrown. This is getting a little crazy, but I can still handle it with the following regular expression:
<?php
$line = '[20-Jun-2024 00:57:12 UTC] PHP Warning: filesize(): stat failed for /home/u1/domains/periplux.io/errors/dev-api.log in /home/u1/domains/periplux.io/public_html/dev-api/errors/view.php on line 7';
$line2 = '[20-Jun-2024 01:15:37 UTC] PHP Fatal error: Uncaught Exception: Testing exception stack trace in log in /home/u1/domains/periplux.io/public_html/dev-api/errors/view.php:87';
$result = preg_match('/^\\[([^\\]]+)\\]\\s*([^:]+):\s*(.*) in (.+)(:(\d+)| on line (\d+))/', $line, $matches);
var_dump($result);
var_dump($matches);
I can evaluate the first character of match 5 and then grab the line number from match 6 or 7 accordingly.
$timestamp = strtotime($matches[1]);
$type = $matches[2];
$message = $matches[3];
$path = $matches[4];
if ($matches[5][0] === ':') {
$line = (int) $matches[6];
} else {
$line = (int) $matches[7];
}
At this point, I’ll be able to normalize the database to have a separate lookup table for file paths and error types. I could make a lookup list for messages as well since many are the same text.
Before we move onto the database, one last part is to extract the stack trace. We are already halfway there with the details containing all multi-line information.
Rather than displaying the stack trace as an array of strings for each line, I decided to combine them as one long string. In the database, this doesn’t necessarily need to be normalized or looked up as a key.
Parse PHP error log as JSON
<?php
require_once "../common/Show.php";
$path = getenv("ERROR_LOG");
$offset = isset($_GET["offset"]) ? (int) $_GET["offset"] : 0;
$size = isset($_GET["size"]) ? (int) $_GET["size"] : 10;
if (!file_exists($path)) {
Show::error("Log file does not exist.");
exit;
}
$file_size = filesize($path);
if ($file_size === false) {
Show::error("Unable to get file size of log file.");
exit;
}
if ($offset > $file_size) {
Show::error("Offset greater than fize size: $file_size bytes");
exit;
}
$file = fopen($path, "r");
if ($offset > 0) {
fseek($file, $offset);
}
$logs = [];
$next_offset = ftell($file);
$has_more = !feof($file);
$in_stack = false;
// Avoid potential infinite loop or excessive memory usage
$max = $size * 10; // no more than 10 lines per log on average
for ($i = 0; $i < $max && $has_more; $i++) {
$line = fgets($file);
if ($line === false) {
$next_offset = 0;
$has_more = false;
break;
}
$found_match = preg_match('/^\\[([^\\]]+)\\]\\s*([^:]+):\s*(.*) in (.+)(:(\d+)| on line (\d+))/', $line, $matches);
if ($found_match !== 0 && $found_match !== false) {
if (count($logs) === $size) {
break;
}
$in_stack = false;
$timestamp = strtotime($matches[1]);
$type = $matches[2];
$message = $matches[3];
$path = $matches[4];
if ($matches[5][0] === ':') {
$line = (int) $matches[6];
} else {
$line = (int) $matches[7];
}
if ($type !== null) {
$type = trim($type);
}
if ($message !== null) {
$message = trim($message);
}
$logs[] = [
'timestamp' => $timestamp,
'type' => $type,
'message' => $message,
'path' => $path,
'line' => $line,
];
} else if (count($logs) === 0) {
$logs[] = [
'message' => $line,
];
} else {
$last_index = count($logs) - 1;
$last_log = $logs[$last_index];
$tag = 'details';
if ($in_stack === false && strpos($line, "Stack trace:") !== false) {
$in_stack = true;
$last_log['stack_trace'] = "";
} else if ($in_stack === true) {
$last_log['stack_trace'] .= $line;
} else if (array_key_exists('details', $last_log)) {
$last_log['details'][] = $line;
} else {
$last_log['details'] = [$line];
}
$logs[$last_index] = $last_log;
}
$has_more = !feof($file);
if ($has_more === false) {
$next_offset = 0;
} else {
$next_offset = ftell($file);
}
}
fclose($file);
$result = [
'logs' => $logs,
'has_more' => $has_more,
];
if ($has_more === true) {
$result['next_offset'] = $next_offset;
}
Show::data($result);
Now we get to design our database. Rather than storing error logs in the main database, let’s just create another small database. Error logs have no need to be aware of the application data (at this time).
After we split out several pieces of data from the log file, our JSON is essentially a table in first normal form – almost. We don’t have the guarantee that each row is unique. We have seven pieces of data – timestamp, type, message, path, line number, and stack trace, and details. All of these values together can be duplicated from the prior error log reported – including the time.
On the path to normalization, I’ve split out the various piece of data into their own tables.
I’ve normalized the logs pretty heavily. If a given error is logged from within an infinite loop, I don’t want the data to grow exponentially for the same error. That 900 MB error log I had earlier could have been easily represented with a few kilobytes of data.
Instead, our logs table just has a few integers that point to the data that’s already been reported previously. I didn’t store id fields to the stack trace and details in the log table since most logs don’t have them. Furthermore, I’ve set it up in a way that multiple logs could reference the same stack trace or details. It’s a rare occurrence, but it could happen that multiple logs have the same stack trace, or a log could have multiple stack traces.
I also setup a few fields for performance. Since the stack trace is a text value, I created a unique hash field to look up the corresponding stack trace. The logs table has first_at and last_at fields to give me a general overview of the logs age. Last, the actual dates are recorded in log_dates with a first_at and last_at as well. A count lets me know how many times the message occurred in the given timeframe. This helps address the infinite loop problem where I could have just incremented a counter when a duplicate record was found.
It’s important to understand the data integrity of our database is at risk. The dates in the logs table could be out of synch with the log_dates. We lost precision of when each log occurred if the first_at and last_at timestamps don’t match in the log_dates table. The stack-trace hash may have a collision with another message, or simply have the incorrect hash for the given text.
With so much risk, why did I do this? There is a mantra or phrase we used to have around the office.
Normalize until it hurts; denormalize until it works
What’s that about? Performance. Mind you, we were dealing with billions of records in a flat file system (Btrieve) with an SQL interface (Pervasive).
Y2K Fix in Flat File Databases
Flat file systems – specifically large ones with billions of records, impose a limitation of adding or removing columns. The total byte size of a record can not be modified without creating a new file. Given the time it takes to add a new column and the downtime of a few data entry centers, each center can cost upwards of $100k/hour of staff waiting for the database to come online. Not to mention that you would need to at least double your available storage space while the old table is transformed into a new table file.
Come around to the time leading up to the Y2K bug where dates were stored in the database as a two-digit number. The cost of adding a new column was out of the question. The same issue applies to modifying a column size. You could theoretically add four numbers instead of two and send it to the database anyway, but it would overflow into the next column. You could change integers from a base-10 decimal system to hex or a higher base system such as 64. However, this would introduce downtime converting all existing data to use the new base system.
The answer was to only change the base system for the decade, and keep numbers 0 to 9 as-is. Once the year 2000 came around, 99 flipped over to A0. The Y2K bug was prolonged the end of 2069 when Z9 wouldn’t have a value to flip over to. Other letters, printable characters, or other binary values could be used to extend the years further. As a counter to the Y2K dates, symbols were introduced to add dates prior to 1900. 1899 may be represented as $9. The system did have a few situations where it actually needed to work with those earlier dates.
When you think about whats in the field, you’ve got two bytes. A 16 bit integer has 65,536 possible unique values. If the system hasn’t upgraded to an RDBMS system yet, there are still many possible ways to represent both the future and the past.
I’ve setup a few stored procedures to assist with logging and viewing errors. There are two specifically for this. sp_log and sp_get_logs. The procedure that creates logs in the database calls on many other procedures to create new values in various tables if they don’t already exist.
MySQL procedure to log error
DROP PROCEDURE IF EXISTS sp_log;
DELIMITER //
CREATE PROCEDURE sp_log (
IN p_timestamp TIMESTAMP,
IN p_type VARCHAR(64),
IN p_message VARCHAR(1024),
IN p_path VARCHAR(1024),
IN p_line INT,
IN p_stack_trace TEXT,
IN p_details TEXT
)
this_proc: BEGIN
DECLARE v_error_message TEXT;
DECLARE v_affected_rows INT;
DECLARE v_detail_id INT;
DECLARE v_stack_trace_id INT;
DECLARE v_path_id INT;
DECLARE v_message_id INT;
DECLARE v_type_id INT;
DECLARE v_log_id INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_error_message = MESSAGE_TEXT;
SELECT v_error_message AS `proc_message`;
END;
CALL sp_get_path_id(p_path, v_path_id);
CALL sp_get_message_id(p_message, v_message_id);
CALL sp_get_type_id(p_type, v_type_id);
CALL sp_get_log_id(
p_timestamp,
v_type_id,
v_message_id,
v_path_id,
p_line,
v_log_id
);
CALL sp_log_details(v_log_id, p_details);
CALL sp_log_stack_trace(v_log_id, p_stack_trace);
CALL sp_log_date(v_log_id, p_timestamp);
END
//
DELIMITER ;
Well… I got most of the procedures worked out to interact with the system and realized that 2038 is coming up soon. I was using FROM_UNIXTIME to convert my BIGINT unix timestamp to a MySQL timestamp. However, I decided to do some poking.
SELECT FROM_UNIXTIME(2147483647); -- 2038-01-19 03:14:07 SELECT FROM_UNIXTIME(2147483648); -- NULL
Well, that isn’t going to work at all. How am I supposed to translate the Unix Timestamp into a MySQL timestamp? Wait… can MySQL timestamps work after 2038?
CREATE TABLE foo (
bar TIMESTAMP
);
INSERT INTO foo (bar) VALUES (2147483648);
INSERT INTO foo (bar) VALUES ('2038-01-19 03:14:08');
select * from foo;
-- 0000-00-00 00:00:00
-- 0000-00-00 00:00:00
If this database is still in use 14 years from now, I’m setting people up for failure. How else can I store dates? What about the MySQL DateTime?
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
bar DATETIME
);
INSERT INTO foo (bar) VALUES (2147483648);
INSERT INTO foo (bar) VALUES ('2038-01-19 03:14:08');
select * from foo;
-- 0000-00-00 00:00:00
-- 2038-01-19 03:14:08
Thank goodness DateTime works. I thought maybe I had to do the math on BIGINT everywhere. Unfortunately it wasn’t able to convert the number into the date time. Actually, it’s unable to convert any number. Maybe I do need to work out the time.
Wait, I think I’m thinking through this a bit too hard. All I’m doing with the timestamp is adding/subtracting 5 seconds. The timestamp is a representation of seconds.
Dates changed. Let’s try with some actual data.
-- Log the error
CALL sp_log(
2147483648,
'PHP Fatal error',
'Uncaught ArgumentCountError: Too few arguments to function Database::__construct(), 0 passed in /home/u1/domains/periplux.io/public_html/dev-api/web-push/chron-notify.php on line 47 and at least 1 expected',
'/home/u1/domains/periplux.io/public_html/dev-api/common/Database.php',
19,
'#0 /home/u1/domains/periplux.io/public_html/dev-api/web-push/chron-notify.php(47): Database->__construct()
#1 {main}
thrown in /home/u1/domains/periplux.io/public_html/dev-api/common/Database.php on line 19
',
''
);
-- View the error
CALL sp_page_logs(1, 10, @affected_rows);
CALL sp_page_log_dates(1, 1, 10, @affected_rows);
CALL sp_page_log_details(1, 1, 10, @affected_rows);
CALL sp_page_log_stack_traces(1, 1, 10, @affected_rows);
-- Remove the error
CALL sp_delete_log(1);
I started running into trouble when deleting the orphaned data. MySQLWorkbench complains that I’m trying to delete data without using a key. In order to get past it, I had to turn off the SQL_SAFE_UPDATES. I also setup an exit handler so that I can restore the setting back to its original value.
Disable @@SQL_SAFE_UPDATES when deleting logs
DROP PROCEDURE IF EXISTS sp_delete_log;
DELIMITER //
CREATE PROCEDURE sp_delete_log (
IN p_log_id INT
)
this_proc: BEGIN
DECLARE v_error_message TEXT;
DECLARE v_sql_safe_updates INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1 v_error_message = MESSAGE_TEXT;
SELECT v_error_message AS `proc_message`;
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
END;
SET v_sql_safe_updates = @@SQL_SAFE_UPDATES;
START TRANSACTION;
DELETE FROM `log_dates` WHERE `log_id` = p_log_id;
DELETE FROM `log_details` WHERE `log_id` = p_log_id;
DELETE FROM `log_stack_traces` WHERE `log_id` = p_log_id;
DELETE FROM `logs` WHERE `id` = p_log_id;
-- About to remove notifications without directly using id
SET SQL_SAFE_UPDATES = 0;
-- Remove orphans
DELETE FROM `details` WHERE `id` NOT IN(
SELECT `detail_id` FROM `log_details`
);
DELETE FROM `stack_traces` WHERE `id` NOT IN(
SELECT `stack_trace_id` FROM `log_stack_traces`
);
DELETE FROM `messages` WHERE `id` NOT IN(
SELECT `message_id` FROM `logs`
);
DELETE FROM `paths` WHERE `id` NOT IN(
SELECT `path_id` FROM `logs`
);
DELETE FROM `types` WHERE `id` NOT IN(
SELECT `type_id` FROM `logs`
);
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
COMMIT;
END
//
DELIMITER ;
This looks good. I think our little database is ready. I’ll create the database on the host tomorrow and setup a cron process to move error logs into the database.
Error Log Database for MySQL/MariaDB
DROP PROCEDURE IF EXISTS sp_delete_all;
DROP PROCEDURE IF EXISTS sp_delete_log;
DROP PROCEDURE IF EXISTS sp_get_log_id;
DROP PROCEDURE IF EXISTS sp_get_message_id;
DROP PROCEDURE IF EXISTS sp_get_path_id;
DROP PROCEDURE IF EXISTS sp_get_type_id;
DROP PROCEDURE IF EXISTS sp_log;
DROP PROCEDURE IF EXISTS sp_log_date;
DROP PROCEDURE IF EXISTS sp_log_details;
DROP PROCEDURE IF EXISTS sp_log_stack_trace;
DROP PROCEDURE IF EXISTS sp_page_log_dates;
DROP PROCEDURE IF EXISTS sp_page_log_details;
DROP PROCEDURE IF EXISTS sp_page_log_stack_traces;
DROP PROCEDURE IF EXISTS sp_page_logs;
DROP TABLE IF EXISTS log_stack_traces;
DROP TABLE IF EXISTS log_details;
DROP TABLE IF EXISTS log_dates;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS types;
DROP TABLE IF EXISTS stack_traces;
DROP TABLE IF EXISTS paths;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS details;
CREATE TABLE `details` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`hash` CHAR(64) NOT NULL UNIQUE,
`details` TEXT NOT NULL
);
CREATE TABLE `messages` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`message` VARCHAR(1024) NOT NULL UNIQUE
);
CREATE TABLE `paths` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`path` VARCHAR(1024) NOT NULL UNIQUE
);
CREATE TABLE `stack_traces` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`hash` CHAR(64) NOT NULL UNIQUE,
`stack_trace` TEXT NOT NULL
);
CREATE TABLE `types` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`type` VARCHAR(64) NOT NULL UNIQUE
);
CREATE TABLE `logs` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`first_at` BIGINT NOT NULL,
`last_at` BIGINT NOT NULL,
`type_id` INT NOT NULL,
`message_id` INT NOT NULL,
`path_id` INT NOT NULL,
`line` INT NOT NULL,
FOREIGN KEY(`type_id`) REFERENCES `types`(`id`),
FOREIGN KEY(`message_id`) REFERENCES `messages`(`id`),
FOREIGN KEY(`path_id`) REFERENCES `paths`(`id`),
UNIQUE(`type_id`, `message_id`, `path_id`, `line`),
INDEX(`path_id`),
INDEX(`last_at`),
INDEX(`first_at`)
)
;
CREATE TABLE `log_dates` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`log_id` INT NOT NULL,
`first_at` BIGINT NOT NULL,
`last_at` BIGINT NOT NULL,
`count` INT DEFAULT 1,
FOREIGN KEY(`log_id`) REFERENCES `logs`(`id`),
INDEX(`log_id`, `last_at`),
INDEX(`log_id`, `first_at`),
UNIQUE(`log_id`, `first_at`)
);
CREATE TABLE `log_details` (
`log_id` INT NOT NULL,
`detail_id` INT NOT NULL,
FOREIGN KEY(`log_id`) REFERENCES `logs`(`id`),
FOREIGN KEY(`detail_id`) REFERENCES `details`(`id`),
PRIMARY KEY(`log_id`, `detail_id`)
);
CREATE TABLE `log_stack_traces` (
`log_id` INT NOT NULL,
`stack_trace_id` INT NOT NULL,
FOREIGN KEY(`log_id`) REFERENCES `logs`(`id`),
FOREIGN KEY(`stack_trace_id`) REFERENCES `stack_traces`(`id`),
PRIMARY KEY(`log_id`, `stack_trace_id`)
);
DROP PROCEDURE IF EXISTS sp_delete_all;
DELIMITER //
CREATE PROCEDURE sp_delete_all (
)
this_proc: BEGIN
DECLARE v_error_message TEXT;
DECLARE v_sql_safe_updates INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1 v_error_message = MESSAGE_TEXT;
SELECT v_error_message AS `proc_message`;
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
END;
SET v_sql_safe_updates = @@SQL_SAFE_UPDATES;
-- About to remove notifications without directly using id
SET SQL_SAFE_UPDATES = 0;
DELETE FROM `log_dates`;
DELETE FROM `log_details`;
DELETE FROM `log_stack_traces`;
DELETE FROM `logs`;
DELETE FROM `details`;
DELETE FROM `stack_traces`;
DELETE FROM `messages`;
DELETE FROM `paths`;
DELETE FROM `types`;
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_delete_log;
DELIMITER //
CREATE PROCEDURE sp_delete_log (
IN p_log_id INT
)
this_proc: BEGIN
DECLARE v_error_message TEXT;
DECLARE v_sql_safe_updates INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1 v_error_message = MESSAGE_TEXT;
SELECT v_error_message AS `proc_message`;
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
END;
SET v_sql_safe_updates = @@SQL_SAFE_UPDATES;
START TRANSACTION;
DELETE FROM `log_dates` WHERE `log_id` = p_log_id;
DELETE FROM `log_details` WHERE `log_id` = p_log_id;
DELETE FROM `log_stack_traces` WHERE `log_id` = p_log_id;
DELETE FROM `logs` WHERE `id` = p_log_id;
-- About to remove notifications without directly using id
SET SQL_SAFE_UPDATES = 0;
-- Remove orphans
DELETE FROM `details` WHERE `id` NOT IN(
SELECT `detail_id` FROM `log_details`
);
DELETE FROM `stack_traces` WHERE `id` NOT IN(
SELECT `stack_trace_id` FROM `log_stack_traces`
);
DELETE FROM `messages` WHERE `id` NOT IN(
SELECT `message_id` FROM `logs`
);
DELETE FROM `paths` WHERE `id` NOT IN(
SELECT `path_id` FROM `logs`
);
DELETE FROM `types` WHERE `id` NOT IN(
SELECT `type_id` FROM `logs`
);
SET SQL_SAFE_UPDATES = v_sql_safe_updates;
COMMIT;
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_get_log_id;
DELIMITER //
CREATE PROCEDURE sp_get_log_id (
IN p_created_at BIGINT,
IN p_type_id INT,
IN p_message_id INT,
IN p_path_id INT,
IN p_line INT,
OUT p_log_id INT
)
this_proc: BEGIN
DECLARE v_id INT;
DECLARE v_first_at BIGINT;
DECLARE v_last_at BIGINT;
SET p_log_id = NULL;
IF p_type_id IS NULL OR p_message_id IS NULL OR p_path_id IS NULL OR p_line IS NULL OR p_created_at IS NULL THEN
LEAVE this_proc;
END IF;
SELECT
`id`,
`first_at`,
`last_at`
INTO
p_log_id,
v_first_at,
v_last_at
FROM
`logs`
WHERE
`type_id` = p_type_id
AND `message_id` = p_message_id
AND `path_id` = p_path_id
AND `line` = p_line
LIMIT 1;
IF p_log_id IS NOT NULL THEN
IF p_created_at < v_first_at THEN
UPDATE `logs` SET `first_at` = p_created_at WHERE `id` = p_log_id LIMIT 1;
END IF;
IF p_created_at > v_last_at THEN
UPDATE `logs` SET `last_at` = p_created_at WHERE `id` = p_log_id LIMIT 1;
END IF;
LEAVE this_proc;
END IF;
INSERT INTO `logs` (
`first_at`,
`last_at`,
`type_id`,
`message_id`,
`path_id`,
`line`
) VALUES (
p_created_at,
p_created_at,
p_type_id,
p_message_id,
p_path_id,
p_line
);
SET p_log_id = LAST_INSERT_ID();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_get_message_id;
DELIMITER //
CREATE PROCEDURE sp_get_message_id (
IN p_message VARCHAR(1024),
OUT p_message_id INT
)
this_proc: BEGIN
DECLARE v_id INT;
SET p_message_id = NULL;
IF p_message IS NULL OR p_message = '' THEN
LEAVE this_proc;
END IF;
SET p_message = TRIM(p_message);
IF p_message = '' THEN
LEAVE this_proc;
END IF;
SELECT `id` INTO p_message_id FROM `messages` WHERE `message` = p_message LIMIT 1;
IF p_message_id IS NOT NULL THEN
LEAVE this_proc;
END IF;
INSERT INTO `messages` (`message`) VALUES (p_message);
SET p_message_id = LAST_INSERT_ID();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_get_path_id;
DELIMITER //
CREATE PROCEDURE sp_get_path_id (
IN p_path VARCHAR(1024),
OUT p_path_id INT
)
this_proc: BEGIN
DECLARE v_id INT;
SET p_path_id = NULL;
IF p_path IS NULL OR p_path = '' THEN
LEAVE this_proc;
END IF;
SET p_path = TRIM(p_path);
IF p_path = '' THEN
LEAVE this_proc;
END IF;
SELECT `id` INTO p_path_id FROM `paths` WHERE `path` = p_path LIMIT 1;
IF p_path_id IS NOT NULL THEN
LEAVE this_proc;
END IF;
INSERT INTO `paths` (`path`) VALUES (p_path);
SET p_path_id = LAST_INSERT_ID();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_get_type_id;
DELIMITER //
CREATE PROCEDURE sp_get_type_id (
IN p_type VARCHAR(64),
OUT p_type_id INT
)
this_proc: BEGIN
DECLARE v_id INT;
SET p_type_id = NULL;
IF p_type IS NULL OR p_type = '' THEN
LEAVE this_proc;
END IF;
SET p_type = TRIM(p_type);
IF p_type = '' THEN
LEAVE this_proc;
END IF;
SELECT `id` INTO p_type_id FROM `types` WHERE `type` = p_type LIMIT 1;
IF p_type_id IS NOT NULL THEN
LEAVE this_proc;
END IF;
INSERT INTO `types` (`type`) VALUES (p_type);
SET p_type_id = LAST_INSERT_ID();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_log;
DELIMITER //
CREATE PROCEDURE sp_log (
IN p_created_at BIGINT,
IN p_type VARCHAR(64),
IN p_message VARCHAR(1024),
IN p_path VARCHAR(1024),
IN p_line INT,
IN p_stack_trace TEXT,
IN p_details TEXT
)
this_proc: BEGIN
DECLARE v_error_message TEXT;
DECLARE v_affected_rows INT;
DECLARE v_detail_id INT;
DECLARE v_stack_trace_id INT;
DECLARE v_path_id INT;
DECLARE v_message_id INT;
DECLARE v_type_id INT;
DECLARE v_log_id INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_error_message = MESSAGE_TEXT;
SELECT v_error_message AS `proc_message`;
END;
CALL sp_get_path_id(p_path, v_path_id);
CALL sp_get_message_id(p_message, v_message_id);
CALL sp_get_type_id(p_type, v_type_id);
CALL sp_get_log_id(
p_created_at,
v_type_id,
v_message_id,
v_path_id,
p_line,
v_log_id
);
CALL sp_log_details(v_log_id, p_details);
CALL sp_log_stack_trace(v_log_id, p_stack_trace);
CALL sp_log_date(v_log_id, p_created_at);
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_log_date;
DELIMITER //
CREATE PROCEDURE sp_log_date (
IN p_log_id INT,
IN p_created_at BIGINT
)
this_proc: BEGIN
DECLARE v_date_id INT;
DECLARE v_first_at BIGINT;
DECLARE v_last_at BIGINT;
IF p_created_at IS NULL OR p_log_id IS NULL THEN
LEAVE this_proc;
END IF;
SELECT `id`, `first_at`, `last_at`
INTO v_date_id, v_first_at, v_last_at
FROM `log_dates`
WHERE
`log_id` = p_log_id
AND (
p_created_at BETWEEN `first_at` AND `last_at`
OR (
p_created_at >= `first_at` - 5
AND p_created_at <= `last_at` + 5
)
)
LIMIT 1;
IF v_date_id IS NULL THEN
INSERT INTO `log_dates` (`log_id`, `first_at`, `last_at`) VALUES (p_log_id, p_created_at, p_created_at);
ELSE
UPDATE `log_dates` SET `count` = `count` + 1 WHERE `id` = v_date_id LIMIT 1;
IF p_created_at < v_first_at THEN
UPDATE `log_dates` SET `first_at` = p_created_at WHERE `id` = v_date_id LIMIT 1;
END IF;
IF p_created_at > v_last_at THEN
UPDATE `log_dates` SET `last_at` = p_created_at WHERE `id` = v_date_id LIMIT 1;
END IF;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_log_details;
DELIMITER //
CREATE PROCEDURE sp_log_details (
IN p_log_id INT,
IN p_details TEXT
)
this_proc: BEGIN
DECLARE v_detail_id INT;
DECLARE v_hash CHAR(64);
IF p_details IS NULL OR p_details = '' THEN
LEAVE this_proc;
END IF;
SET p_details = TRIM(p_details);
IF p_details = '' THEN
LEAVE this_proc;
END IF;
SET v_hash = SHA2(p_details, 256);
SELECT `id` INTO v_detail_id FROM `details` WHERE `hash` = v_hash LIMIT 1;
IF v_detail_id IS NULL THEN
INSERT INTO `details` (`hash`, `details`) VALUES (v_hash, p_details);
SET v_detail_id = LAST_INSERT_ID();
END IF;
IF NOT EXISTS(
SELECT 0 FROM `log_details` WHERE `log_id` = p_log_id AND `detail_id` = v_detail_id LIMIT 1
) THEN
INSERT INTO `log_details` (`log_id`, `detail_id`) VALUES (p_log_id, v_detail_id);
END IF;
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_log_stack_trace;
DELIMITER //
CREATE PROCEDURE sp_log_stack_trace (
IN p_log_id INT,
IN p_stack_trace TEXT
)
this_proc: BEGIN
DECLARE v_stack_trace_id INT;
DECLARE v_hash CHAR(64);
IF p_stack_trace IS NULL OR p_stack_trace = '' THEN
LEAVE this_proc;
END IF;
SET p_stack_trace = TRIM(p_stack_trace);
IF p_stack_trace = '' THEN
LEAVE this_proc;
END IF;
SET v_hash = SHA2(p_stack_trace, 256);
SELECT `id` INTO v_stack_trace_id FROM `stack_traces` WHERE `hash` = v_hash LIMIT 1;
IF v_stack_trace_id IS NULL THEN
INSERT INTO `stack_traces` (`hash`, `stack_trace`) VALUES (v_hash, p_stack_trace);
SET v_stack_trace_id = LAST_INSERT_ID();
END IF;
IF NOT EXISTS(
SELECT 0 FROM `log_stack_traces` WHERE `log_id` = p_log_id AND `stack_trace_id` = v_stack_trace_id LIMIT 1
) THEN
INSERT INTO `log_stack_traces` (`log_id`, `stack_trace_id`) VALUES (p_log_id, v_stack_trace_id);
END IF;
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_page_log_dates;
DELIMITER //
CREATE PROCEDURE sp_page_log_dates (
IN p_log_id INT,
IN p_page_number INT,
IN p_page_size INT,
OUT p_affected_rows INT
)
this_proc: BEGIN
DECLARE v_page_offset INT;
IF p_page_number < 1 THEN
LEAVE this_proc;
END IF;
IF NOT p_page_size BETWEEN 1 AND 100 THEN
LEAVE this_proc;
END IF;
SET v_page_offset = p_page_size * (p_page_number - 1);
SELECT
`first_at`,
`last_at`,
`count`
FROM
`log_dates` AS ld
WHERE
ld.`log_id` = p_log_id
ORDER BY
ld.`last_at` DESC
LIMIT p_page_size OFFSET v_page_offset;
SET p_affected_rows = FOUND_ROWS();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_page_log_details;
DELIMITER //
CREATE PROCEDURE sp_page_log_details (
IN p_log_id INT,
IN p_page_number INT,
IN p_page_size INT,
OUT p_affected_rows INT
)
this_proc: BEGIN
DECLARE v_page_offset INT;
IF p_page_number < 1 THEN
LEAVE this_proc;
END IF;
IF NOT p_page_size BETWEEN 1 AND 100 THEN
LEAVE this_proc;
END IF;
SET v_page_offset = p_page_size * (p_page_number - 1);
SELECT
d.`details`
FROM
`log_details` AS ld
INNER JOIN `details` AS d ON ld.`detail_id` = d.`id`
WHERE
ld.`log_id` = p_log_id
ORDER BY
d.`id`
LIMIT p_page_size OFFSET v_page_offset;
SET p_affected_rows = FOUND_ROWS();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_page_log_stack_traces;
DELIMITER //
CREATE PROCEDURE sp_page_log_stack_traces (
IN p_log_id INT,
IN p_page_number INT,
IN p_page_size INT,
OUT p_affected_rows INT
)
this_proc: BEGIN
DECLARE v_page_offset INT;
IF p_page_number < 1 THEN
LEAVE this_proc;
END IF;
IF NOT p_page_size BETWEEN 1 AND 100 THEN
LEAVE this_proc;
END IF;
SET v_page_offset = p_page_size * (p_page_number - 1);
SELECT
st.`stack_trace`
FROM
`log_stack_traces` AS lst
INNER JOIN `stack_traces` AS st ON lst.`stack_trace_id` = st.`id`
WHERE
lst.`log_id` = p_log_id
ORDER BY
st.`id`
LIMIT p_page_size OFFSET v_page_offset;
SET p_affected_rows = FOUND_ROWS();
END
//
DELIMITER ;
DROP PROCEDURE IF EXISTS sp_page_logs;
DELIMITER //
CREATE PROCEDURE sp_page_logs (
IN p_page_number INT,
IN p_page_size INT,
OUT p_affected_rows INT
)
this_proc: BEGIN
DECLARE v_page_offset INT;
IF p_page_number < 1 THEN
LEAVE this_proc;
END IF;
IF NOT p_page_size BETWEEN 1 AND 100 THEN
LEAVE this_proc;
END IF;
SET v_page_offset = p_page_size * (p_page_number - 1);
SELECT
l.`id`,
ld.`last_at`,
t.`type`,
m.`message`,
p.`path`,
l.`line`,
ld.`count`
FROM
`logs` AS l
INNER JOIN `types` AS t ON l.`type_id` = t.`id`
INNER JOIN `messages` AS m ON l.`message_id` = m.`id`
INNER JOIN `paths` AS p ON l.`path_id` = p.`id`
INNER JOIN `log_dates` AS ld ON ld.`log_id` = l.`id`
ORDER BY
ld.`last_at` DESC
LIMIT p_page_size OFFSET v_page_offset;
SET p_affected_rows = FOUND_ROWS();
END
//
DELIMITER ;
Wrap Up
We’ve done a bit of stuff today in regards to error logs. In short, we read a file and created a database for storing its data. The process covered many steps, analysis, and various technologies.
- Configured error logging via host interface to php.ini
- Configured error logging via .htaccess
- Restricted access to error log and any file beginning with a dot
- Centralized error logging to one file
- Read a large text file with a limited amount of memory
- Jump to a specific byte position in the file
- Read one line at a time
- Use regular expressions to parse date, error type, message, file, and line number
- Parse stack-traces that span over multiple lines
- Read the byte position where we left off so that the next request can pickup at the same point
- Create a database schema and normalized the logs to avoid duplicate values.
- Create an entity relationship diagram of the error log
- Use BIGINT to represent 64-bit unix timestamps to account for 2038 bug
- Handle a scenario where the same error may be repeated in an infinite loop.
- Create procedures to log the data, paginate through the logs, and delete logs.
- Work with transactions when deleting the data
- Disable SQL Safe Updates when deleting orphaned data from lookup tables
2 responses to “Error Logging”
[…] I went over how to enable error logging in PHP, read large error log files to be parsed as JSON entries and paginated. I then proceeded to build […]
[…] 20: Error Logging – I turned on a few settings to log errors and parsed a large text file in small batches to […]