MySQL: Can't create table (errno: 150)

Question

I am trying to import a .sql file and its failing on creating tables.

Here's the query that fails:

CREATE TABLE `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;    

I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing?

MySQL: Can't create table './dbname/data.frm' (errno: 150)

Answer 1

I had a similar problem when dumping a Django mysql database with a single table. I was able to fix the problem by dumping the database to a text file, moving the table in question to the end of the file using emacs and importing the modified sql dump file into the new instance.

HTH Uwe

Answer 2

I had same issue. It was related to table's column Collation and Character Set. Make sure Character Set and Collation must be same for both columns on two tables. If you want to set a foreign key on that. Example- If you put foreign key on userID column of userImage table referencing userID column of users table.Then Collation must be same that is utf8_general_ci and Character set utf8 for both columns of tables. Generally when you create a table mysql takes these two configuration from server settings.

Answer 3

I had a similar problem but mine was because i was adding a new field to an existing table that had data , and the new field was referencing another field from the parent table and also had the Defination of NOT NULL and without any DEFAULT VALUES. - I found out the reason things were not working was because

1. My new field needed to autofill the blank fields with a value from the parent table on each record, before the constraint could be applied. Every time the constraint is applied it needs to leave the Integrity of the table data intact. Implementing the Constraint (Foreign Key) yet there were some database records that did not have the values from the parent table would mean the data is corrupt so MySQL would NEVER ENFORCE YOUR CONSTRAINT

It is important to remember that under normal circumstances if you planned your database well ahead of time, and implemented constraints before data insertion this particular scenario would be avoided

The easier Approach to avoid this gotcha is to

• Save your database tables data
• Truncate the table data (and table artifacts i.e indexes etc)
• Apply the Constraints
• Import Your Data

I Hope this helps someone

Answer 4

There are quite a few things that can cause errno 150, so for people searching this topic, here is what I think is a close to exhaustive list (source Causes of Errno 150):

For errno 150 or errno 121, simply typing in SHOW ENGINE INNODB STATUS, there is a section called "LATEST FOREIGN KEY ERROR". Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. You need SUPER privileges to run it, so if you don't have that, you'll just have to test out the following scenarios.

1) Data Types Don't Match: The types of the columns have to be the same

2) Parent Columns Not Indexed (Or Indexed in Wrong Order)

3) Column Collations Don't Match

4) Using SET NULL on a NOT NULL Column

5) Table Collations Don't Match: even if the column collations match, on some MySQL versions this can be a problem.

6) Parent Column Doesn't Actually Exist In Parent Table. Check spelling (and perhaps a space at the beginning or end of column)

7) One of the indexes on one of the columns is incomplete, or the column is too long for a complete index. Note that MySQL (unless you tweak it) has a maximum single column key length of 767 bytes (this corresponds to a varchar(255) UTF column)

In case you get an errno 121, here are a couple of causes:

1) The constraint name you chose is already taken

2) On some systems if there is a case difference in your statement and table names. This can bite you if you go from one server to another that have different case handling rules.

Answer 5

I experienced this error when have ported Windows application to Linux. In Windows, database table names are case-insensitive, and in Linux they are case-sensitive, probably because of file system difference. So, on Windows table Table1 is the same as table1, and in REFERENCES both table1 and Table1 works. On Linux, when application used table1 instead of Table1 when it created database structure I saw error #150; when I made correct character case in Table1 references, it started to work on Linux too. So, if nothing else helps, make you sure that in REFERENCES you use correct character case in table name when you on Linux.

Answer 6

A real edge case is where you have used an MySQL tool, (Sequel Pro in my case) to rename a database. Then created a database with the same name.

This kept foreign key constraints to the same database name, so the renamed database (e.g. my_db_renamed) had foreign key constraints in the newly created database (my_db)

Not sure if this is a bug in Sequel Pro, or if some use case requires this behaviour, but it cost me best part of a morning :/