Odd Effect Copying Table, 0 Values In Primary Key

[seantellis]

First off, I think that SQLyog is a great product, and I'd like to say thanks for offering the free edition.

I'd now like to share a very odd piece of behaviour I came across today, in case it saves anyone else tearing their hair out.

I had a fairly simple table with a numeric “id” column, as follows:

Code:

+———————-+————–+——+—–+—————-+—————-+
| Field                | Type         | Null | Key | Default        | Extra          |
+———————-+————–+——+—–+—————-+—————-+
| id                   | int(5)       |      | PRI | NULL           | auto_increment |
| …                  | …          |      | … | …            | …            |

This is populated with auto-incrementing record ids starting from 1, as you would expect. However, I needed to add a single, “special” record, so I assigned it ID 0. This seems OK – it is a legal value for the field, and works in every single use case encountered except one.

I needed to copy this table from the live server to my local working copy of the database, so I tried to use SQLyog's copy table feature. So, as usual, I right clicked on the table, selected “Copy Table to Different Host/DB”, and then entered the name of the local database and table. But I got this error:

Error No. 1062

Duplicate entry '1' for key 1

There are no duplicates in the original table. If I dump the whole database using mysqldump, and import into my local database, then everything works. If I change the ID for the special row from 0 to -1, and do a table copy, then all is well. I have made this change permanent, as the actual value of the ID is of little consequence – it just needs to be something that the auto-increment will never generate on its own.

This is what I think is happening. When copying a table, the “0” value in the first row is being interpreted as NULL, not 0. This means “assign the next auto-increment value”, which in a blank table will be 1. So the special row will erroneously be given ID 1 in the new table. The next row is copied, and this has the explicit ID 1. Oops! There's already an ID of 1 in this supposedly unique column, so this is a failure.

I think that this is standard mySQL behaviour, which can be suppressed using the “NO_AUTO_VALUE_ON_ZERO” server mode option (available in 4.1.1 onwards).

The manual states that “As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.”

It would be nice if the table copy in SQLyog also did the same thing.

[peterlaursen]

With MySQL 5 the sql_mode can be changed. You can do from SQL pane:

http://www.webyog.com/faq/index.php?action…hlight=sql_mode

it is not a 'server mode' from 4.1 is a connection specific mode – and changeable from 5.0

Quote:

the “0” value in the first row is being interpreted as NULL

I don't think this is the explanation. If it was it was a regular BUG with MySQL.

[Ritesh]

This is normal that when inserting 0, MySQL takes it as NULL and inserts the next valid value which is 1.

NO_AUTO_VALUE_ON_ZERO option will override this issue. If mysqldump is doping then its an official solution by MySQL.

We will implement this in v5.1 BETA 3.

[joejk2]

Dear All.

This is an amazing piece of software – thank you very much!

I am encountering the same 'Error No. 1062' – complaining about duplicate entries in my primary key. I have tried globally setting the sql_mode = 'NO_AUTO_VALUE_ON_ZERO' but the problem remains.

Any further clues would be greatly appreciated. Many thanks,

joe

[peterlaursen]

Quote:

I am encountering the same 'Error No. 1062' – complaining about duplicate entries in my primary key. I have tried globally setting the sql_mode = 'NO_AUTO_VALUE_ON_ZERO' but the problem remains.

Well if you set sql_mode = 'NO_AUTO_VALUE_ON_ZERO' an enter more rows with id=0 (if a PK) then of course you will have 'Error No. 1062' – duplicate entries in my primary key' for row no. 2.

@joejk2:

1) Are you using HTTP-tunnel? There was a bug in the tunnelling file of 5.02 that could create things like this!

2) So would you please try version 5.1 BETA6.

Also setting the global sql_mode = 'NO_AUTO_VALUE_ON_ZERO' will not have effect with SQLyog as SQLyog sets “sql_mode = '' ” for the connection! refer to: http://www.webyog.com/faq/28_72_en.html.

You will have to issue the set sql_mode form inside SQLyog. With HTTP-tunneling you will have to edit the tunnelling file to change the SQLmode (either 'hard-code' the sql-mode or use mysql_pconnect() method. On editing the tunnellng file: http://www.webyog.com/faq/21_103_en.html (with 'pconnect' MySQL 'remembers' the setting from statement to statement.)

If I did not fully understand then please describe your problem more in detail!

[joejk2]

Peter,

Many thanks for your reply. Please excuse my confusion in this matter – I am very new to all this.

1) I am not using HTTP tunnelling. Rather I am using mysql's standard port 3306. I have included my jobfile below.

2) In those tables that do not correctly synchronize there are no rows with identical rows. I have included the description of these tables below and the data from the first

Hope my answer is vaguely useful! Many thanks again.

joe

sja.log is:[u]

Error No. 1062^M

Duplicate entry 'fergal:messages' for key 1^M

Error No. 1062^M

Duplicate entry '1' for key 1^M

Error No. 1062^M

Duplicate entry '1' for key 1^M

JOBFILE

127.0.0.1

root

***** 3306

fergal

ip_address

root

***** 3306

fergal

TABLES THAT RETURN ERROR NO 1062

