Resolve error 150 while restoring backup in MySQL Database

MySQL Tutorials

If you are getting error while importing or restoring the database dump like as mentioned below :

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

This may come due to foreign key constraints defined in tables.  So If in backup file Referencing table is created before the Referenced table than this will throw error. So, in this case, you will need to make MySQL stop checking foreign key constraints while importing/restoring the backup.

So How to resolve it ? 

We can resolve it using below commands

SET FOREIGN_KEY_CHECKS=0;

You can also debug the issue by using below command if you have Process Privilege on the database.

SHOW ENGINE INNODB STATUS;

If you don’t have the process privilege than you will need to have one otherwise it will show you error like :

[SQL] SHOW ENGINE INNODB STATUS;
[Err] 1227 - Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

Other reason can also be involved like:

  1. Storage engine types are different.
  2. All referenced keys MUST have either PRIMARY or UNIQUE index.
  3. Referencing Column MUST have identical data type to the Referenced column.
  4. Backup using the same constraint name which is already taken by other foreign keys.
  5. Column Collations Don’t Match.