MySQL Version 5.5.37: Dropping tables older than 30 days using CURSOR

Question

I've been researching trying to find a way to fetch all rows from a query result and process them individually. I've written a script which I thought would work but apparently not.

The script:

DECLARE @name char(20);

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT table_name
FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (now() - interval 30 day)

OPEN c1; 

FETCH NEXT FROM c1
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

PREPARE stmt FROM "concat('DROP TABLE IF EXISTS `', @table_name,'`;')"
EXECUTE stmt
DEALLOCTATE stmt

FETCH NEXT FROM c1
INTO @table_name

END

CLOSE c1
DEALLOCATE c1

The script is intended to drop all tables older than 30 days. Although it doesn't seem to work for MySQL Version 5.5.37.

I'm new to MySQL and I'm running a server with MySQL for Windows (XP). Perhaps this syntax for CURSORS isn't correct for the corresponding server version? I'm not sure but I'd be very happy if anyone could help me out.

EDIT:

This is the error message returned when I try to execute the script from the SQL Command Line:

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 'DECLARE @name char(20)' at line 1

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 'DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM information_schema.tables' at line 1

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 'FETCH NEXT FROM c1 INTO @table_name

WHILE @@FETCH_STATUS = 0 BEGIN

PREPARE st' at line 1

UPDATE:

I also tried this script (NOTE: I intend to create a event which will be executed daily to remove tables older than 30 days.):

delimiter |

CREATE EVENT clean_logger
    ON SCHEDULE EVERY 1 DAY
    DO
    BEGIN
        DECLARE @name char(20);
    DECLARE c1 CURSOR READ_ONLY
    FOR
    SELECT table_name
    FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (now() - interval 30 day);

    OPEN c1; 

    FETCH NEXT FROM c1
    INTO @table_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    PREPARE stmt FROM "concat('DROP TABLE IF EXISTS `', @table_name,'`;')";
    EXECUTE stmt;
    DEALLOCTATE stmt;

    FETCH NEXT FROM c1
    INTO @table_name;

    END;

CLOSE c1;
DEALLOCATE c1;
END |

delimiter ;

Running this script in the SQL Command Line returns:

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 '@name char(20); DECLARE c1 CURSOR READ_ONLY FOR SELECT table_name FROM infor' at line 5

Statement delimiters (`;`) are missing from your code, did you strip them before posting, or are they really missing from your original code? Also, `DECLARE` works only from within a stored procedure/function, did you also strip the function/pocedure headers for brevity? — RandomSeed, Apr 11 '14 at 11:50
In addition to @RandomSeed's points, you need to define what "doesn't seem to work" means. What error or behavior do you get? — , Apr 11 '14 at 11:54
No, I posted the whole script. I save it as a sql file and execute from the SQL Command Line. So yeah, the statement delimiters are missing. I'm not sure how to properly put this idea into a working script. — Linus, Apr 11 '14 at 11:55
@dan1111 I've added the error message returned by executing the script. (See edit) — Linus, Apr 11 '14 at 12:04

score 2 · Accepted Answer · answered Apr 11 '14 at 12:49

Try below procedure:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_drop_table`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_drop_table`()
BEGIN
        DECLARE done INT(1) DEFAULT 0;
        DECLARE _tblname VARCHAR(20) DEFAULT '';

        DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'puslogger' AND UPDATE_TIME < (NOW() - INTERVAL 30 DAY);
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- open cursor1
OPEN cur1;
BEGIN

REPEAT
FETCH cur1 INTO _tblname;
IF _tblname = '' THEN
    SET done = 1;
END IF; 

            IF (done<>1) THEN
    SET @str1=CONCAT("DROP TABLE IF EXISTS ",_tblname);
                 PREPARE stmt1 FROM @str1;
                 EXECUTE stmt1;
                 DEALLOCATE PREPARE stmt1;

            END IF;

UNTIL done
END REPEAT;
END;
     CLOSE cur1;
-- close cursor1     

     SELECT 'done';

END$$

DELIMITER ;

Thank you very much, I will certainly try this, and if it works out, I'll accept this as the best answer :) — Linus, Apr 11 '14 at 13:11
That's awesome it worked, I just had to change `SET @str1=CONCAT("DROP TABLE IF EXISTS ",_tblname);` to ``SET @str1=CONCAT("DROP TABLE IF EXISTS `",_tblname,"`");`` Thank you again. — Linus, Apr 11 '14 at 13:34

MySQL Version 5.5.37: Dropping tables older than 30 days using CURSOR

1 Answers1