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


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;
OPEN all_tbl_cursor;


get_tbls: LOOP
FETCH all_tbl_cursor INTO tbl_name;


IF v_finished = 1 THEN
LEAVE get_tbls;
END IF;


SET @sql= CONCAT('ALTER TABLE `', db_name,'`.`', tbl_name, '` REORGANIZE PARTITION;');
PREPARE stmt FROM @sql;
EXECUTE stmt;


SET @sql= CONCAT('OPTIMIZE TABLE `', db_name,'`.`', tbl_name, '`;');
PREPARE stmt FROM @sql;
EXECUTE stmt;


END LOOP get_tbls;
DEALLOCATE PREPARE stmt;


CLOSE all_tbl_cursor;


END//
DELIMITER ;

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');

Conclusion:

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 🙂