mysqldump & Innodb constraints

[pollux0]

I have always used mysqldump to backup my databases..which has always worked.

Recently I started using Innodb tables, in particular, foreign key restraints. The problem is with restoring the database. I create a backup of my database successfully like this:

mysqldump myDatabase > backup.sql -p

Then i try to restore the database:

mysql < backup.sql -p

But I get errors because of the foreign key restraints. It turns out that when mysql made the backup file it didn't check which tables should be created first. The child table is created before the parent table. So in order to fix this I have to edit the backup file manually and move the parent table script above the child table script. Is there a way to make sql recognise the constraints?

 

[QatQat]

Read one of my old posting


thread436-518134


Bye


Qatqat

Life is what happens when you are making other plans.

 

[pollux0]

thats crazy!!!! Why would mysql leave such a fundamental problem unfixed!!!! What if two tables had circular dependencies??? Wouldnt the restore fail no matter which table i put first???

 

[QatQat]

I have not found any other solution. You have to modify the table order and, if you have circular dependancies, you first create the tables and all straight dependancies, then run a couple of alter table statement.

If someone has a better solution I am interested too.


Bye

Qatqat

Life is what happens when you are making other plans.