Rows That Are Inserted Are Deleted In The Same Synch

[phunkk]

I just updated a remote database with data from my local db. For a certain table 16 rows were inserted, but they were deleted at the same time (in the same synch process). I didnt make a printscreen, but maybe someone is farmiliar with this problem? If you need more info, just ask 🙂

Tnx in advance!

[peterlaursen]

Yes .. we know some possible reasons for this!

Rows are inserted because a PK-value exists on source but not on target.

But they get deleted again on target if checksums for a particular row differ. That can occur:

1) With PK's based on a TIMESTAMP, DATETIME or similar

It is basically a MySQL 'documentation bug'

refer to: http://webyog.com/faq/35_104_en.html

It should work with most recent SQLyog versions now.

2) With PK's build on strings that have certains special characters in them (baskslash, apostrophe, backtick). Those were SQLyog bugs fixed quite a long time ago (most of them way back in 4.07 actually). There has also been an issue with non-ascii characters and HTTP-tunnelling. Don't remember exactly when that was!

3) When tables is having different character sets (and typically when there are strings containing non-ascii-characters).

This is not a bug! For instance accented latin characters like ä. é. ø, ñ are encoded using one byte in latin1 and 2 bytes in utf8. If one of your tables use each their charset like this the md5(concat_ws(your_data)) will return different values on the hosts. Charsets must (in principle – there may be a few special characters that sync OK across latin1/latin2/latin5 for instance) be identical in tables that are sync'ed.

4) There are one or more issues with UCS2 charset too ..

http://webyog.com/faq/35_112_en.html

Please answer:

1) What is the SQLyog version that you use?

2) If the above does not explain and a SQLyog upgrade does not fix this then please post the table schema with a few rows of data to reproduce.

3) Also inform about the MySQL server version(s) and the connection method! In particular if you are HTTP-tunnelling or not?

[Author]

Posts