Copy Database To Different Host/db & Foreign Keys Havoc!

[peterlaursen]

PROGRAM VERSION PLEASE !!! Did you try latest available version? .. how often will we have to ask those questions ???

What does 'fails' mean? a SPECIFIC description please, EXACT error message ?!?!

The solution for such problems with Foreign Keys ALWAYS is to “SET FOREIGN_KEY_CHECKS = 0”. This should be done by the program. If it does not it is a bug! … So again: PROGRAM VERSION PLEASE !!! Did you try latest available version?

[Splash!]

Program version: SQLyog Community Edition 6.04 (running on Win2000)

However, I am doing it again in order to reproduce the error message for you, and it isnt doing it this time. I will play around and post again if I can figure out what went wrong. If not, I guess its resolved. 😕

Maybe it had something to do with the fact that the first time I tried it, I copied just the structure over, and then did it again with the data as well. Seems unlikely this would hurt it, but it sure isnt failing this time.

Thanks anyway, and I apologise for posting without the program version. 🙄 I really should know better. 🙂

[peterlaursen]

It is true of course that copying (or restoring from a backup or sync'ing) a populated 'child' table would fail because of the foreign key CONSTRAINT if not the 'parent' table was populated in advance. That is why the SET FOREIGN_KEY_CHECKS command was invented.

Alternatively you would have to populate the tables in a certain order, and the server has no way to generate that order itself. It would involve a lot of application code to find out that order (and in the (rare) situation where references are circular it would simply not be possible at all).

I cannot explain what happened this time when you had the error. Looks like the server 'forgot' or 'ignored' the SET FOREIGN_KEY_CHECKS command.

And yes .. If you can reproduce it then please report details!

[Author]

Posts