Mastering MariaDB

Debugging stored programs using the SQL_ERROR_LOG plugin

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. ...