I have the following stored procedure which works when executed
by hand.
-- coding:utf-8 --
SQL_UP = u"""
DELIMITER $$
DROP PROCEDURE IF EXISTS DropFK $$
CREATE PROCEDURE DropFK (
IN parm_table_name VARCHAR(100),
IN parm_key_name VARCHAR(100)
) BEGIN
-- Verify the foreign key exists IF EXISTS (SELECT NULL FROM
information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN
-- Turn the parameters into local variables set @ParmTable =
parm_table_name ;
set @ParmKey = parm_key_name ;
-- Create the full statement to execute set @StatementToExecute =
concat('ALTER TABLE ',@ParmTable,' DROP FOREIGN KEY
',@ParmKey);
-- Prepare and execute the statement that was built prepare
DynamicStatement from @StatementToExecute ;
execute DynamicStatement ;
-- Cleanup the prepared statement deallocate prepare
DynamicStatement ;
END IF;
END $$
DELIMITER ;
"""
SQL_DOWN = u"""
"""
when I put it inside a migration and invoke db-migrate I get
...
~/Desktop/mysql/dbmigrate$ db-migrate
Starting DB migration...
- Current version is: 0 - Destination version is:
20120308020932
Starting migration up!
*** versions: ['20120228235709', '20120308003414',
'20120308003950', '20120308004106', '20120308020932']
===== executing 20120228235709_tools.migration (up) ===== =====
ERROR executing
/Users/alaindazzi/Desktop/mysql/dbmigrate/migrations/20120228235709_tools.migration
(up) ===== [ERROR] error executing migration: (1064, "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 'DELIMITER $$
\nDROP PROCEDURE IF EXISTS DropFK $$ \nCREATE PROCEDURE DropFK (
\nIN ' at line 1")
[ERROR DETAILS] SQL command was: DELIMITER $$
DROP PROCEDURE IF EXISTS DropFK $$
CREATE PROCEDURE DropFK (
IN parm_table_name VARCHAR(100),
IN parm_key_name VARCHAR(100)
) BEGIN
-- Verify the foreign key exists IF EXISTS (SELECT NULL FROM
information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA =
DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN
-- Turn the parameters into local variables set @ParmTable =
parm_table_name
~/Desktop/mysql/dbmigrate$
Is there a workaround for this issue? display the sql show the
proper code (--showsqlonly).
