Error 1062 For Table Synching To An Empty Table

[mkinnan]

I have been using SQLyog for a month now for my Drupal installations and have noticed a problem. This may be an easy fix or a SQLyog checkbox that needs to be ‘checked'. SQLyog has problems with synchronizing and data import for the ‘users’ table in the database. I tried searching the forums, but I really don’t know what this problem is called other than the error message.

When a fresh copy of Drupal is installed, you end up with two rows in the ‘users’ table. The first is a system user and has a PRIMARY KEY identifier as 0 (the UID column). The second row is account #1 and is the administrative user and has a PRIMARY KEY of 1. The problem is that when I either synchronize (to an empty table) or do an entire database backup/import I received the error: Error No. 1062 – Duplicate entry '1' for key 1. Attached is a picture of the database rows before and after synching, encase my explanation is not very good.

I don't have this problem when I use phpMyAdmin to do database export/import. I tried comparing export/import settings between SQLyog and phpMyAdmin but nothing seems to help me with fixing the problem in SQLyog. Can anyone provide me some feedback? I am using version 8.1.

Thanks.

Below is the table creation info:

Code:

create table
CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL auto_increment,
** … removed a bunch of table creation here … **

PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

[peterlaursen]

1) I first wrote this

Please tell the program version! And also if you are using HTTP tunnel.

There were some fixes with 'duplicate key' error earlier. But I understand you also get this error during import. Do you use SQLyog for both export and import?

I will ask you to provide a test case. Can you do like this

Code:

CREATE newtable AS SELECT * from oldtable WHERE uid=0 or uid=1

.. and next create exactly the same indexes on 'newtable' as on 'oldtable' .. if not the 'duplicate error' comes again.

Next export/dump 'newtable' .. drop 'newtable'. Can you now import it?

2) but next I noticed

UNIQUE KEY `name` (`name`),

.. so it could be an issue with that key and not the Primary Key. What does the content of 'name' look like for column 0 and column 1? And is 'name' column declared NULL or NOT NULL?

[mkinnan]

peterlaursen wrote on Jun 8 2009, 09:48 AM:

1) I first wrote this

Please tell the program version! And also if you are using HTTP tunnel.

I stated in my post I was using version 8.1. Is there another version number you need? I connect to the databases directly using MySQL (no SSH or HTTP).

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Do you use SQLyog for both export and import?

I use SQLyog for both export and import.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Code:

CREATE newtable AS SELECT * from oldtable WHERE uid=0 or uid=1

.. and next create exactly the same indexes on 'newtable' as on 'oldtable' .. if not the 'duplicate error' comes again.

I created another table and manually added the PRIMARY and UNIQUE indexes without trouble.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Next export/dump 'newtable' .. drop 'newtable'. Can you now import it?

Everything worked smoothly. I even was able to Sync (schema sync followed by data sync) 'newtable' to another table without a problem. However, if I try schema sync followed by data sync of the 'oldtable' to another table I receive an error.

I did retest an entire database export/import and cannot reproduce the error (it has only happened a couple times over a month). But I always get the error with every sync job.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

.. so it could be an issue with that key and not the Primary Key. What does the content of 'name' look like for column 0 and column 1? And is 'name' column declared NULL or NOT NULL?

Name is declared as NOT NULL. The name for 0 is empty and the name for 1 is 'admin' (the attached picture in my previous post shows most of the values of the tables before and after sync). I attached the full 'table create' schema below.

You are thinking that since 'uid = 0' has an empty 'name' value, SQLyog gives an error because the 'name' column is set as NOT NULL and it expects there to be a value?

Code:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

[mkinnan]

mkinnan wrote on Jun 8 2009, 10:36 AM:

Everything worked smoothly. I even was able to Sync (schema sync followed by data sync) 'newtable' to another table without a problem. However, if I try schema sync followed by data sync of the 'oldtable' to another table I receive an error.

I just realized that since this works even though 'uid = 0' has an empty 'name' value, then SQLyog doesn't have a problem with this value being NULL even though the column is defined as NOT NULL. Could the problem lie with the auto incrementation for the 'uid' column?

[peterlaursen]

SQLyog does not expect anything like that. It only reads data. I am only trying to find some entry to the problem! If table is NOT NULL and name = it is likely becuase global sql_mode is *strict* and session sql_mode for the application is '' (empty mode). But also SQLyog sets an empty sql_mode. And still it should not matter.

If you are telling that problems occur with a fresh installation of this application (where the 2 first rows are created) there must be some difference between the table created by the application and the table you just created.

Quote:

