Description:
A prepared statement supports the ALTER TABLE statement according to the MySQL 5.5 Manual, 12.6 SQL Syntax for Prepared Statements, but you can't submit the following syntax as a prepared statement against an InnoDB table:
SET @stmt := 'ALTER TABLE ? DROP FOREIGN KEY ?';
It raises the following error stack:
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 '? DROP FOREIGN KEY ?' at line 1
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
How to repeat:
Here's a test case script that demonstrates the error:
SET FOREIGN_KEY_CHECKS = 0;
-- Conditionally drop objects.
SELECT 'GRANDMA' AS "Drop Table GRANDMA";
DROP TABLE IF EXISTS grandma;
-- ------------------------------------------------------------------
-- Create GRANDMA table.
-- ------------------------------------------------------------------
SELECT 'GRANDMA' AS "Create Table GRANDMA";
CREATE TABLE grandma
( grandma_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, grandma_name CHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Conditionally drop objects.
SELECT 'TWEETY' AS "Drop Table TWEETY";
DROP TABLE IF EXISTS tweety;
-- ------------------------------------------------------------------
-- Create TWEETY table.
-- ------------------------------------------------------------------
SELECT 'TWEETY' AS "Create Table TWEETY";
CREATE TABLE tweety
( tweety_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, grandma_id INT UNSIGNED
, tweety_name CHAR(20) NOT NULL
, KEY tweety_fk1 (grandma_id)
, CONSTRAINT tweety_fk1 FOREIGN KEY (grandma_id)
REFERENCES grandma (grandma_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/* Set statement and variables. */
SELECT 'Set query into @sv_constraint session variable';
SET @sql := 'SELECT constraint_name INTO @sv_constraint FROM information_schema.referential_constraints WHERE table_name = ?';
SET @sv_table := 'tweety';
-- Select session variable value.
SELECT @sv_constraint;
/* Prepare, execute, and deallocate prepare. */
PREPARE stmt FROM @sql;
EXECUTE stmt USING @sv_table;
DEALLOCATE PREPARE stmt;
/* Set statement and variables. */
SET @sql := 'ALTER TABLE ? DROP FOREIGN KEY ?';
/* Prepare, execute, and deallocate prepare. */
PREPARE stmt FROM @sql;
EXECUTE stmt USING @sv_table, @sv_constraint;
DEALLOCATE PREPARE stmt;
-- Alter the table and drop a foreign key.
SELECT 'ALTER TABLE tweety DROP FOREIGN KEY tweety_fk1' AS "Statement";
ALTER TABLE tweety DROP FOREIGN KEY tweety_fk1;
Suggested fix:
Enable prepared statements to run the 'ALTER TABLE t DROP FOREIGN KEY fk' statement or change the documentation.
