How to use PREPARE and EXECUTE in Stored Procedure backup script (MySQL)

Question

I have a Stored Procedure which loops through all the tables in a schema (customerinfo) and makes a backup of them by renaming them to include the date and copying them to another schema (customerinfo_bak).

That looks as follows

DELIMITER $$
CREATE PROCEDURE backup_tables()
BEGIN
  DECLARE DONE INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cursorController CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'customerinfo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
  
  OPEN cursorController;
  CREATE DATABASE IF NOT EXISTS customerinfo_bak;
  REPEAT
  
    SET @backup_table = CONCAT('customerinfo_bak.', tableName, '_backup_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
    SET @sqlstmt = CONCAT('CREATE TABLE ', @backup_table, ' LIKE customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @sqlstmt = CONCAT('INSERT INTO ', @backup_table, ' SELECT * FROM customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  
  UNTIL DONE 
  END REPEAT;
  
  CLOSE cursorController;

END$$
DELIMITER ;

The issue that I'm having is that the SP saves and is visible in my schema, but upon calling it with CALL customerinfo_bak.backup_tables(); I get the following error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

I have a suspicion that it's to do with how I use my Local and Session variables in the PREPARE section based on this MySQL Error 1064 : near NULL at line 1 answer from Bill Karwin, but I don't understand why the error would appear as a NULL syntax issue.

If someone has any idea why I can't call my SP that would be greatly appreciated.

Answer 1

Ok so I found the answer (thanks ChatGPT) it turns out it's due to a very simple oversight. The fact that the 'tableName' variable is not being populated within the loop. When 'tableName' is NULL, the concatenation with NULL results in NULL, which causes the SQL syntax error.

The corrected code looks as follows:

DELIMITER $$
CREATE PROCEDURE backup_tables()
BEGIN
  DECLARE DONE INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cursorController CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'customerinfo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
  
  OPEN cursorController;
  CREATE DATABASE IF NOT EXISTS customerinfo_bak;
  REPEAT
    FETCH cursorController INTO tableName;
    
    IF NOT DONE THEN
      SET @backup_table = CONCAT('customerinfo_bak.', tableName, '_backup_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
      SET @sqlstmt = CONCAT('CREATE TABLE ', @backup_table, ' LIKE customerinfo.', tableName);
      PREPARE stmt FROM @sqlstmt;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      # Prepare and execute INSERT INTO statement
      SET @sqlstmt = CONCAT('INSERT INTO ', @backup_table, ' SELECT * FROM customerinfo.', tableName);
      PREPARE stmt FROM @sqlstmt;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;
  
  UNTIL DONE 
  END REPEAT;
  
  CLOSE cursorController;

END$$
DELIMITER ;

Changes:

1. Added FETCH cursorController INTO tableName; to fetch the table name from the cursor into the tableName variable.
2. Added an IF NOT DONE THEN check to ensure that the cursor fetch did not return a NULL value before proceeding with the rest of the loop.

For anyone who wants to use this method as an automated backup script, you can create it as an event and have it scheduled to run in intervals by calling the SP in an event.

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY  -- Adjust schedule as needed
DO
  CALL backup_tables();