I am having a problem synchronizing a table containing a unique key. The table is of the form:
[codebox]CREATE TABLE `tablename` (
`id` int(10) unsigned NOT NULL auto_increment,
`a` int(10) unsigned NOT NULL default '0',
`b` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_ab` (`a`,`b`),
KEY `tablename_FKIndex1` (`a`),
KEY `tablename_FKIndex2` (`b`),
CONSTRAINT `0_1242` FOREIGN KEY (`a`) REFERENCES `another_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_1243` FOREIGN KEY (`b`) REFERENCES `yet_another_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[/codebox]
Here is what happens:
In the source database there could have been a row (1,100,200) that was deleted, followed by a new row (2,100,200) subsequently added. The destination database would still contain (1,100,200). This is a behavior of the application that I can't adjust.
I want to copy the changes from the source database to the destination. It is as though SJA is doing all the inserts first followed by all the deletes. Because of the unique key constraint, the insert of (2,100,200) fails, but then it goes ahead and deletes (1,100,200), causing the databases to no longer match up. I have to synchronize twice to get the newly added row into the destination database.
The source and destination databases are within the same instance of MySQL, so they are the same version.
My xml file looks like this.
[codebox]
…
…
tablename
id in ('1','2')
[/codebox]
(Note: I am dynamically generating it based on the exact rows in both databases I want to sync up — that's why it has the WHERE clause.)
Aside from removing the unique key constraint, is there any way to make this work in one pass, and not have to synchronize twice? I tried disabling foreign key constraints in the xml file, but this doesn't have an effect. Is there a way to disable unique key constraints as well?
Pleas always tell which program version you are using!
This “Because of the unique key constraint, the insert of (2,100,200) fails”.
Doesn't this constarint exist on target as soruce as well?
If the row exists on source it should also be possible to INSERT on target if the table CREATE statement is the same.
If the is a (server-side) CONSTRAINT on the data then of course it is not possible – but it is not a SQLyog issue then!
What am I missing?
We recently changed the order of operation for DATASYNC from INSERT -> DELETE -> UPDATE to DELETE -> INSERT -> UPDATE. That can be confusing (rows will be deleted and next inserted), but in the end data should come there.
Was it possible that you attach a small complete test case with a dump from the source as well as the target? Only one table with a few rows of data are required. I think that would the best way to secure that we do understand the problem.
You can create a ticket if you do not want to expose your data here. It can also be fake data as long as the issue is reproducable.
With FK's you should set Foreign Keys Check = 0. I repeat that the UK CONSTRAINT should not be an issue. If the row can exist on the source it should also insert on the target without problems.
lsemel
Memberpeterlaursen
Participant