mysql> describe f_objectcache;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| keyname | varchar(255) | NO | PRI | | |

| value | mediumblob | YES | | NULL | |

| exptime | datetime | YES | MUL | NULL | |

+

---

+

---

+

---

+

---

+

---

+

---

+

3 rows in set (0.00 sec)

mysql> select keyname from f_objectcache;

+

---

+

| keyname |

+

---

+

| fergal:messages |

| fergal:pcache:idhash:1-0!1!0!0!!en!2 |

| fergal:pcache:idhash:1-0!3!0!1!0!en!2 |

| fergal:pcache:idhash:1306-0!1!0!0!!en!2 |

| fergal:pcache:idhash:1306-0!3!0!1!0!en!2 |

| fergal:pcache:idhash:1313-0!1!0!0!!en!2 |

| fergal:pcache:idhash:1327-0!1!0!0!!en!2 |

| fergal:pcache:idhash:1360-0!1!0!0!!en!2 |

+

---

+

mysql> describe f_searchindex;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| si_page | int(8) unsigned | NO | PRI | 0 | |

| si_title | varchar(255) | NO | MUL | | |

| si_text | mediumtext | NO | MUL | | |

+

---

+

---

+

---

+

---

+

---

+

---

+

3 rows in set (0.01 sec)

mysql> select si_page from f_searchindex;

+

---

+

| si_page |

+

---

+

| 1 |

| 4 |

| 989 |

| 1280 |

| 1281 |

| 1282 |

| 1283 |

| 1284 |

| 1285 |

| 1286 |

| 1287 ……

mysql> describe f_site_stats;

+

---

+

---

+

---

+

---

+

---

+

---

+

| Field | Type | Null | Key | Default | Extra |

+

---

+

---

+

---

+

---

+

---

+

---

+

| ss_row_id | int(8) unsigned | NO | PRI | 0 | |

| ss_total_views | bigint(20) unsigned | YES | | 0 | |

| ss_total_edits | bigint(20) unsigned | YES | | 0 | |

| ss_good_articles | bigint(20) unsigned | YES | | 0 | |

| ss_total_pages | bigint(20) | YES | | -1 | |

| ss_users | bigint(20) | YES | | -1 | |

| ss_admins | int(10) | YES | | -1 | |

+

---

+

---

+

---

+

---

+

---

+

---

+

7 rows in set (0.00 sec)

mysql> select ss_row_id from f_site_stats;

+

---

+

| ss_row_id |

+

---

+

| 1 |

+

---

+

[peterlaursen]

Quote:

In those tables that do not correctly synchronize there are no rows with identical rows

No but the MySQL server claims that there are identical Primary Keys. The error 1062 is a MySQL server error – not an internal SQLyog error. Also the Selects don't tell much as any insert of data with a dublicate key would fail. There are no dublicate keys in the select that you post here, but the Server would never allow for creation of dublicate primary keys!

To understand how SQLyog DATA SYNC uses the Primary Key, read http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

Is it possible that you could provide a 'test case'. 'create staements for the tables' (rather than 'describes')and two dumps (source and target data – not too many) to reproduce the issue

BTW: what SQLyog program verison are you using?

[joejk2]

Peter.

Many thanks again for your reply. Thank you for the link to the pdf which I tried my best to understand.

1) I can't find any duplicated keys throughout any of the tables. Indeed two of the tables (site_stats and searchindex) only have one row of data!

2) I am using sja501 (linux version)

3) I am trying to use sja to sync two live installations of MediaWiki – hence it is not trivial to provide the create statements for the tables.

I have attached dumps for the SOURCE and TARGET. Error No. 1062 is returned on attempting to sync tablesThis forum is not allowing me two upload so the files are at AngryFruit.co.uk:

• SOURCE_OBJECTCACHE
• SOURCE_SEARCHINDEX
• SOURCE_SITE_STATS

The sja.log is

Error No. 1062^M

Duplicate entry 'SOURCE:pcache:idhash:1-0!1!0!0!!en!2' for key 1^M

Error No. 1062^M

Duplicate entry '1' for key 1^M

Error No. 1062^M

Duplicate entry '1' for key 1^M

I'm sure I'm missing something simple and I apologise in advance for this! Many thanks for any help you can offer.

Joe

[peterlaursen]

Hi again ..

Your data sync'ed fine here between two MySQL 5.0.20 databases with SJA for Windows as of SQLyog 5.1 beta6 on this local Windows machine.

Code:

SQLyog Job Agent Version 5.1
Copyright (c) Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Sun Apr 09 22:23:42 2006

