MySQL use cursor to clone existing tables

Question

I'm trying to write a MySQL stored proceedure that loops through all existing tables in my database and creates a copy/clone of each table. I'm using a cursor to loop through the table names then create a new table like this:

DELIMITER //
CREATE PROCEDURE CopyTables()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE tableName VARCHAR(100);
    DECLARE copyTableName VARCHAR(100);

    DECLARE curTables
        CURSOR FOR
            SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN curTables;
    create_loop: LOOP
        FETCH curTables INTO tableName;
        IF finished THEN LEAVE create_loop; END IF;
        SELECT concat('Processing table ', tableName);
        SET copyTableName = CONCAT('copy_',tableName);
        SELECT concat('Creating table ', copyTableName);
        CREATE TABLE copyTableName LIKE tableName;
    END LOOP;
    CLOSE curTables;
END //
DELIMITER;

But I get an error when calling the stored procedure:

> call CopyTables()
[2020-12-08 18:16:03] 1 row retrieved starting from 1 in 77 ms (execution: 15 ms, fetching: 62 ms)
[2020-12-08 18:16:03] [S1000] Attempt to close streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@7a714591 that was not registered. Only one streaming result set may be open and in use per-connection. Ensure that you have called .close() on  any active result sets before attempting more queries.

Is the result set exception effectively complaining because I'm creating new tables which is effectively messing with the cursor/select? I've got additional table changes on both the original and copied table to perform, like adding new columns, creating triggers, modifying constraints.

The list of table names is not static, and this should be able to run on whatever database I need it.

Can you suggest another way to achieve this without the cursor perhaps?

Bill Karwin Bill Karwin · Accepted Answer · 2020-12-08T18:44:36

The problem is that the procedure is returning multiple result sets, but your Java client is not handling that correctly.

Refer to How do you get multiple resultset from a single CallableStatement?

Another problem with your procedure is that you aren't creating tables the way you think you are.

This statement:

CREATE TABLE copyTableName LIKE tableName;

will only create a table named literally copyTableName that is like another table that is literally tableName. It will NOT use the values of variables by those names.

To do what you want, you need to use a prepared statement:

SET @sql = CONCAT('CREATE TABLE `', copyTableName, '` LIKE `', tableName, '`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This way the value of your variables is concatenated into an SQL statement.

Note that PREPARE only accepts a user-defined session variable, the type with the @ sigil. It doesn't work with local variables you create in your procedure with DECLARE. Read https://dev.mysql.com/doc/refman/8.0/en/prepare.html and https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

MySQL use cursor to clone existing tables

1 Answers