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?

Are you sure you are remembering to remove the ';' before the '--'? I copied your query, edited it as needed, and tested it, and it works fine. Have you tried printing out whatever you get from the select to see if it matches what you expect? — ghenghy, May 24 '17 at 00:00
Yes, the ';' is definitely being removed. The standalone command (below) does NOT drop the tables: I've used the Correct DB. SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @TABLES FROM information_schema.TABLES WHERE table_schema = 'my_database_name' AND table_name LIKE 'ma_members_list_%' AND CREATE_TIME < (NOW() - INTERVAL 1 HOUR); When I execute this command it displays the table and Create Times: SELECT TABLE_NAME, CREATE_TIME FROM information_schema.TABLES WHERE table_schema = 'my_database_name'; Can you show your code (for comparison purposes)? — Alan N, May 24 '17 at 01:23

score 0 · Answer 1 · answered Jun 05 '17 at 17:25

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

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

1 Answers1