Re: duplicate entry error, but key doesn't exist - Jonathon Wardman - com.mysql.lists.cluster

5 messages in com.mysql.lists.clusterRe: duplicate entry error, but key do...

From	Sent On	Attachments
Paul Maunders	12 Sep 2006 04:08
Hartmut Holzgraefe	12 Sep 2006 04:36
Paul Maunders	12 Sep 2006 05:08
Hartmut Holzgraefe	12 Sep 2006 05:17
Jonathon Wardman	12 Sep 2006 05:27

Subject:	Re: duplicate entry error, but key doesn't exist
From:	Jonathon Wardman (jona...@fubra.com)
Date:	09/12/2006 05:27:32 AM
List:	com.mysql.lists.cluster

Paul Maunders wrote:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Thanks, you are right, a different key was causing the problem. There was another unique index in the table on a field called legacy_username.

It seems that in our old non-cluster database (MySQL 4.1.18) there were two records with the following legacy_usernames

"cpc" "cpc "

MySQL 4.1 was happy to treat these as unique usernames, but when inserting in to the MySQL 5.1 NDB table, it triggers a Duplicate entry error.

Is this expected behaviour? Are space characters at the end of a word somehow ignored in a unique key in MySQL 5.1 ?

You can replicate this with the following test data

CREATE TABLE `clustertest` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=ndbcluster

Then run

INSERT INTO `clustertest` SET `username` = 'bob';

Followed by

INSERT INTO `clustertest` SET `username` = 'bob ';

The second statement fails with

#1062 - Duplicate entry '8' for key 'PRIMARY'

Paul

Hartmut Holzgraefe wrote:

Paul Maunders wrote:

I have been running a script to insert more rows into this table, and it just stopped with a duplicate entry error.

ERROR 1062 (23000): Duplicate entry '1151544' for key 'PRIMARY'

However, if I do a search for a record with that id, nothing is returned.

mysql> select * from user_petrolprices WHERE user_id=1151544; Empty set (0.00 sec)

The actual problem is in the way the error message is generated here which simply reports the wrong key and value. You should check the failing INSERT for other values that violate any of the additional UNIQUE keys on that table.

The cluster api can only report a unique key violation back to the mysql server but not the actual id of the key that got violated.

The output routine for this error does expect a key number though so it will always report a violation of the tables first key here, even though the real problem is in one of the other unique keys on that table.

This is a known bug (http://bugs.mysql.com/21072) that we originally tried to solve by using a different error code which lead to problems of its own though ...

- --

Paul Maunders

Operations Director Fubra Limited

web | www.fubra.com email | pa...@fubra.com voip | sip://20...@asterisk.fubra.it direct | +44 (0)1252 367 202 phone | +44 (0)1252 367 222 fax | +44 (0)1252 367 229

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Fubra Ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Fubra Ltd accepts no liability for any damage caused by any virus transmitted by this email. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFBqM/sYtjtAM5Dp4RAqTbAKCESgRamVYHvvS1BNWpaV9TLNoVAwCcCNjo iFJRTSp2k3Tc7x5OZG2aIHw= =irtq -----END PGP SIGNATURE-----

This behaviour is specified in the manual - http://dev.mysql.com/doc/refman/5.1/en/blob.html. The same was specified in the 4.1 manual but was not actually implemented (see http://bugs.mysql.com/bug.php?id=21335).