I am using both versions 6.56 and the 7.0 RC on windows XP. I do a "backup database as Sql Dump". I examine the output and it is correct. It has the USE dbname statement in it, as well as the non-bulk INSERT statements I requested. There are no triggers in the database.
When I try to restore the database using the dump file, I have selected the database to restore to in SQLyog (although I know it should not be necessary since the dump has the USE statement in it). It chugs along for a short while and reports an error. It was trying to execute a statement to "DROP TRIGGER triggername". As I mentioned, there are no triggers in the DB. Furthermore, I look down in the error file and see that some of the next INSERT statements refer to data that does not exist in this database.
At first glance, it would appear I have choosen the wrong dump to restore. So I check the filename, location ,etc of the dump many times and try the restore many times. All with the same results. It appears that even though I have selected one dump file, it is using another.
I am including a copy of the dump file and the resulting error file, although I am not sure they can be much help.
Regards,
Mike
Full Version: Restore From Dump Bug
"It appears that even though I have selected one dump file, it is using another."
Please tell:
1) " it is using another" the (complete) dump (not only the USE statement) is a backup of another database than what you thought it should be? correct?
2) "though I have selected" how did you select? by clicking in the Object Broser or using the dropdwon?
3) did you use the database context-menu in the Objects browser or the menu in the menu line for starting the dump process? Or is it 'scheduled backup' ?
Please tell:
1) " it is using another" the (complete) dump (not only the USE statement) is a backup of another database than what you thought it should be? correct?
2) "though I have selected" how did you select? by clicking in the Object Broser or using the dropdwon?
3) did you use the database context-menu in the Objects browser or the menu in the menu line for starting the dump process? Or is it 'scheduled backup' ?
I have tried to reproduce that wrong database was exported. I cannot. Nertheless I think I have an idea ..
What I imagine could be the problem is that the export idalogue has 'memory' of the filename. If you have exported db1 once as 'db1.sql', next you open the dialogue the proposed filename is still 'db1.sql' . It will overwrite (but prompt you to confirm the overwrite though!)
Do you think it is better that we as defaault 'fill out' the file name wiht the name of the database?
Or is the problem another?
What I imagine could be the problem is that the export idalogue has 'memory' of the filename. If you have exported db1 once as 'db1.sql', next you open the dialogue the proposed filename is still 'db1.sql' . It will overwrite (but prompt you to confirm the overwrite though!)
Do you think it is better that we as defaault 'fill out' the file name wiht the name of the database?
Or is the problem another?
QUOTE (peterlaursen @ Jun 26 2008, 10:02 AM) 
"It appears that even though I have selected one dump file, it is using another."
Please tell:
1) " it is using another" the (complete) dump (not only the USE statement) is a backup of another database than what you thought it should be? correct?
I would say YES but I only see the part of the dump beginning with the error. When the error occurs it is obvious that the statement (DELETING a Trigger) does not even exist in the dump I was expecting to use. Also, I can see data further in the error file that does not belong to the proper dump.
2) "though I have selected" how did you select? by clicking in the Object Broser or using the dropdwon?
Both ways.
3) did you use the database context-menu in the Objects browser or the menu in the menu line for starting the dump process? Or is it 'scheduled backup' ?
Both ways (not scheduled backup)
Please tell:
1) " it is using another" the (complete) dump (not only the USE statement) is a backup of another database than what you thought it should be? correct?
I would say YES but I only see the part of the dump beginning with the error. When the error occurs it is obvious that the statement (DELETING a Trigger) does not even exist in the dump I was expecting to use. Also, I can see data further in the error file that does not belong to the proper dump.
2) "though I have selected" how did you select? by clicking in the Object Broser or using the dropdwon?
Both ways.
3) did you use the database context-menu in the Objects browser or the menu in the menu line for starting the dump process? Or is it 'scheduled backup' ?
Both ways (not scheduled backup)
QUOTE (peterlaursen @ Jun 26 2008, 10:49 AM)
I have tried to reproduce that wrong database was exported. I cannot. Nertheless I think I have an idea ..
What I imagine could be the problem is that the export idalogue has 'memory' of the filename. If you have exported db1 once as 'db1.sql', next you open the dialogue the proposed filename is still 'db1.sql' . It will overwrite (but prompt you to confirm the overwrite though!)
Do you think it is better that we as defaault 'fill out' the file name wiht the name of the database?
Or is the problem another?
What I imagine could be the problem is that the export idalogue has 'memory' of the filename. If you have exported db1 once as 'db1.sql', next you open the dialogue the proposed filename is still 'db1.sql' . It will overwrite (but prompt you to confirm the overwrite though!)
Do you think it is better that we as defaault 'fill out' the file name wiht the name of the database?
Or is the problem another?
I do not believe the wrong database was exported, since I can open the dump and verify that it is the correct one. However, whenever I attempt to restore the dump I get something else. I am not even sure where it is coming from.
One thing about these databases is they all start with the same name (e.g. metalsmith_target, metalsmith_1, etc.) However, only the metalsmith target has been dumped and attempted to be restored.
I am curious, did you try to restore the dump that I uploaded? And was it successful?
IMHO, yes it would be nice to have it default to the name of the database.
QUOTE
Query:
CREATE TABLE `buyout_product` (
`buyout_id` int(11) unsigned NOT NULL auto_increment,
`category_id` int(11) unsigned default NULL,
`description` varchar(50) NOT NULL,
`weight` decimal(11,3) default NULL,
`price` decimal(11,2) default NULL,
`create_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`country_of_origin` varchar(50) default NULL,
`alloy` varchar(50) default NULL,
PRIMARY KEY (`buyout_id`),
UNIQUE KEY `new_index9` (`description`),
KEY `FK_buyout_category` (`category_id`),
CONSTRAINT `FK_buyout_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11129 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 59392 kB; (`category_id`) REFER `metalsmith/cat'
Error occured at:2008-06-30 11:19:23
Line no.:502
Error Code: 1005 - Can't create table '.\metalsmith_target\buyout_product.frm' (errno: 150)
CREATE TABLE `buyout_product` (
`buyout_id` int(11) unsigned NOT NULL auto_increment,
`category_id` int(11) unsigned default NULL,
`description` varchar(50) NOT NULL,
`weight` decimal(11,3) default NULL,
`price` decimal(11,2) default NULL,
`create_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`country_of_origin` varchar(50) default NULL,
`alloy` varchar(50) default NULL,
PRIMARY KEY (`buyout_id`),
UNIQUE KEY `new_index9` (`description`),
KEY `FK_buyout_category` (`category_id`),
CONSTRAINT `FK_buyout_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11129 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 59392 kB; (`category_id`) REFER `metalsmith/cat'
Error occured at:2008-06-30 11:19:23
Line no.:502
Error Code: 1005 - Can't create table '.\metalsmith_target\buyout_product.frm' (errno: 150)
I got this error while restoring the dump attached above.
This error is obvious cause `Category` table does not exist while restoring the dump.
Also, I din get any error related to trigger , i can see from the SQLyog.err file attached by you that trigger related error was logged in year 2007.
Please empty the sqlyog.err file and try again the restore from dump and check what error message you are getting?
QUOTE (Mahesh @ Jun 29 2008, 11:32 PM)
I got this error while restoring the dump attached above.
This error is obvious cause `Category` table does not exist while restoring the dump.
Also, I din get any error related to trigger , i can see from the SQLyog.err file attached by you that trigger related error was logged in year 2007.
Please empty the sqlyog.err file and try again the restore from dump and check what error message you are getting?
This error is obvious cause `Category` table does not exist while restoring the dump.
Also, I din get any error related to trigger , i can see from the SQLyog.err file attached by you that trigger related error was logged in year 2007.
Please empty the sqlyog.err file and try again the restore from dump and check what error message you are getting?
Sorry to take so long to get back to you. But thanks for pointing out that the error I was reading was the oldest one at the beginning of the file instead of the end. I re-ran the restore and got the same error as you. That is,
CODE
Query:
/*Data for the table `buyout_change_item` */
/*Table structure for table `buyout_product` */
DROP TABLE IF EXISTS `buyout_product`
Error occured at:2008-07-05 11:01:04
Line no.:486
Error Code: 1217 - Cannot delete or update a parent row: a foreign key constraint fails
/*Data for the table `buyout_change_item` */
/*Table structure for table `buyout_product` */
DROP TABLE IF EXISTS `buyout_product`
Error occured at:2008-07-05 11:01:04
Line no.:486
Error Code: 1217 - Cannot delete or update a parent row: a foreign key constraint fails
I see that the problem is that the restore did not take into account foreign key restrictions. This was my problem with my understanding. Originally, when specifying the dump, I did not set the "Set FOREIGN_KEY_CHECKS=0" to on. (Really didn't think about it). So I recreated the dump with the "Set FOREIGN_KEY_CHECKS=0" on and then tried restoring. There were no errors.
So thanks for persevering and helping me understand what was going on!
Thanks for your feedback.
With recent program versions (you may check changelogs to find exactly when) "Set FOREIGN_KEY_CHECKS=0" is default for exports and data sync - because sometimes users 'did not think about it' -- so we changed that!
With recent program versions (you may check changelogs to find exactly when) "Set FOREIGN_KEY_CHECKS=0" is default for exports and data sync - because sometimes users 'did not think about it' -- so we changed that!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.