Event Scheduler for temp tables and rows with their names

Question

I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO what I wanted to do is to create event scheduler that would drop temp tables that were created more than a day ago...For that purpose I am writing something like

SELECT (DROP) 
    *
FROM
    information_schema.TABLES
WHERE 
    table_schema = 'db' and CREATE_TIME < (NOW()-INTERVAL 24 HOUR)

But I also wanted to delete the rows containing just removed table names inside cached_tbl table... Please advise how I can do it... In the regular programming I would just through all the selected table_names from previous script into the array and go through the loop comparing this table names to the table names in cached_tbls... But I have no idea how implement it in mysql

score 0 · Answer 1 · answered Oct 12 '18 at 11:06

Hi and welcome to SO.

Answering your question - you shouldn't do it in that way (going through list of temp tables etc.). Generally speaking temp tables only serve for your current connection (session) and when you close one - all your temp tables are gone. It's normal. Temp tables should serve you as some kind of additional cache for your logic - when you need to export results from one procedure to another for example.

But you need to remember that SQL is used for persistent storage of data and its management, and if you want to store data - use ordinary tables and MySQL will do the rest. Use TRIGGERs or STORED PROCEDUREs for checking/flushing data from tables.

Speaking more specifically about temp tables names. Checking manual - https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html - you would see that MySQL stores names of temp tables on its own

CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 194
                NAME: #sql7a79_1_0
              N_COLS: 4
               SPACE: 182
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE

It's not a t1 table, but #sql7a79_1_0. You can say - wait, i know that table's name is t1! - but you should remember that t1 is just a some kind of pointer that you use in your current session and your own purpose, MySQL relies on its own mechanisms.

score 0 · Answer 2 · answered Oct 17 '18 at 20:39

Alisa, I'm assuming that you are using the naming convention temp_1, temp_2 to indicate that you intend to delete these tables after only a short time and that these are not TEMPORARY tables as surmised by @anton.

A few points to note ab:out the example below:

It's written as an event but you could easily turn it into a procedure which would allow you to pass in different interval values or database name.

You'll need to use a cursor to fetch the table names and dynamic SQL to prepare and execute statements to drop the tables and delete the records in cached_tbl.

I've made it a little more complex than strictly necessary to illustrate how you could create and populate a TEMPORARY table in one statement which would be useful if you did turn this into a procedure. You can simplify it by declaring the cursor as the the statement in @sql1 and removing the prepare and execute on stmt1.

You probably want to be a bit more restrictive in selecting tables otherwise you run the risk of deleting cached_tbl or other database tables by accident.

DELIMITER //

CREATE EVENT prune
ON SCHEDULE
  EVERY 1 DAY
DO

  BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE tname VARCHAR(64);
    DECLARE cur CURSOR FOR SELECT * FROM `tables_tmp`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET @sql1 = 
      'CREATE TEMPORARY TABLE `tables_tmp`  
       SELECT `table_schema`,`table_name`  
       FROM `information_schema`.`tables`
       WHERE `table_schema` = ''db''
       AND `table_name` LIKE ''temp_%'' 
       AND `create_time` < (NOW() - INTERVAL 1 DAY)';

    PREPARE stmt1 FROM @sql1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    OPEN cur;

    myloop:LOOP
      FETCH cur INTO tname;

      IF done = TRUE THEN
        LEAVE myloop;
      END IF;

      SET @sql2 = CONCAT('DROP TABLE IF EXISTS `db`.`', tname , '`;'); 
      PREPARE stmt2 FROM @sql2;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;

      SET @sql3 = CONCAT('DELETE FROM `cached_tbl` WHERE `table_name` = ''', tname , ''';'); 
      PREPARE stmt3 FROM @sql3;
      EXECUTE stmt3;
      DEALLOCATE PREPARE stmt3;

    END LOOP;

  END //

DELIMITER ;

Event Scheduler for temp tables and rows with their names

2 Answers2