A stored procedure required to bulk delete tables that are more than 1 hour old

Question

I have created the following MySQL (v5.7.14) stored procedure. It successfully DROPS all the tables in my database that are prefixed 'members_list_'. However, I would only like to DROP the tables that are prefixed 'members_list_' AND are older than 1 hour.

BEGIN
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables 
FROM information_schema.tables 
WHERE table_schema = 'my_database_name' 
AND table_name LIKE 'members_list_%'; -- AND CREATE_TIME < (NOW() - INTERVAL 1 HOUR);

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END

You will see that I have dummied-out a command in the WHERE clause. It uses the CREATE_TIME feature but when I 'make-live' that segment of code (by removing the '; --' part), I get the following error message:

Procedure execution failed 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

Is it even possible to bulk delete prefixed tables that are older than 1 hour?

Answer 1

To answer my own question. The reason for the '1064' error message was that the GROUP_CONCAT string length has a default limit of 1024 characters. This meant that because I was sometimes deleting a lot of tables, this 1024 limit was breached.

To rectify this I needed set the GROUP_CONCAT command to a much higher number, thus:

SET SESSION group_concat_max_len = 1000000;

The stored procedure worked fine after this. Here is my whole revised stored procedure. It also includes a 'CASE' routine that will ensure that it doesn't fail if a NULL condition is met.

BEGIN
SET SESSION group_concat_max_len = 1000000;

SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables FROM information_schema.tables 
    WHERE table_schema = 'indexingwebsite2017' AND table_name LIKE 'ma_members_list_%' AND CREATE_TIME < (NOW() - INTERVAL 1 HOUR);

CASE 
    WHEN ISNULL(@tables) THEN 
        SELECT 'NULL VALUE DETECTED' AS Verification;
    ELSE 
        SET @tables = CONCAT('DROP TABLE ', @tables);
        select @tables;
        PREPARE stmt1 FROM @tables;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1; 
END CASE;

END