MySQL Stored Procedure fails with ERROR 1064 (42000)

Question

I have written a MySQL stored procedure that will add a new partition to an existing table:

DELIMITER //

DROP PROCEDURE IF EXISTS cr_par //

CREATE PROCEDURE cr_par (
    IN p_table VARCHAR(256),
    IN p_date DATE
) BEGIN

    DECLARE stmt      VARCHAR(1024);
    DECLARE ddl       VARCHAR(512);
    DECLARE par_name  VARCHAR(20) DEFAULT '';
    DECLARE par_no    INT DEFAULT 0;
    DECLARE lt_value  INT DEFAULT 0;
    
    SET par_no   = TO_DAYS(p_date) + 1;
    SET par_name = CONCAT('p', par_no);
    SET lt_value = par_no + 1;
    
    SET ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT ddl;

END //

DELIMITER ;

When I run the stored procedure I get this error:

mysql> CALL cr_par('test', '2021-09-13');
ERROR 1064 (42000): 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

If I comment out the PREPARE, EXECUTE and DEALLOCATE statements and re-run the stored procedure I get this, which is a valid DDL statement:

mysql> CALL cr_par('test', '2021-09-13');
+------------------------------------------------------------------------------+
| ddl                                                                          |
+------------------------------------------------------------------------------+
| ALTER TABLE test ADD PARTITION (PARTITION p738412 VALUES LESS THAN (738413)) |
+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

I've also tried these variations and all return the same error:

    SET ddl = 'ALTER TABLE test ADD PARTITION (PARTITION p738412 VALUES LESS THAN (738413));';

    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

and this...

    SET ddl = CONCAT('ALTER TABLE ? ADD PARTITION (PARTITION ? VALUES LESS THAN (?))');
    
    PREPARE stmt FROM @ddl;
    EXECUTE stmt USING @p_table, @par_no, @lt_value;
    DEALLOCATE PREPARE stmt;

I am using this version of MySQL:

Server version: 8.0.25-15 Percona Server (GPL), Release 15, Revision a558ec2

Does anyone have any ideas what could be causing this, I must be missing something simple?

Answer 1

SET ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

PREPARE stmt FROM @ddl;

Please be aware that ddl and @ddl are two different variables.

The variables you declare with the local variable DECLARE statement have a scope within the body of one stored routine. They are never spelled with a @ sigil.

The user-defined variables with the @ sigil have a scope of a MySQL session. You don't need to declare these kinds of variables. Just setting the variable to a value implicitly creates the variable.

You cannot SET ddl = ... and expect that string to be read from the @ddl variable. Nor vice-versa.

The PREPARE statement only supports preparing an SQL from a user-defined variable. Which means you must set the @ddl variable to your SQL statement:

SET @ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

Then you don't need to DECLARE ddl at all, since there's no use for that variable.