As a stored procedures fan, I use MySQL stored procedures to get some of my DBA tasks accomplished. To make it simple, I have a template stored procedure that can be customized for several purposes.
The template syntax contains cursor, continue handler, loop, if condition and prepared statement. Thought it may be useful for others – at least, who are searching for the MySQL Stored Procedure syntax – so I’m publishing this post!

Use case: Reorganize tables partition:

In MySQL Cluster, it is required to reorganize all NDB tables’ partitions after adding new data nodes to rebalance the data across all nodes. Also the tables need to be optimized afterwards to reclaim the memory space. For this task I use the following procedure:

DROP PROCEDURE IF EXISTS reorganize_tables;
DELIMITER //
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))
BEGIN


…

The latest MySQL 5.5 milestone release offers support for an ANSI/ISO standard feature called the SIGNAL syntax. You can use this syntax inside stored routines (including triggers) to raise an error condition which can be used to invoke specific error handling, or otherwise abort the stored routine. In addition, you can use the SIGNAL syntax to convey information about what went wrong, which may be used by the caller to handle the error.

I have written about MySQL data entry validation procedures in the past. At the time, MySQL did not support any proper means to raise an error condition inside a stored routine, and …

Hi again!

Just a moment ago, I read this post by Peter Gulutzan. In this post, Peter explains a little trick that allows you to capture the SQL state in a variable whenever an error occurs in your MySQL stored routine code.MySQL CONDITIONs and HANDLERsFor the uninitiated: in MySQL stored routines, you can declare HANDLERs which are pieces of code that are executed only in case a particular CONDITION occurs. This device serves the same purpose …

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides …