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
[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
Re: Re: Simple query help
El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query help
Hi I have a little problem with my sql skills. I have a table with the following fields: id (int) | value (varchar) | date (date) I need to show for a given id the value of the oldest date. Normally, I'd do something like this: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication process?
Hi First at all ... sorry for my poor english. I have the following process: I have a Primary PC that acquires data from an external device. This data is stored in a local database. This external device is sending information all the time. There is a Secondary PC that copies the data from the Primary PC database. There is another database in the secondary pc. I have to do this with longer time intervals. I want to: The secondary pc copies only the new incoming data from primary pc database. When the secondary pc copies the data, the primary pc has to delete all these copied records (in the local database). This process has to be repeated continusly. Could I use replication technique to achieve this process? If yes, How can I make this with replication technique? Or Is there another posibility to handle this process (I mean automatic posibility)? Thanks in advance and best regards Boris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cloning a Database
Hi I need to know if is there anyway to clone a existing database (structure and data)? If yes, how can I do this? I looking for a SQL command. Thanks in advance and best regards Boris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
