Automatic Logging of Table Data Changes and Creation of Backups via a Stored Procedure

Permalink: http://bit.ly/1rNFRsO



The stripped down stored procedure shown below will accept any Data Manipulation Language statement as its parameter and automatically log the statement and create table backup copies before the statement is executed. The logging functionality is similar to MySQL's binary log but exclusive to DML statements and is useful for table data recovery operations, such as undoing the last table data change or to revert databases back to a certain point in time. All this is done exclusively using stored routines (procedures and functions).

Its assumed that the databases and tables that will be used are already formed to specific business requirements since DDL statements will not be logged by the stored procedure. Though logging of table data changes can also be achieved using triggers, it is not practical to alter each and every trigger of every table if there are numerous tables to consider. Using a stored procedure allows the functionality to be portable to any existing database since no alteration is needed.

DELIMITER $$
DROP PROCEDURE IF EXISTS `$DML` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `$DML`(
  IN dmlString TEXT)
$dml: BEGIN
   DECLARE clearLogs BOOLEAN DEFAULT TRUE;
   DECLARE backupAllDB BOOLEAN DEFAULT TRUE;
   DECLARE dmlFilter BOOLEAN DEFAULT TRUE;
   DECLARE EXIT HANDLER
     FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
     ROLLBACK;
   SET dmlString = TRIM(dmlString);
   IF (dmlString LIKE ('UNDO%'))
   THEN
     CALL $UNDO(dmlString);
     LEAVE $dml;
   END IF;
   initialize: BEGIN
   DECLARE selectStmtBackupAllDB  TEXT;
     IF (
       IF((
         EXISTS(
           SELECT *
           FROM `information_schema`.`TABLES`
           WHERE (`TABLE_SCHEMA` = '$backup'))
         ),
       1, 0)
     )
     THEN
       LEAVE initialize;
     END IF;
     CREATE DATABASE `$backup`;
     CREATE TABLE `$backup`.`history` (
       `dmlStringId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `user` VARCHAR(255) NOT NULL,
       `dmlString` TEXT NOT NULL,
       `timestamp` TIMESTAMP NOT NULL DEFAULT NOW(),
       PRIMARY KEY (`dmlStringId`)
     )
     ENGINE = InnoDB;
     CREATE TABLE `$backup`.`tableref` (
       `tableRefId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `dbName` VARCHAR(64) NOT NULL,
       `tableName` VARCHAR(64) NOT NULL,
       `tableAlias` VARCHAR(64) NOT NULL,
       `backupOf` INTEGER UNSIGNED,
       PRIMARY KEY (`tableRefId`)
     )
     ENGINE = InnoDB;
     IF (backupAllDB)
     THEN
       SET selectStmtBackupAllDB =
         "SELECT T.`TABLE_SCHEMA`, T.`TABLE_NAME`
           FROM `information_schema`.`TABLES` T
           WHERE (`TABLE_SCHEMA` != 'information_schema')
             AND (`TABLE_SCHEMA` != 'mysql')
             AND (`TABLE_SCHEMA` != 'performance_schema')
             AND (`TABLE_SCHEMA` != '$backup')
             AND (`TABLE_NAME` NOT LIKE 'dynamic_cursor%')";
     ELSE
       SET selectStmtBackupAllDB =
         "SELECT T.`TABLE_SCHEMA`, T.`TABLE_NAME`
           FROM `information_schema`.`TABLES` T
           WHERE (`TABLE_SCHEMA` =  DATABASE())
             AND (`TABLE_NAME` NOT LIKE 'dynamic_cursor%')";
     END IF;
     CALL dynamicCursor(
       selectStmtBackupAllDB,
       'initialize', @NULL, logging
     );
   END initialize;
   IF (dmlFilter)
     AND (dmlString NOT LIKE ('INSERT%'))
     AND (dmlString NOT LIKE ('UPDATE%'))
     AND (dmlString NOT LIKE ('DELETE%'))
     AND (dmlString NOT LIKE ('REPLACE%'))
     AND (dmlString NOT LIKE ('SELECT%'))
     AND (dmlString NOT LIKE ('SHOW%'))
   THEN
     SET dmlString = NULL;
   END IF;
   START TRANSACTION;
     SET @dmlStringStmt = dmlString;
     PREPARE dmlStringStmt FROM @dmlStringStmt;
     EXECUTE dmlStringStmt;
     DEALLOCATE PREPARE dmlStringStmt;
   COMMIT;
   IF (dmlString LIKE ('SELECT%'))
     OR (dmlString LIKE ('SHOW%'))
   THEN
     LEAVE $dml;
   ELSE
     INSERT `$backup`.`history`
     VALUES (DEFAULT, USER(), dmlString, DEFAULT);
   END IF;
