MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Decoding 1005 on MySQL

with 17 comments

If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.

The error by itself isn’t very handy. This is a sample:

ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150)

You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.

The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.

Written by maclochlainn

January 30th, 2010 at 6:58 pm

Posted in InnoDB,MySQL,sql

17 Responses to 'Decoding 1005 on MySQL'

Subscribe to comments with RSS or TrackBack to 'Decoding 1005 on MySQL'.

  1. Thanks for the valuable post. i could resolve the similar issue at my end following the set of instructions.

    SinyL

    14 Oct 10 at 4:46 am

  2. Thanks for your post, it helps me to fix my problem.

    Xavier

    18 Nov 10 at 4:18 am

  3. […] Google hunting led me to MacLochlainns Weblog which stated some helpful hints on how to check the status of the innodb engine […]

  4. Thanks!! That save really helped.

    GAA

    2 Feb 11 at 12:50 pm

  5. Another reason that this can happen is if you’ve accidentally created one of the two tables as MyISAM, rather than INNODB (doh!).

    George Lund

    23 Mar 11 at 6:02 am

  6. thanks a lot for the post

    joel varghese jacob

    11 May 11 at 10:09 am

  7. Thanks a lot, this has helped me tremendously.

    Clikc

    30 Oct 11 at 7:24 am

  8. Adding to the thank you! This helped solve my problem. An index needed to be created on my source table.

    Troy

    2 Nov 11 at 2:02 am

  9. You can find more in this comment.

    maclochlainn

    3 Nov 11 at 5:23 pm

  10. THANX A LOT FAM. MY LECTURER COULDN’T EVEN SOLVE IT :D.

    Radar

    27 Nov 11 at 5:49 am

  11. You can always refer your lecturer to my Oracle Database 11g and MySQL 5.6 book. ;-) For reference, I maintain an active comment section on my blog for the book and suggested corrections.

    maclochlainn

    27 Nov 11 at 9:12 am

  12. Thanks for these hints.

    Try checking the table storage engine: you cannot point a constraint to an ISAM table!

    Kate

    9 Jan 12 at 10:46 am

  13. I had the same problem, caused by different encodings. Thanks for the post and all the replies.

    Jorge Maturana

    2 May 12 at 1:03 am

  14. I had the same issue as the last poster: DIFFERENT ENCODINGS on the tables. One table was ‘latin1′ (don’t know how it got created as latin1 to begin with, but it was) and the other was ‘utf8′.

    Kevin Rohr

    9 Jul 12 at 1:19 pm

  15. If you are experiencing this problem while loading data from a dump file, try disabling FK checks at the beginning of the file:

    SET foreign_key_checks = 0;

    then enable FK checks back:

    SET foreign_key_checks = 1;

    Cheers,
    MattS

    MattS

    12 Feb 13 at 3:25 pm

  16. I can also appeare when the table you try to apply the foreign to is not innoDB

    jb681131

    28 Jan 14 at 1:46 am

  17. After much struggling with this on a test DB, found out that this same error will show up if the referenced table is not the same storage type as the table you are connecting.

    In my case the tables had all originally been MyISAM, and got converted to InnoDB except for one. Changing that one table to Engine=InnoDB fixed my issue.

    Rob Weaver

    16 Feb 14 at 1:37 pm

Leave a Reply