how to use vars in the command "create partition" with mysql

Question

I want to create an event that every 15 days executes a procedure. This procedure must to create a new partition. I am doing the following:

/*Vars
v_tab  --> logic tables's name
v_year --> year... to use it as part of name
v_fech --> this is used to get the current date  plus 15 days
v_day  --> this is the numbers of days to add to the current date
*/

DROP PROCEDURE IF EXISTS CreatePartition;
delimiter //
CREATE PROCEDURE CreatePartition (IN v_day INT)
BEGIN
DECLARE v_year INT;
DECLARE v_fech DATE;
DECLARE v_tab VARCHAR(20);

SET v_year = (select curdate()+0);
SET v_fech = (DATE_ADD(CURDATE(), INTERVAL v_day DAY));
SET v_tab = (select concat('p',v_year));  <--- the char p is because it doesn't accept name with only numbers

ALTER TABLE SystemEvents PARTITION BY RANGE(TO_DAYS(DeviceReportedTime))(
                PARTITION v_tab VALUES LESS THAN (TO_DAYS("v_fech")),                               
                PARTITION pDefault VALUES LESS THAN MAXVALUE  
                );
END
//
delimiter ;

If I exec this procedure it works, but if I exec it inside an event, it doesn't work

call CreatePartition(15);

The following doesn't work and I don't know why

CREATE EVENT EventPruebas
ON SCHEDULE
EVERY 15 DAY
DO
call CreatePartition(15);

and I get this error...

ERROR 1064 (42000): Not allowed to use NULL value in VALUES LESS THAN near '),
PARTITION p230101 VALUES LESS THAN MAXVALUE

score 1 · Answer 1 · answered Jun 06 '13 at 18:36

TO_DAYS("v_fech") is not referencing a variable named v_fech at all. It's trying to convert a literal string "v_fech" to a number of days. Of course, that string is not a valid date expression, so TO_DAYS() can only return NULL.

Likewise your reference to v_tab is probably not using the value of the variable v_tab, it's using the actual identifier v_tab.

To do what you want to do, you'll have to use dynamic SQL with PREPARE and EXECUTE.

Something like the following should work (though I have not tested it).

PREPARE stmt FROM CONCAT('ALTER TABLE SystemEvents 
    PARTITION BY RANGE(TO_DAYS(DeviceReportedTime))(
        PARTITION ', v_tab, ' VALUES LESS THAN (TO_DAYS(''', v_fech, ''')),
        PARTITION pDefault VALUES LESS THAN MAXVALUE  
    )');
EXECUTE stmt;

how to use vars in the command "create partition" with mysql

1 Answers1