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:

If everything worked fine, of course, we will see no errors.