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.