Database Synchronisation Wizard

[savery]

I have two mysql databases on local and remote servers. Local is running 4.21, Remote 4.14 of mysql

I am using the data sync wizard to keep remote database up to date with local database. Both have identical tables definitions. All tables have primary keys defined (unique etc…)

I use one way sync as I do not want local changed.

I run the job and get the following type of results.

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`zw_HEAT_CallLog` 1943 1943 0 0 33

Both local and remote start off with 1943 records. After sync remote has 33 less because they are deleted.

They do not match. Why are the rows being deleted when they exist in the local database?

There are no relationships defined between tables (fk s)

localhost

user1

pwd1 3306

[default]

db1

shuser1

shpwd1

ssh.myserver.com

22

3310

database.myserver.com

user2

pwd2 3306

[default]

db2

`zw_HEAT_CallLog`

[peterlaursen]

Quote:

All tables have primary keys defined (unique etc…)

A UNIQUE KEY and a PRIMARY KEY is NOT the same thing. Do you have a PRIMARY key?

You may paste the table definition here!

[savery]

/*Table structure for table `zw_HEAT_CallLog` */

drop table if exists `zw_HEAT_CallLog`;

CREATE TABLE `zw_HEAT_CallLog` (

`CallID` varchar(8) NOT NULL default '',

`CustID` varchar(50) default NULL,

`CustType` varchar(15) default NULL,

`CallType` varchar(100) default NULL,

`Tracker` varchar(96) default NULL,

`CallStatus` varchar(50) default NULL,

`Priority` char(2) default NULL,

`CDuration` decimal(9,4) default NULL,

`CallCount` decimal(5,2) default NULL,

`StopWatch` decimal(9,6) default NULL,

`ClosedBy` varchar(96) default NULL,

`ClosedDate` varchar(10) default NULL,

`ClosedTime` varchar(8) default NULL,

`Cause` varchar(50) default NULL,

`CallDesc` text,

`CloseDesc` text,

`RecvdBy` varchar(96) default NULL,

`RecvdDate` varchar(10) default NULL,

`RecvdTime` varchar(8) default NULL,

`ModBy` varchar(96) default NULL,

`ModDate` varchar(10) default NULL,

`ModTime` varchar(8) default NULL,

`DTLastMod` int(11) default NULL,

`CallSource` varchar(15) default NULL,

`PriorityName` varchar(50) default NULL,

`QuickCall` char(1) default NULL,

`UserCL1` varchar(35) default NULL,

`UserCL2` varchar(35) default NULL,

`UserCLD` varchar(10) default NULL,

`CSRating` varchar(15) default NULL,

`Category` varchar(25) default NULL,

`SLAUrgency` varchar(15) default NULL,

`IRStatus` varchar(10) default NULL,

`KIList` varchar(6) default NULL,

`DescSummary` varchar(100) default NULL,

`ActRaisedDate` varchar(10) default NULL,

`ActClosedDate` varchar(10) default NULL,

`TargetCloseDate` varchar(10) default NULL,

`IRNumber` varchar(10) default NULL,

`IRClosedDate` varchar(10) default NULL,

`CustClass` varchar(25) default NULL,

`Acknowledgedin24` varchar(10) default NULL,

`PlatformType` varchar(15) default NULL,

`InternalInfoOnly` char(1) default NULL,

`TrackerEmail` varchar(100) default NULL,

`CustomerPriorityIssue` char(1) default NULL,

`GroupName` varchar(75) default NULL,

`CustomerContact` varchar(35) default NULL,

PRIMARY KEY (`CallID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[peterlaursen]

ok .. ther was a PK ..

.. but what is the SQLyog version that you are using ?

Th PK is based on a varchar column (CallId).

There has been some issues with special characters in string-based PK's (like for instance ), but I believe everything is OK with recent versions.

However string data must use the same charset. For instance if one table use utf8 and another latin1 and there are characters outside the ASCII-range, situations like this could occur. Check for this.

If not we will need two samle tables (source + target) to reproduce with.

Also inform about the MySQL versions.

[savery]

I am using the 5.19 trial at the moment. My company has an enterprise licence and we downloaded the upgrade. I think the purchase must have been over one year ago as the registration key does not work.

A colleague is sorting this out. If necessary we will purchase a new licence for this project.

I think that the problem is the char set one (utf8/latin1). I am new to mysql and so appreciate your help with all this.

When I created the database on my local box I took the vanilla settings. I will go back and try to make the ini's match and be more specific in my database and table creation options.

If this does not work I will be send a new reply. Otherwise thanks for you help

[savery]

Peter,

It was a utf8/latin1 problem. All seems to replicate nicely now.

This can be considered closed.

Thanks again

[Author]

Posts