Xaprb

Stay curious!

MySQL’s ERROR 1025 explained

with 24 comments

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:

innotop FK screenshot

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.

Written by Xaprb

August 22nd, 2006 at 12:44 pm

Posted in Innotop, SQL

«
»

24 Responses to 'MySQL’s ERROR 1025 explained'

Subscribe to comments with RSS

  1. Was looking for a answer to this!

    flashman

    19 Dec 06 at 1:40 am

  2. “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.

    Dwight

    29 Jan 07 at 3:32 am

  3. 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.

    Xaprb

    29 Jan 07 at 8:44 am

  4. Heh – the problem I’m having is that I’m getting this error when I try to drop the offending foreign key constraint!

    Al Davidson

    16 Mar 07 at 9:09 am

  5. Hmm, that’s odd and may be a bug. Check SHOW INNODB STATUS and see if there are any hints there.

    Xaprb

    16 Mar 07 at 9:13 am

  6. 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

    Anonymous

    9 May 07 at 11:24 am

  7. Very Very Informative!

    Alnoor

    23 May 07 at 2:00 pm

  8. Anon’s comment helped. Is this a bug???

    Cheers,
    Rohitesh.

    Rohitesh

    16 Jul 07 at 2:50 pm

  9. 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.

    Xaprb

    16 Jul 07 at 9:50 pm

  10. 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

    nimrod-

    23 Jul 07 at 6:45 pm

  11. 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.

    Trenton

    19 Sep 07 at 5:38 pm

  12. 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.

    Xaprb

    19 Sep 07 at 5:49 pm

  13. 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 :)

    Karuna (kgx)

    1 Oct 07 at 7:19 pm

  14. 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.

    Carlos Seo

    4 Jan 08 at 8:09 am

  15. 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,

    Fuvito

    22 Jan 08 at 4:20 pm

  16. 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……..
    :)

    Monalisa

    12 Feb 08 at 2:05 am

  17. Trenton, thanks a lot, really helped!

    Sumana

    9 Dec 08 at 10:02 pm

  18. Way pointed by Trenton is the correct approach for dropping a foreign key constraint.

    First you need to drop the foreign key constraint using its internal name and then using the visible name – yes that is quite confusing and I wonder why MySQL never worked on it to make it simple.

    For more details, one can visit the link pointed by Trenton in same comment (http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/#comment-13404).

    Th for such detailed discussion.

    Nitesh

    19 Jan 09 at 3:38 am

  19. Thanks a lot, I had a similar problem, Trenton’s specific solution solved my problem!

    Barry

    3 Jun 09 at 12:23 pm

  20. I got a similar error message trying to drop a unique key on 3 columns, 2 of which had relationships to other tables. In phpmyadmin I modified the unique key into an index and chose “ignore” for 2 of the columns (I could do that because it was meaningful to have an index on the remaining key). The update ran without any error. But I agree MySQL error message sucks!

    Xavier

    29 Jun 09 at 5:13 am

  21. I was unable to delete the foreign key myself. I believe this issue was due to the fact that I forward engineered (created) the database using mysql work bench.

    I simply re-created the tables (by copying the old ones), and i was then able to delete foreign keys without any problems.

    tison

    5 Nov 09 at 9:04 pm

  22. MySql errors can often be a source of frustation so It is good that you have documented resolutions for some of them.

    C2 Web Design

    28 Jan 10 at 8:18 am

  23. You simply put this commmand :

    DELETE FROM [target_table]
    INSERT IGNORE INTO [target_table] SELECT * FROM [source_table]

    ENJOY IT !

    Uno

    21 Apr 10 at 4:56 am

  24. You can also get this error if you get the foreign_key name wrong. Use ’show create table’ on the table you’re trying to drop the key for and see if it’s named what you think it is.

    wolfalohalani

    29 Apr 10 at 2:28 pm

Leave a Reply