END $dml $$
DELIMITER ;

The stored procedure is part of a small project, Safe DML, $DML().

A function to get all the columns of any table from any database

Permalink: http://bit.ly/VP174V



Certain complex MySQL SELECT and subquery statements will not allow the use of the * wildcard and you will need to fill in the entire column list of a given table. Consider the following simplified example, a SELECT statement that contains 3 columns. The asterisk here refers to all columns, which is actually the 3 columns listed in the GROUP BY clause:

SELECT
 IF(
  EXISTS(
  SELECT *
  FROM (
   SELECT *
   FROM `dbName_A`.`tableName_A`
   UNION ALL
   SELECT *
   FROM `dbName_B`.`tableName_B`
   AS `compareTables`
  GROUP BY `column_1`, `column_2`, `column_3`
  HAVING COUNT(*) = 1),
 1, 0);

Imagine if it were dozens of columns instead of just 3. You can't simply put in the * wildcard like 'GROUP BY * '. The above example will not work without the GROUP BY clause and you'll need to type in all the column names. A solution is to create a function that returns a list of the column names for you. Here it is:

DELIMITER $$
DROP FUNCTION IF EXISTS `getColumnList` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getColumnList`(
  dbName VARCHAR(64),
  tableName VARCHAR(64)) RETURNS text CHARSET utf8
BEGIN
  DECLARE columnList TEXT;
  SET group_concat_max_len = 65533;
  SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR '`,`')
  FROM `information_schema`.`COLUMNS`
  WHERE (`TABLE_SCHEMA` = dbName)
    AND (`TABLE_NAME` = tableName)
  INTO columnList;
  SET columnList = CONCAT('`', columnList, '`');
  RETURN columnList;
END $$
DELIMITER ;

This function has 1 limitation: as of version 5.5 of MySQL, it is not possible to get a column list into a variable if it comes from a temporary table. This is due to the fact that temporary tables are invisible within the `information_schema` database. This will hopefully be resolved in a future version of MySQL.

To use the function, simply pass the database name and table name parameters to the function such as:

SET @columnList = getColumnList('dbName_A', 'tableName_A');

A list of the column names in the form similar to the above example will be returned:

`column_1`, `column_2`, `column_3` 

The first example can now be re-written in a simpler way. Now, it does not matter how many columns there are, the function will take care of it all:

SELECT
 IF(
  EXISTS(
  SELECT *
  FROM (
   SELECT *
   FROM `dbName_A`.`tableName_A`
   UNION ALL
   SELECT *
   FROM `dbName_B`.`tableName_B`
   AS `compareTables`
  GROUP BY @columnList
  HAVING COUNT(*) = 1),
 1, 0);

This function is especially useful for dynamic MySQL statements and is robust enough to handle a wide variety of usage types. Here's a run down on the function's underlying code:

• TEXT variable type is used for the string return since it is the most practical character length among the variable choices. Smaller string types are too small at 255 maximum character length, while the next larger option, MEDIUMTEXT, is at 16,777,215 characters is just too big and would be a resource hog. TEXT data type can hold a string up to a maximum length of 65,535 characters.
• group_concat_max_len has a default value of 1,024. If the column list concatenated is longer than 1,024 characters, it will be truncated. The max length of the GROUP_CONCAT clause should match the character length of the TEXT data type in order to maximize its usefulness. The value is set at 65,533 since 2 backticks (explained below) surrounding the string will add up to 65,535 characters. This change is not permanent and only remains valid for the duration of the active session. You may increase the maximum to a much larger value, but 65 thousand characters should pretty much handle most cases.
• Column names are surrounded in backticks in case they contain reserved words. Not enclosing column names in backticks can be problematic since the function may be used in a variety of ways and may cause unpredictable results if not done so.

Updated: 10/11/2012