I did retest an entire database export/import and cannot reproduce the error (it has only happened a couple times over a month). But I always get the error with every sync job.

I understand with both sync to an empty and an non-empty table?

No clue at the moment. But please also tell

1) the server versions (both source and target – probably unimportant though).

2) is the database used by the application while syncing? Data sync does not take a 'snapshot' of each table, but 'chews its way' through it from one end (and actually I also think backup/export does). Also this we should have handled but that is just an idea right at the moment. The 'duplicate' indicates that an INSERT-attempt is performed that should have been an UPDATE attempt. Can you afford to try stop the application from connecting the database while syncing?

With 'dump' you have LOCK and FLUSH LOGS options. With Data Sync there is not (but we have discussed adding it).

[mkinnan]

peterlaursen wrote on Jun 8 2009, 11:04 AM:

I understand with both sync to an empty and an non-empty table?

The problem only seems to occur on an empty table.

peterlaursen wrote on Jun 8 2009, 11:04 AM:

1) the server versions (both source and target – probably unimportant though).

CentOS 5.3 (x86_64), Apache (2.2.8 (EL)), PHP (5.2.6), MySQL (5.0.58)

peterlaursen wrote on Jun 8 2009, 11:04 AM:

2) is the database used by the application while syncing? Data sync does not take a 'snapshot' of each table, but 'chews its way' through it from one end (and actually I also think backup/export does). Also this we should have handled but that is just an idea right at the moment. The 'duplicate' indicates that an INSERT-attempt is performed that should have been an UPDATE attempt. Can you afford to try stop the application from connecting the database while syncing?

The database is not being used. All tests were performed on a fresh install of the database (by Drupal installation) so no users are accessing the website and no part of Drupal is accessing the database.

This is the table I created manually:

Code:

CREATE TABLE `newtable1` (
`uid` int(10) unsigned NOT NULL default '0',
`name` varchar(60) character set utf8 NOT NULL default '',
`pass` varchar(32) character set utf8 NOT NULL default '',
`mail` varchar(64) character set utf8 default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) character set utf8 NOT NULL default '',
`signature` varchar(255) character set utf8 NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) character set utf8 default NULL,
`language` varchar(12) character set utf8 NOT NULL default '',
`picture` varchar(255) character set utf8 NOT NULL default '',
`init` varchar(64) character set utf8 default '',
`data` longtext character set utf8,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This is the table Drupal creates:

Code:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

[peterlaursen]

“happens with an empty table only”. Actually when syncing to an empty table we use basically the same copy as when exporting.

But I also noticed different charsets. Actually we do not prevent syncing tables with different charsets. But not sure either that this is an issue. But it could be if there are utf8-characters in source that cannot be handled in latin1. Or maybe two utf8 characters that are mapped to the same byte-value in different single-byte encodings. I tried

Why do you create tables with different charsets? Also (with reference to you other post here today): Do you use 'export as ..' or 'backup as sql-dump' ?? Only the latter will create engine and charset parameters as they wer originally defined in source.

edit: I removed a mistakeof mine here. But please ensure that the .sql file has a utf8 charset specification like the table you export from. 'backup as sql-dump' will write this – 'export as ..SQL' will not.

[mkinnan]

The tables created by Drupal use CHARSET=utf8. When I created the table manually using the SQL query you posted, it apparently used the CHARSET=latin1. I didn't select a specific charset.

Code:

CREATE TABLE newtable AS SELECT * from oldtable WHERE uid=0 or uid=1

The exporting/backing up I have done is the 'Backup Database as SQL Dump' [Ctrl + Alt + E]

[peterlaursen]

I reported this to MySQL: http://bugs.mysql.com/bug.php?id=45470

There is no way to sync the Drupal user table with current server behaviour. Basically I find it quite silly what the Drual installer does!

But let se see the reply from MySQL!

[peterlaursen]

can't you just make the 'uid' column SIGNED instead of UNSIGNED. Will it break the application?

[peterlaursen]

To sync (and also to copy, import etc.) an autoincrement field containing a '0' value we need to set 'NO_AUTO_VALUE_ON_ZERO' sql_mode. If we don't the server will make it '1' and if there is one more '1' we have the 'duplicate key' error. That is what happens.

We are working on this now.

[navyashree.r]

Issue added in our track list,

http://code.google.com/p/sqlyog/issues/detail?id=1106

Regards,

Navya

[navyashree.r]

Hi,

This issue is fixed and available in 8.13 beta1, publicly available!

Please refer to:

http://www.webyog.com/blog/2009/06/19/sqly…-been-released/

Please let us know the status.

Thank you.

Regards,

Navya

[Author]

Posts