Prepared Statements Mysql

Question

I have a prepared statement I want to execute direct to the MySQL server;

USE `testdb`;
SET @t = (
    SELECT GROUP_CONCAT(mytab.TABLE_NAME SEPARATOR ', ') 
    FROM (SELECT * FROM information_schema.TABLES as `m` WHERE table_schema = 'testdb' AND table_name LIKE 'mi_%' LIMIT 0,1) as `mytab`
    );
PREPARE `stmt1` FROM 'DROP TABLE t';
EXECUTE `stmt1` USING @t;
DEALLOCATE PREPARE `stmt1`;

However, when I execute it returns an error on the EXECUTE line saying

Error Code: 1210. Incorrect arguments to EXECUTE

I would appreciate any help on what I'm doing wrong.

I have mysql version 5.1.68

Answer 1

Your issue can be resolved with preparing fully static DDL before executing. Sample:

Tables:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| b              |
+----------------+
2 rows in set (0.00 sec)

And your (little modified) SQL:

mysql> SET @t = (SELECT GROUP_CONCAT(mytab.TABLE_NAME SEPARATOR ', ') FROM (SELECT * FROM information_schema.TABLES as `m` WHERE table_schema = 'test'
 AND table_name LIKE 'a%' LIMIT 0,1) as `mytab`);
Query OK, 0 rows affected (0.06 sec)

mysql> select @t;
+------+
| @t   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

Now, DDL:

mysql> set @drop = CONCAT('DROP TABLE ', @t);
Query OK, 0 rows affected (0.00 sec)

mysql> select @drop;
+--------------+
| @drop        |
+--------------+
| DROP TABLE a |
+--------------+
1 row in set (0.00 sec)

And, finally, prepared statement:

mysql> PREPARE `stmt1` FROM @drop;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `stmt1`;
Query OK, 0 rows affected (0.00 sec)

You'll get:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| b              |
+----------------+
1 row in set (0.00 sec)

You can use only one variable (I've added @drop to increase readability)