Safe DML Options

Permalink: http://bit.ly/VNLTQe



The Safe DML project provides automatic creation of backups and an undo functionality for MySQL. These two abilities do not rely on the command line shell and can simply be executed by queries.

$DML() Options

Inside the stored procedure, $DML(), you can find the following options that can be set:

-- Switches logging on/off
DECLARE logging BOOLEAN DEFAULT FALSE;
-- Clears the logs per call
DECLARE clearLogs BOOLEAN DEFAULT TRUE;
-- Set to FALSE to backup only the current db in use
DECLARE backupAllDB BOOLEAN DEFAULT TRUE;
-- Disables filtering out of unsupported statements
DECLARE dmlFilter BOOLEAN DEFAULT TRUE;

• The logging option enables/disables logs written by Safe DML into the `debug` table in the `$backup` database. Logging is useful for development work when adding new features or updating Safe DML. Disable for production use to improve performance.
• Additionally, you can set logs to clear for each time $DML() is used. Enable this option only when needed and only during development work since keeping this BOOLEAN set to TRUE can cause the `debug` table to consume too much disk space over time especially when used in a production environment.
• $DML() can be used for just the current database by setting this option to FALSE. This is also useful for keeping disk space consumption of backup tables low if only the current database needs an UNDO functionality.
• Statements not supported by Safe DML has an option to be filtered out from being passed. Only INSERT, UPDATE, DELETE, and REPLACE statements are currently supported. Since SELECT and SHOW statements do not modify data, you can pass these statements into $DML() if so desired but these will not be recorded in the `history` table inside the `$backup` database. 

Other statements that are allowed to be used inside transactions will be supported in a future version. Options on the size restriction on the maximum number of `history` table records and flushing schedule of table backups are under development.

See the change logs for updates on new features.

$UNDO() Options

For the $UNDO() stored procedure, you can also set its options for logging and clearLogs. To invoke an undo command, you can either use the UNDO keyword by calling the $DML() stored procedure or call the $UNDO() stored procedure directly.

To UNDO the last statement:

CALL $DML("UNDO");
-- or
CALL $DML("UNDO LAST STATEMENT");
-- or
CALL $UNDO('');
-- or
CALL $UNDO('LAST STATEMENT');

To UNDO all changes:

CALL $DML("UNDO ALL")
-- or
CALL $UNDO('ALL');

To UNDO multiple statements or to a specific point in time, first determine the commands you want to revert by parsing the `history` table for the id of the final command to be reverted:

SELECT * FROM `$backup`.`history`;
-- take note of the dmlStringId

Then call the desired stored procedures, where n is the dmlStringId of the final command to be reverted.

CALL $DML("UNDO n");
-- or
CALL $UNDO(n);

For example, there are 3 records in the `history` table with dmlStringIds 1, 2, and 3. The following call will have the effect of undoing the 3rd command in the record, then the 2nd command, in sequence, while keeping the 1st command with dmlStringId = 1, untouched:

CALL $DML("UNDO 2");

To be continued.

Safe DML

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



There is no native undo ability inside MySQL. Thus, the common methods to workaround the problem is through creating backup dumps and enabling binary logging, using transactions, and requiring the WHERE clause in Data Manipulation Language commands by using the safe updates option. These methods have drawbacks:

1. Creating backups via mysqldump and using binary logging to revert to a point in time will have the same effect as an undo functionality. However, these are executed via the command line shell. Since these tools are not executed inside MySQL, this method may not be convenient and presents limitations on when it can be used.
2. Transactions allow you to "undo" as long as you have not committed your data manipulation changes. Imagine if you discover data manipulation changes that you wish to undo after the last transaction commit. It is impossible.
3. Using the safe updates option when running MySQL is meant to be a way to prevent undesirable changes by requiring the WHERE clause. Requiring the WHERE clause does not provide an undo ability.

Without a feasible undo ability, mistakes can lead to frustrations and several hours of work lost to having to manually re-enter previous data.

This small project is aimed to provide the missing undo ability, all inside MySQL and allows the user to undo previous DML changes independent of the session. To fully automate the undo functionality, the DML command needs to be contained in a stored procedure. The example below shows a data manipulation statement encapsulated in quotation marks so that single quotes can be used by the statement itself. The statement is passed as a parameter of the $DML() SP:

CALL $DML(
  "UPDATE `dbName`.`tableName`
  SET `columnName` = 'updateValue'
  WHERE `primaryKey` = 100"
);

The Safe DML stored procedure, $DML(), automates the creation of backups by copying table records into a created database named `$backup`. The undo functionality can be invoked by calling the $UNDO() stored procedure:

CALL $UNDO('');
-- or
CALL $DML('UNDO');

This reverts the last DML command. There are options to undo all previous DML commands, by UNDO('ALL') or just up to a specific number of changes. UNDO options can be found in this blog entry: http://mysql-0v34c10ck.blogspot.com/2011/05/safe-dml-options.html.

How does it work? $DML() will accept any data manipulation statement no matter how complex. It will gracefully handle commands that are in the wrong syntax or that cause errors. In such cases, no changes will take effect and the appropriate MySQL error message will be thrown to indicate what the problem with the statement is.

Before executing the DML statement, $DML() will create snapshots of all tables from all databases. There is an option to create backup copies for only the current database and this is discussed in Safe DML Options. The creation of a snapshot only takes effect when $DML() is called for the first time when the `$backup` database does not exist yet.

Note: The backup copies are considered "snapshots" since these are data copies only. The primary key, indexes, and foreign key constraints of the tables are not backed up. The snapshots are stored in the `$backup` database and if any DDL statements are executed, for example, a new column was added in a table, it is recommended that the `$backup` database be dropped so that it can be appropriately re-created. This flush process may become automated in a future version of this project.

Inside the created `$backup` database, there will be 3 tables that are used by Safe DML: `history`, `tableref`, and `debug`. The `history` table contains records of the DML commands executed using $DML(). The `tableref` table contains records about each table snapshot. The third table, `debug`, contains logs written by Safe DML and is useful for development work when adding new features or updating Safe DML.

$DML() stored procedure does not manipulate any table from any database other than its own `$backup` database. This allows the user to create TRIGGERS and CONSTRAINTS in their tables since Safe DML does not rely on those.

The project, Safe DML, is currently in development. Only INSERT, UPDATE, DELETE, and REPLACE statements are currently supported. This will expand in a future version. 

To be continued.