Book Image

Mastering MariaDB

By : Federico Razzoli
Book Image

Mastering MariaDB

By: Federico Razzoli

Overview of this book

Table of Contents (19 chapters)
Mastering MariaDB
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

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