CODEWITHSUNDEEP
mysqlalter-table
EvanK
EvanK

Reputation: 1072

Issue with multiple ALTER TABLE statements in a single transaction

I'm having issues executing multiple ALTER TABLE statements in the same transaction, on MySQL 5.1.41. There is another developer running the same code and having none of these issues on 5.1.49

Multiple ADD FOREIGN KEY statements on the same table work fine: 

ALTER TABLE school_state ADD FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE; 
ALTER TABLE school_state ADD FOREIGN KEY (state_id) REFERENCES state(id) ON DELETE CASCADE;

But across different tables, I get a cryptic error: 

ALTER TABLE school_state ADD FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE CASCADE; 
ALTER TABLE school_state ADD FOREIGN KEY (state_id) REFERENCES state(id) ON DELETE CASCADE; 
ALTER TABLE publisher_login ADD FOREIGN KEY (publisher_id) REFERENCES publisher(id); 

Can't create table 'my_database.#sql-2cd_45' (errno: 150)

Stranger still, multiple DROP FOREIGN KEY statements fail even across the same table: 

ALTER TABLE school_state DROP FOREIGN KEY school_state_ibfk_2; 
ALTER TABLE school_state DROP FOREIGN KEY school_state_ibfk_1; 

Error on rename of './my_database/school_state' to './my_database/#sql2-2cd-45' (errno: 152)

Am I really going to have to execute each single statement as its own transaction? Because that kind of defeats the purpose of having atomic transactions.

UPDATE: Per Ike's suggestion below, I've run SHOW ENGINE INNODB STATUS after the ADD FOREIGN KEY query above and gotten the following:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110630  8:01:12 Error in foreign key constraint of table my_database/#sql-2cd_95:
FOREIGN KEY (publisher_id) REFERENCES publisher(id):
Cannot resolve column name close to:
) REFERENCES publisher(id)

Honestly, I still don't understand what the issue is, as the key it's referencing (publisher.id) does exist, and the field intended to become a foreign key (publisher_login.publisher_id) also very much exists. These statements run fine one at a time, so why are they causing errors when run all at once?

Upvotes: 2

Views: 2885

Answers (1)

Ike Walker
Ike Walker

Reputation: 65567

DDL can't be rolled back, so you shouldn't be running it inside a transaction. You can read more about that in the manual.

As for the errors you are getting, you should run SHOW ENGINE INNODB STATUS\G and look at the LATEST FOREIGN KEY ERROR section to find out why it's failing.

Upvotes: 3

Related Questions