Duplicate Entry Error While Synchronizing

[brianvic]

I am using SQLyog Enterprise V5 with MySQL 4.1.1. I am connecting directly to two databases which are running on Linux servers under SuSE Linux Pro 9.3. Both servers have identical sofware and setup.

Synchronization is one way only with delete in the traget table.

Both table schemas are identical. There are 19 tables of varying sizes, the largest of which is 49Mb with 1154 rows (the table stores RTF documents).

All of the tables synchronize perfectly, even with the tables where there are 80,000+ rows.

It is the 49Mb table that has the problem. I get the following error in the log and the synchronization stops even if I have not set it to:

Duplicate entry 'SOS5502' for key 1 – (the 'SOS5502' in itself is not important)

The entry is a unique primary key.

The schema is as follows (using the MyISAM engine):

Field Type Null Key

STYLE varchar(10) PRI

DDATA longblob

TITLE varchar(250)

UPDATED date MUL

PUBLIC tinyint(4) MUL

COMMENT varchar(255) YES (NULL)

STYLE is a primary, unique key

UPDATED and PUBLIC are not unique

When I try to synchornize I get the above error on some of the rows. If I delete the offending rows in the target it will happily synchronize.

The next time I try to sync I will get the same error, but the offending rows will be different. There appears to be no rhyme nor reason to which STYLE causes the problem.

I get the error even if I just select to update the DDATA field.

It would appear to suggest that it is trying to create a duplicate entry, but why should this be? The entry already exists so surely it should just be updated? Or am I missing something silly?

Any suggestions would be greatly appreciated. The program is a God send already, but if I could get the synchronization to work it would be pure manna from heaven!!

Thanks all.

Brian

[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