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



The rationale behind comparing tables versus using a CHECKSUM TABLE statement can be found in the first part of this entry.

Comparing the record differences of two similar tables can be useful when transferring records from an old database to a new one or when comparing backup tables against the original tables. Depending on specific requirements, it may be necessary to validate that the transfer was successful or to see which specific data in the records of the original and in-use tables have been updated, inserted, or deleted when compared to the backup. The query in the stored procedure below will show the differences caused by updates to current records as well as the record differences …

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



Click here to skip to the code snippet.

The CHECKSUM TABLE result sets of two similar tables only indicate if there are differences between the two tables. It does not tell you what exactly the differences are between the two. Does `tableA` have an updated value for one of its records that `tableB` does not have? Or does `tableA` have an extra row that `tableB` does not have? Depending on specific business requirements, the CHECKSUM TABLE statement may be sufficient. If you need to determine what the actual differences are, there is a way to go about doing this automatically and dynamically by creating a stored procedure.

This SELECT statement that uses a UNION ALL clause …

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 …

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 …

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



This is the workaround to create dynamic cursors in MySQL and is meant to be used together with the general purpose dynamic cursor stored procedure.

Increase the compared value of colValN in the WHILE-DO construct to the maximum number of columns your dynamic cursor will be needing. This fixes MySQL's limitation on cursors by providing the dynamic cursor with consistent column names that are independent of the underlying SELECT statement and also provide a predictable number of columns.

The final result is that the temporarily created table will be constant, while the records that the cursor will traverse will remain dynamic. The code in …

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



See part 1 for the rationale behind the code and part 2 to understand limitations faced by cursors in MySQL.

The code snippet below shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure. This iteration also works around MySQL's cursor problem mentioned in part 2 by calling the dynamic cursor fix stored …

Permalink: http://bit.ly/RcRieg



Refer to part 1 for the rationale behind the code or you can skip to part 3 for a working example as well as how you can debug the stored procedure.

Important: The SP will create a table named `dynamic_cursor`. Make sure this table does not exist in the database where you will be storing the procedure. Here's the 1st iteration of a general purpose dynamic cursor:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT …

Permalink: http://bit.ly/TBKQL3



Skip to part 2 for the code snippet or to part 3 for a working example and how to debug the stored procedure.

Also another workaround on a missing MySQL functionality, enabling/disabling triggers, can be found here.

As of version 5.5, MySQL still does not have the native ability to execute a dynamic cursor. This can be worked around but the resulting stored procedure will have a few limitations.

This stored procedure is a general purpose …

The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see an example, with a simple …