Abdel-Mawla Gharieb, continue handler, cursor, if condition, loop, MySQL, MySQL Cluster, prepared statement, stored procedure, stored routine
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;
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE tbl_name varchar(50) DEFAULT "";
DECLARE all_tbl_cursor CURSOR FOR
SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = db_name and ENGINE='ndbcluster';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
FETCH all_tbl_cursor INTO tbl_name;
IF v_finished = 1 THEN
SET @sql= CONCAT('ALTER TABLE `', db_name,'`.`', tbl_name, '` REORGANIZE PARTITION;');
PREPARE stmt FROM @sql;
SET @sql= CONCAT('OPTIMIZE TABLE `', db_name,'`.`', tbl_name, '`;');
PREPARE stmt FROM @sql;
END LOOP get_tbls;
DEALLOCATE PREPARE stmt;
And then the procedure can be executed as follows ( to reorganizing all NDB tables partitions in database called “cluster_database”):
mysql> CALL reorganize_tables('cluster_database');
I believe by making few modifications to the above procedure (parameters, cursor’s select, prepared statement, … etc.) it can be used for several purposes.
Hope you find it useful 🙂
Thank you very much for the article and the template.
I’m also a big fan of stored procedures, and in general all stored routines. I’ve made some changes (https://gist.github.com/wchiquito/880dea085a30b92d525fa98bb44a35aa) to your template.
Welcome all comments of the modifications.
Your changes are welcomed 🙂 I believe they are another way of writing the same logic anyway!
thanks for your feedback!
In effect, it was a refactoring since, by mistake, I ran the stored procedure in a database without “ndbcluster” tables and an error was thrown.
Thank you very much for your comment.