PDA

View Full Version : Restore failed with innoDB tables



herve
April 20th, 2009, 06:47 AM
Hi,
When, I want to restore a database with tables Innodb and constrants, I have this following error:
ERROR 1217 (23000) at line 1158: Cannot delete or update a parent row: a foreign key constraint fails

I think that Zmanda restore tables alphabetically and try to verify the constraint immediately and consequenctly I have this error.
Is there an option witch deferred the constraints while the restore?
thanks for your help.
Regards.

paddy
April 20th, 2009, 03:14 PM
Which version of ZRM are you using?

Are you restoring from logical backups?

Please provide contents of mysql-zrm.conf

thanks,
Paddy

herve
April 21st, 2009, 05:59 AM
- My version is MySQL-zrm-2.1-1

- I use a logical backup

The parameters of the file mysql-zrm.conf are :
backup-level=0
backup-mode=logical
destination=/var/lib/mysql-zrm
retention-policy=3D
compress=1
all-databases=1
user="wikiuser"
password="xxxxxx"
host="host-distant.mysoc.fr"
verbose=1
mailto="root@localhost"
remote-mysql-binpath="/u01/app/lampp/bin"

kulkarni_mangesh
April 21st, 2009, 08:38 PM
Hi,
When, I want to restore a database with tables Innodb and constrants, I have this following error:
ERROR 1217 (23000) at line 1158: Cannot delete or update a parent row: a foreign key constraint fails


What kind of backup you have performed, table OR Database based backup..?

What kind of restore you are performing, table OR Database based ..?

Are you performing restore to localhost and database is same as backed up database...?

Have you dropped the database before restore...?

Can you please provide us command you have used to perform a restore...?


I think that Zmanda restore tables alphabetically and try to verify the constraint immediately and consequenctly I have this error.
Is there an option witch deferred the constraints while the restore?
thanks for your help.
Regards.

Zmanda uses "mysqldump" to perform a backup and uses "mysql" command to restore. So if there is an issue it is likely that, issue will be in these two commands.

paddy
April 22nd, 2009, 03:43 PM
I'm surprised that you are seeing this problem.

Which version of MySQL are you using?

mysqldump (logical backup) should have disabled foreign key check when it did backup. You can search for "SET foreign_key_checks = 0;"

You can also disable the foreign key check before you do restoration as a workaround.

Paddy