Concat and Drop in Mysql 5.6 with more thant one row

Question

I do maintain a website that is becmming real old - using vBulletin.

For Some time the DB does create temp table (taggregate_temp) and i do have a real good chunk of them (some hundreds) that slows the server a lot ...

I did some search to find a way to get rid of those temp tables.

And tried to use (with modificiations) some of the query i did find ...

This one would be perfect but.. i do get a = error 1172 - results contain more than one row

and I dont find, where the limit of row is:

    SET @tables = NULL;
    SELECT CONCAT(table_schema, '.`', table_name, '`') INTO @tables FROM
    (select * from
     information_schema.tables 
      WHERE table_schema = 'mydatabase' AND table_name LIKE 'taggregate_temp%' ESCAPE '\\'
      LIMIT 10000) TT;

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

In advance, thanks. Hoping to cleanse the Db from those multiplying temp tables (before moving and upgrading the forum in some time.)

Answer 1

Here is the code with modifications to address the error and drop the temporary tables:

SET @drop_tables = NULL;

SELECT GROUP_CONCAT(CONCAT('`', table_name, '`')) INTO @drop_tables
FROM information_schema.tables 
WHERE table_schema = 'mydatabase' AND table_name LIKE 'taggregate_temp%' ESCAPE '\\';

SET @drop_query = CONCAT('DROP TABLE ', @drop_tables);

PREPARE stmt FROM @drop_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This code retrieves the names of all temporary tables that match the pattern 'taggregate_temp%' in the 'mydatabase' schema and concatenates them into a single DROP TABLE query. Then, it prepares and executes the DROP query to remove the temporary tables.