Duplicate Entry Error While Synchronizing

[peterlaursen]

this surely should not happen! If looks somewhat like a problem with old versions and special characters like or ' . But should not happen with SQLyog5.

this:

Quote:

If I delete the offending rows in the target it will happily synchronize.

looks like the key to the problem for me. Also synch'ing with LONGBLOBs is a little unusual. Could it be a concatenation/checksums problem with LONGBLOBs? How big are those LONGBLOBs ?

do you use the option some way?

If the TITLE field is unique you can use this field ONLY with the the option. Then only this column is used for checksums calculation. And it will speed up things a lot too. But this is a workaround only – not a bugfix … if there is a bug!

Could you paste in the jobfile?

Are the data confidential? Some sample data are needed to work with this problem.

[peterlaursen]

Now this rewritten article is out on Webyog website:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

It gives an example how you can really effectivize things using the option.

I have been thinking a little more about this. To be able to work further into this e need

* the Jobfile

* the 'create statement for the table'. Copy it from OBJECTS pane in SQLyog

* some sample data. All columns but not all rows.

To generate sample data for instance build a new table like

Code:

CREATE TABLE new_table SELECT * from old_table LIMIT 1000;

Now export new_table.

If you don't want to attach it here you can zip it all and mail it to [email protected].

It might be some bug with concatenation/checksums, so that rows that are identical don't get identical checksums. Then SQLyog tries to INSERT a row. However the MySQL Server does not accept an INSERT using a PK-value that is already in use.

Do you happen to know whether the problematic row(s) are changed on the source when this happens? It surprises me somewhat that it is an INSERT and not an UPDATE error that occurs. It looks like even the PK is not identified correctly.

But we need sample data and the jobfile to reproduce it. And nothing can be fixed that can't be reproduced.

[peterlaursen]

By the way

Quote:

I am using SQLyog Enterprise V5 with MySQL 4.1.1.

MySQL 4.1.1 is a VERY early alpha version (first in 4.1 series) and very buggy. You realy should upgrade the servers. Latest is 4.1.16 (to stay in 4.1.x)

[peterlaursen]

@brianvic

you write

Quote:

The entry is a unique primary key.

is it a PRIMARY KEY or a UNIQUE INDEX?

Please paste in the 'create statement for the table' EXACTLY as from the SQLyog OBJECTS pane. Don't edit it! Copy-paste!

[brianvic]

Peter

Sorry for the delay in responding. I can't include a subset of data as I don't have permission to do so, but here is the table creation statement for the table in question.

/*DDL Information For – sosio.STYLESDATA*/

---

Table Create Table

---

---

STYLESDATA CREATE TABLE `STYLESDATA` (

`STYLE` varchar(10) NOT NULL default '',

`DDATA` longblob NOT NULL,

`TITLE` varchar(250) NOT NULL default '',

`UPDATED` date NOT NULL default '0000-00-00',

`PUBLIC` tinyint(4) NOT NULL default '0',

`COMMENT` varchar(255) default NULL,

UNIQUE KEY `STYLE` (`STYLE`),

KEY `UPDATED` (`UPDATED`),

KEY `PUBLIC` (`PUBLIC`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

Hope this is helpful. I will see if I can get permission to supply you with even a couple of records for this if that would help.

[brianvic]

Sorry, also meant to say that the version of MySQL I am using is 4.1.10. I must have left off the zero in my initial post. This is the version of MySQL as supplied with SuSE 9.3

[peterlaursen]

Thanks – that clarified a lot!

There is no Primary Key!! It says (only) UNIQUE KEY `STYLE` (`STYLE`).

If you change it to be PRIMARYthen your problem is over! PRIMARY KEYS are defined from ALTER TABLE, not MANAGE INDEXES.

Now read this article:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

and find the paragraph 'Sync without a Primary Key' (it is near the end).

You will understand then that you are trying to create two records from one. However the server prevents this since there can only be one row with the same UNIQUE KEY.

BTW.. it is very easy to update MySQL on SuSE! Also to MySQL 5.x. I am running SuSE 10 myself, and I have updated several times (it came with 4.1.14 – now I am running 5.0.17). Just click the RPM from KDE or GNOME and the YAST installer does the rest! Dump your data first! But 4.1.10 is not bad I think.

[Author]

Posts