Error 150 when trying to import .sql file to phpmyadmin

Question

I am trying to import a file *.sql from MySQL database on my server backup to new webhosting. I try to import via phpMyAdmin. I tried running XAMPP on local machine and import and am still getting the same error. Here is the create table statement:

CREATE TABLE `yv3nd_rokgallery_file_loves` (
   `file_id` int(10) unsigned NOT NULL DEFAULT '0',
   `kount` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`file_id`),
   UNIQUE KEY `file_id` (`file_id`),
   CONSTRAINT `yv3nd_file_loves_file_id_files_id` FOREIGN KEY (`file_id`) 
      REFERENCES `yv3nd_rokgallery_files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I got: #1005 - Can't create table 'test.yv3nd_rokgallery_file_loves' (errno: 150)

I'm pretty new to this so any help to fix this error would be appreciated.

Here is more code:

CREATE TABLE yv3nd_rokgallery_files(  
   id int(10) unsigned NOT NULL AUTO_INCREMENT,  
   filename varchar(255) NOT NULL,  
   guid char(36) NOT NULL,  
   md5 char(32) NOT NULL,  
   title varchar(200) NOT NULL,  
   description text,  
   license varchar(255) DEFAULT NULL,  
   xsize int(10) unsigned NOT NULL, 
   ysize int(10) unsigned NOT NULL,  
   filesize int(10) unsigned NOT NULL,  
   type char(20) NOT NULL, 
   published tinyint(1) NOT NULL DEFAULT '0',  
   created_at datetime NOT NULL,  
   updated_at datetime NOT NULL,  
   slug varchar(255) DEFAULT NULL, 
   PRIMARY KEY (id), UNIQUE KEY id (id), 
   UNIQUE KEY guid (guid), 
   UNIQUE KEY yv3nd_files_sluggable_idx (slug), 
   KEY yv3nd_rokgallery_files_published_idx (published), 
   KEY yv3nd_rokgallery_files_md5_idx (md5), 
   KEY yv3nd_rokgallery_files_guid_idx (guid)) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

What did you find when you searched for that error message? How did you adjust your query after you got more information about the error? — miken32, Oct 26 '14 at 23:56
If you familar with this problem please write step by step that i must to change in this quesry command to make it work. Thanks! — Dmitri345, Oct 27 '14 at 00:00
Did you create the table `yv3nd_rokgallery_files` before trying to create the table `yv3nd_rokgallery_file_loves` — Barmar, Oct 27 '14 at 00:17
OP, can you post the code for creating the other table? This is likely a foreign key constraint error. So insight into the other table might help. Also, next time you ask a question please take the time to format the code so it looks neat and is easily readable. It is a pain to read a paragraph of code that does not flow together. — AdamMc331, Oct 27 '14 at 00:18
A properly-created backup will contain a line like `SET @@FOREIGN_KEY_CHECKS=0;` near the beginning, so that foreign key dependencies can be ignored while restoring. Does your file have that? — Michael - sqlbot, Oct 27 '14 at 01:34

AdamMc331 · Accepted Answer · 2014-10-27T00:47:09.983

1

The errno 150 usually means that the foreign key constraint can't be created. Without seeing the rest of the file it is hard to say for sure, but I would guess that it is one of two things:

Your yv3nd_file_loves_file_id_files_id constraint may have been defined in another table as well. You can't create two constraints with the same name, that could potentially throw an error.

Another possibility is that you are creating a constraint on incorrect types. In this table your field_id is int(10). If in the yv3nd_rokgallery_files table your id variable is anything but that this will fail. For example: if in the other table it's int(9) this won't work.

If neither of these are correct, can you post the code for the yv3nd_rokgallery_files table?

EDIT

A third possibility is that the id field in the other table is not set as the primary key. This will also cause an error, because the foreign key of one table should match the primary key of another.

EDIT 2 Another possibility could be that you are creating the file_loves table before the files table. That would cause an error because you'd be creating a reference to a table that does not exist yet.

edited Oct 27 '14 at 00:47

answered Oct 27 '14 at 00:16

AdamMc331

16,492
10
71
133

Are you creating the files table BEFORE the file_loves table? Because I posted both tables into [SQLFiddle](http://sqlfiddle.com/#!2/26ded) and they worked fine. – AdamMc331 Oct 27 '14 at 00:45
Im IMPORTING *.sql file to EMPTY database. I created NEW EMPY DATABASE UTF8 encoding. AND THATS important is THAT CREATING CODE of yv3nd_rokgallery_files table in *.sql file goes ONLY AFTER code which GIVES THIS error! It is in ORDER like in my question example! 1)CREATE TABLE `yv3nd_rokgallery_file_loves` ( `file_id` int(10) unsigned NOT NULL DEFAULT '0', ....... after that goses some more code and: 2)DROP TABLE IF EXISTS yv3nd_rokgallery_files; CREATE TABLE yv3nd_rokgallery_files ( id int(10) unsigned NOT NULL AUTO_INCREMENT, filename varchar(255) – Dmitri345 Oct 27 '14 at 00:51
1

That's your problem. You need to create `yv3nd_rokgallery_files` FIRST. See my second edit, please. – AdamMc331 Oct 27 '14 at 00:52
This fyle *.sql was created by HOSTING robot backup program. I didnt change anything. It was set to backup hosting mysql database every 10 days. I had 5 copies all gives the same error ? Do you thing I need manually change code to make creating of yv3nd_rokgallery_files table 1 of all ? – Dmitri345 Oct 27 '14 at 00:55
It's worth a try, isn't it? The reason you're getting an error is because you're trying to create a reference to a table that has not been created yet. Please try to see why that would not work. The table does not exist, so the constraint can't be created. – AdamMc331 Oct 27 '14 at 00:56
1

+1 but FWIW if the referenced table doesn't exist, it gives `ERROR 1215 (HY000): Cannot add foreign key constraint`, not errno 150. I tested on MySQL 5.6.21. Anyway, it sounds like the hosting robot backup program is outputting the tables in the wrong order. – Bill Karwin Oct 27 '14 at 01:33
@Billkarwin Interesting. Using [SQLFiddle](http://sqlfiddle.com/#!2/26ded) I still get errno 150. – AdamMc331 Oct 27 '14 at 01:34
YES HOSTINg ROBOT AUPUT TABLES IN WRONG ORDER! THANK YOU FOR YOUR TIME! – Dmitri345 Oct 27 '14 at 01:42

score 0 · Answer 2 · edited May 23 '17 at 12:27

Errno 150 is what is reported if the FOREIGN KEY couldn't work.

The most common reason is that the data type of the foreign key column is different from the data type of the primary key column it references.

Your foreign key column is file_id int unsigned. The argument 10 for int(10) doesn't matter in this case. It's still an int unsigned.

Your primary key column is in table yv3nd_rokgallery_files, column id. You should double-check that that column is int unsigned. If it isn't, for example if it's a bigint or other size of int, or if it's not unsigned like file_id is, then the you'll get errno 150.

You must change one of the columns, either yv3nd_rokgallery_file_loves.file_id or yv3nd_rokgallery_files.id, so they use the same data type.

The errno 150 can also happen if the foreign key fails for other reasons. For example, table yv3nd_rokgallery_files must be an InnoDB table. Is it MyISAM?

Question:For example, table yv3nd_rokgallery_files must be an InnoDB table. Is it MyISAM? --->Answer:Im IMPORTING *.sql file to EMPTY database. I created NEW EMPY DATABASE UTF8 encoding. — Dmitri345, Oct 27 '14 at 00:37

Error 150 when trying to import .sql file to phpmyadmin

2 Answers2