MySQL issues a cryptic error message, “Error on rename,” when you try to alter a table in such a way that it would break a foreign key constraint:
create table test1(a int not null primary key)engine=innodb; create table test2(a int not null, foreign key(a) references test1 (a)) engine=innodb; alter table test2 modify a smallint not null; ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/test2' (errno: 150)
This happens because ALTER TABLE really works by making a copy of the table, then renaming to move the old table out of the way and move the new table into its place. It is certainly one of the less meaningful error messages I’ve seen in MySQL.
There’s slightly more information in the output of SHOW ENGINE INNODB STATUS, if you are looking there (of course, if you’re looking there you’re probably already clued in to what’s going on). And innotop can parse that information for you:
In case you didn’t understand why the foreign key constraint was failing, the error message innotop parses out is much clearer. It’s because the foreign key columns in the parent and child table have to have the same data type. I was trying to change the child’s column to an incompatible type.
Technorati Tags:No Tags
Was looking for a answer to this!
“In case you didn’t understand why the foreign key constraint was failing, the error message innotop parses out is much clearer. It’s because the foreign key columns in the parent and child table have to have the same data type. I was trying to change the child’s column to an incompatible type.”
Then how can I make the change? Can you propose a solution?, This would greatly help me since I am currently in this scenario.
You probably need to remove the foreign key, change the column types, and then add it back. You won’t be able to change the column types until the foreign key is gone.
Heh - the problem I’m having is that I’m getting this error when I try to drop the offending foreign key constraint!
Hmm, that’s odd and may be a bug. Check SHOW INNODB STATUS and see if there are any hints there.
i found if i went into the foreign key tab and tried to remove it from thre it let me, but not from the indicies tab
Very Very Informative!
Anon’s comment helped. Is this a bug???
Cheers,
Rohitesh.
I’m not sure what Anon meant. If it was a comment about a GUI program, I’m not the one to ask; I do everything from the command-line.
i solved this problem by doing it at hand, it is, with other name i create a copy of the table that doesn’t have the definition of the foreign key, then copy all the data in this new table, then delete the old table, and so i couldn’t find a way to rename the new table, create again a table with the original name and place the data in the correct one. then delete the temporal table
I had a variant of this problem when trying to drop a foreign key column. In my scenario, customer is many-to-one with master_customer, and I wanted to remove master_customer.
This bug/thread http://bugs.mysql.com/bug.php?id=14347 has some info. The short of it is:
1) alter table customer drop foreign key customer_ibfk_1;
2) alter table customer drop column master_customer_id;
3) drop table master_customer;
works for me.
Ah yes, that’s another gotcha. There’s also another case where it won’t give you more information — in fact, it won’t give you any information — in SHOW INNODB STATUS. You can’t ALTER either the parent or child table to a different storage engine without first dropping the constraint, which as you point out has to be done by constraint name, not index name.
For those of you still looking for a solution, do this:
> SHOW CREATE TABLE table_name;
That will show the actual constraint name, which may differ from the column name.
Then simply do something like:
> ALTER TABLE table_name DROP FOREIGN KEY xxx_ibfk_d;
Replace ‘xxx_ibfk_d’ with the constraint name that you got above.
HTH :)
A great solution. I was thinking on delete some tables, change the data column type and create the same tables. I think this is a better solution.
Hi All,
I was having the same problem, I was trying to drop a column,
Thanks to Karuna, after reading his reply I noticed: I was trying to drop the index before dropping the foreign key,
Now it works fine with the following order:
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE tableX DROP FOREIGN KEY fkId;
ALTER TABLE tableX DROP INDEX idxId;
ALTER TABLE tableX DROP COLUMN columnX;
SET FOREIGN_KEY_CHECKS = 1;
in my case fkId and idxId were same,
Thanks all,
Hi All,
Can anybody help me out please?
Can I drop the foreign key and column in separate clauses of one SQL statement?
for example:
ALTER TABLE party
DROP FOREIGN KEY FK_party_advertiser_id,
DROP COLUMN advertiser_id;
Do help please……..
:)