Table SrcRows TgtRows Inserted Updated Deleted
========================= ======= ======= ======== ======= =======
`source_archive` 0 0 0 0 0
`source_categorylinks` 0 0 0 0 0
`source_hitcounter` 0 0 0 0 0
`source_image` 0 0 0 0 0
`source_imagelinks` 0 0 0 0 0
`source_interwiki` 174 174 0 0 0
`source_ipblocks` 0 0 0 0 0
`source_logging` 0 0 0 0 0
`source_math` 0 0 0 0 0
`source_objectcache` 3 3 0 0 0
`source_oldimage` 0 0 0 0 0
`source_page` 0 0 0 0 0
`source_pagelinks` 0 0 0 0 0
`source_querycache` 0 0 0 0 0
`source_recentchanges` 2 2 0 0 0
`source_revision` 1283 1283 0 0 0
`source_searchindex` 1 1 0 0 0
`source_site_stats` 1 1 0 0 0
`source_text` 1283 1283 0 0 0
`source_trackbacks` 0 0 0 0 0
`source_transcache` 0 0 0 0 0
`source_user` 1 1 0 0 0
`source_user_groups` 2 2 0 0 0
`source_user_newtalk` 0 0 0 0 0
`source_validate` 0 0 0 0 0
`source_watchlist` 0 0 0 0 0

Total time taken – 11 sec(s)

So it could be an issue that has been fixed lately (there have been a lot of fixes). Porbably SJA for Linux will be recompiled too when SQLyog 5.1 is FINAL. Do you have a chance to install SQLyog on WINE? The SJA for windows will run with WINE. Refer to: http://www.webyog.com/faq/31_71_en.html .

I have to leave for an hour but will test later with SJA for Linux version 5.02 (that is now 4-5 months old – yours is even older). I will copy data to my SuSE10 that also runs 5.0.20. Check back in 1-2 hours!

I think that there are UTF-8 data in the file. That probably is where 'the chain breaks' with your SJA version

To start it the command is just

Code:

wine sja jobfile.xml

?

BTW: to attach data here you should ZIP then. We support a few graphics formats directly – else only ZIP! We shalll not risk any sort of 'code injection' or 'SQL injection' to our server<_<

[peterlaursen]

Iam sorry …

Seems that I sync'ed the same file to itself.

With the correct data for source and target i get these errror:

Code:

Sync started at Sun Apr 09 23:13:22 2006

Error No. 1062
Duplicate entry 'SOURCE:pcache:idhash:1-0!1!0!0!!en!2' for key 'keyname'

Error No. 1062
Duplicate entry '1' for key 'si_page'

Error No. 1062
Duplicate entry '1' for key 'ss_row_id'

Now I see what you mean. Three rows fails to sync. They are:

source_objectcache: 3rd row causes trouble //note latin1_bin collation (however collations should not matter!).

source_searchindex: 1st and only row causes trouble //note UNSIGNED integer.

source_site_stats: 1st and only row causes trouble //note UNSIGNED integers.

Yes there is an issue here! Those three rows should have been UPDATED. Instead SJA tries to INSERT, but the server prevents this as the PK's are identical. And that is what the error message is about.

I am in doubt whether your database(s) is/are corrupted or if it is an issue with SJA/SQLyog.

I experience that when SQLyog exports the data the export cannot import!

[joejk2]

Hmmmmm….

As far as I am aware my databases are not corrupted. MediaWiki seems to be content with them (the install is fully functional). Is there any reason why SJA would attempt an INSERT rather than an UPDATE?

joe

[peterlaursen]

Hmmm .. this is 'hairy' …

There is something crazy with your data. I can import your MYSQLDUMP data. I can export with SQLyog, but not import these files again as there are 'empty insert statements' in the file.

However if you empty or delete the three tables where sync fails at target, the sync runs OK. The data are created at the target. This seems permanently to solve the issue with the two last tables (I can edit and add data and sync)

But not the first table – the source_objectcache table. There is something very very weird with the source_objectcache.value column. I cannot update the 'exptime' value or delete a row in this table from GUI because of this! However an update staement like

Code:

update source_objectcache set exptime = '2006-04-09 19:12:40' where keyname = 'SOURCE:messages';

works fine. But not if the .value column is included in the where!

Looks like the SQLyog/SJA code has an issue with this MEDIUMBLOB column (it cannot be because of the binary collation!!!)! The SYNC tool does not identify them correctly and even a simpel WHERE clause in an update statement with these data fail.

But for the last two tables I simply think your target database (indexes) was corrupt. If I delete those tables at target and let the sync recreate them, there is no issue anymore no matter what I do at source. ('Repair Tables' is not an option with InnoDB tables)

Over to Bangalore 😀

@ritesh: BTW: why does not the WHERE simply use the PK as it did before? I think that change came with the 'defaults' thing of 5.1. But imagine a LONGBLOB of 4 GB in a WHERE …

Your data attatched as a ZIP package for convenience

[Ritesh]

Thanks for the sample data. I will take a look into it today.

[Ritesh]

This is not a BUG. This is not working because the table does NOT HAVE A PRIMARY KEY. It has a UNIQUE KEY SJA does not recognise. I will suggest that you change it to PRIMARY KEY and it will work. I just tried and it works 🙂

Starting from BETA 7, it will actually show a warning so that an USER does not get confused.

[peterlaursen]

@Ritesh – don't you forget that SJA-SYNC now is supposed to work without a PK?

I agree that Joeck shoul have a PK, but still this should not happen.

Also it is a bug, that you can't export and import again! The export file generated is invalid!

[joejk2]

Many thanks for all your help.

I will look at changing the keys next time I get a moment.

Thanks again.

joe

[Author]

Posts