Importing a database error
Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed It seems to be a foreing key error. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing a database error
(response interspersed) Boris Villazon [EMAIL PROTECTED] wrote on 10/14/2005 08:30:13 AM: Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), This index duplicates your primary key and is not needed KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), Your primary key already indexes this column. This index is also not needed. CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Is `read_restring` also an InnoDB table? Is `read_restring`.`id` also indexed? ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed Is that really what SHOW INNODB STATUS said or is that the output of perror? Whenever working with Innodb, if you get an error always check SHOW INNODB STATUS for more complete details. It seems to be a foreing key error. It is but exactly what was wrong with the foreign key you didn't say. You get that from SHOW INNODB STATUS. At this point in your script, does `read_restring` even exist? If it doesn't, you may need to reorganize your script. If `attribute_instance` has data already in it and at least one row has an `id` value that isn't on `read_restring`, that would also cause an error 150. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón Check my comments above and get back to us with the error description from SHOW INNODB STATUS immediately after re-creating the error if you are still having problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Importing a database error
[EMAIL PROTECTED] wrote: (response interspersed) Boris Villazon [EMAIL PROTECTED] wrote on 10/14/2005 08:30:13 AM: Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), This index duplicates your primary key and is not needed Oki, I will check ... thanks KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), Your primary key already indexes this column. This index is also not needed. Oki, I will check also. CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Is `read_restring` also an InnoDB table? Is `read_restring`.`id` also indexed? Yes, read_restring is also an InnoDB table and `read_restring`.`id` is also indexed. ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed Is that really what SHOW INNODB STATUS said or is that the output of perror? Whenever working with Innodb, if you get an error always check SHOW INNODB STATUS for more complete details. Show InnoDB status didn't give useful information in this case. It seems to be a foreing key error. It is but exactly what was wrong with the foreign key you didn't say. You get that from SHOW INNODB STATUS. At this point in your script, does `read_restring` even exist? If it doesn't, you may need to reorganize your script. If `attribute_instance` has data already in it and at least one row has an `id` value that isn't on `read_restring`, that would also cause an error 150. Thanks for your tip. I did reorganize my script and everything works fine. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón Check my comments above and get back to us with the error description from SHOW INNODB STATUS immediately after re-creating the error if you are still having problems. Thanks and regards Shawn Green Database Administrator Unimin Corporation - Spruce Pine
