Database Synchronization Wizard

[TGKnIght]

Hi, just a quick note to share an observation made this morning while performing a DB data sync.

When DB is sync'ed the program will go through all tables in alphabetical order and try to sync rows one table at a time.

This causes some tables to fail synchronizing if they have a foreign key that depends on a table further down the list.

For example, if we are currently at table “orders” which has a foreign key to the table “users”.. The sync in “orders” will potentially fail if the “users” records do not yet exist in the target database.

Perhaps before performing the data sync you could gather the meta-data for each table, find the tables that have foreign key references, and skip those until their parent tables have been filled in.

For the time being my solution is to just run the DB sync twice if I see any failures because of foreign keys.

[peterlaursen]

did you check the 'SET FOREIGN_KEYS_CHECK = 0″ box?

http://dev.mysql.com/doc/refman/5.0/en/set-option.html

Quote:

FOREIGN_KEY_CHECKS = {0 | 1}

If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. See Section 14.2.6.4, “FOREIGN KEY Constraints”.

Actually 'circular references' are possible and then there is NO TABLE ORDER 'that saves the day' !

The Foreign_keys_check option is for that.

[peterlaursen]

Also the 'mysqldump' program uses this.

From http://dev.mysql.com/doc/refman/5.0/en/inn…onstraints.html

Quote:

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded.

I always mismatch this 🙁

Correct is “FOREIGN_KEY_CHECKS”, “FOREIGN_KEYS_CHECK” is wrong!

[TGKnIght]

Ah oops… I had seen the option but did not know what it was for.. Will test it out in a little while.

Thanks!

[Author]

Posts