The SQL_ERROR_LOG
plugin is particularly useful to log errors of the stored programs. For example, consider the following procedure:
CREATE PROCEDURE backups.backup_table(IN db_name CHAR(64), IN table_name CHAR(64)) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; SET @sql = CONCAT('TRUNCATE TABLE backups.', table_name); PREPARE stmt FROM @sql; EXECUTE stmt; SET @sql = CONCAT('INSERT INTO backups.', table_name, 'SELECT * FROM ', db_name, '.', table_name); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = NULL; END;
The preceding procedure is very simple. It just copies a table to a backup database, after deleting the old rows in the backup table. You can think of it as a quick way to run the TRUNCATE TABLE
and INSERT … SELECT
statements.
However, many problems may occur. For example, the backup table may not exist yet. Or, the source table may not exist, or its structure may be changed. There may also be privilege problems. If any of these problems (or any other problem) occur, an SQL error is produced. In that case, the execution moves to the DECLARE EXIT HANDLER
block, which does nothing, except suppress the error. This block may look useless, and probably it is; however, in real use cases, there are several good reasons why a HANDLER
block might be used, and it always suppresses the error (unless a RESIGNAL
statement is issued). When debugging a stored program, this means that the error is not sent to the client, and the developer may not notice the error that has occurred.
An empty handler can be useful in a program. It can suppress warnings or errors that are expected and do not cause any problem. The most obvious example is when a stored procedure tries to create tables with the IF NOT EXISTS
clause. If tables exist, a note will be issued. However, in most cases, this is not useful or is even annoying and thus, you may want to suppress the note. In case of the EXIT
handlers, the execution of the program will still stop. In case of a CONTINUE
handler, the execution will continue normally, and the warning or error will simply be ignored. While this could be the desired behavior, it is also a potential source of problems: if we use a generic class such as SQLEXCEPTION
, the handler could suppress an error that was not expected, making the debugging harder.
Of course, a quick way to see that an error occurred is using the SHOW WARNINGS
statement. But this statement can only work in a stored procedure, not in stored programs that cannot return a result set.
The SQL_ERROR_LOG
plugin is generally a better way to see errors. Let's execute the previously discussed procedure:
MariaDB [test]> CALL backups.backup_table('shop', 'customer'), Query OK, 0 rows affected (0.62 sec)
On executing the procedure, we see that no error is reported but we want to check whether an error has occurred. If we take a look at the last lines of the SQL error log, we can find one of the following entries depending on the error that occurred:
2014-01-10 12:26:43 root[root] @ localhost [] ERROR 1146: Table 'backups.customer' doesn't exist : TRUNCATE TABLE backups.customer
2014-01-10 13:12:06 root[root] @ localhost [] ERROR 1146: Table 'shop.customer' doesn't exist : INSERT INTO backups.customer SELECT * FROM shop.customer
2014-01-10 13:09:13 root[root] @ localhost [] ERROR 1136: Column count doesn't match value count at row 1 : INSERT INTO backups.customer SELECT * FROM shop.customer
If everything worked fine, of course, we will see no errors.