Synchronisation/foreign key problem
Posted: Tue 09 Jul 2013 13:10
We are using the trial version of dbForge for MySQL.
We're trying to synchronize 2 MySQL databases using data comparison and synchronisation.
We run into the following problem :
Data comparison runs well, but the synchronisation script generates several SQL-statements to drop the foreign keys on several tables - the foreign keys are then restored after the sql synchronisation statements have been executed. Dropping foreign keys however takes a very long time on our database and the database file exploded when we ran the script : from 65 GB to over 110 GB before we stopped the script and restored a backup copy of our db.
The synchronisation script also contains a (commented-out) statement to temporarily disable foreign key checking while executing the synchronisation statements. So we removed the drop foreign key statements and removed the comments on this statement. This worked well and is a good solution.
However, we have to edit the script to get this behaviour. When however the update script grows (the db contains thousands of pictures so the update script can be quite large), we cannot open the script (out of memory error) to edit it before it is executed. It also cannot be saved to file - this gives the same error.
--> 1. Is there a way to get a script with no foreign key drop statements and with the foreign key check comments removed that it can be executed directly against the database.
--> 2. Why does the operation to save the script to file, generate an out of memory error?
Greetings
Luc
We're trying to synchronize 2 MySQL databases using data comparison and synchronisation.
We run into the following problem :
Data comparison runs well, but the synchronisation script generates several SQL-statements to drop the foreign keys on several tables - the foreign keys are then restored after the sql synchronisation statements have been executed. Dropping foreign keys however takes a very long time on our database and the database file exploded when we ran the script : from 65 GB to over 110 GB before we stopped the script and restored a backup copy of our db.
The synchronisation script also contains a (commented-out) statement to temporarily disable foreign key checking while executing the synchronisation statements. So we removed the drop foreign key statements and removed the comments on this statement. This worked well and is a good solution.
However, we have to edit the script to get this behaviour. When however the update script grows (the db contains thousands of pictures so the update script can be quite large), we cannot open the script (out of memory error) to edit it before it is executed. It also cannot be saved to file - this gives the same error.
--> 1. Is there a way to get a script with no foreign key drop statements and with the foreign key check comments removed that it can be executed directly against the database.
--> 2. Why does the operation to save the script to file, generate an out of memory error?
Greetings
Luc