Bug #52430 Incorrect key in the error message for duplicate key error involving BINARY type
Submitted: 29 Mar 2010 11:01 Modified: 7 Jul 2010 19:02
Reporter: Nirbhay Choubey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5.3-m3 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: 5.1.45, 5.5.3-m3, regression

[29 Mar 2010 11:01] Nirbhay Choubey
Description:
Server throws incorrect duplicate-key in the error message for duplicate key error
involving multicolumn key of 'BINARY' type. (for mysql-5.5.x)

CREATE TABLE t1(c1 BINARY(10) NOT NULL, c2 BINARY(10) NOT NULL, c3 BINARY(10) NOT NULL, PRIMARY KEY(c1,c2,c3));
INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');

INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
ERROR 1062 (23000): Duplicate entry 'abc\x00\x00\x00\x00\x00\x00\x00-abc\x00\x00\x00\x00\x00\x00\x00-' for key 'PRIMARY'

The above error message do not seem to represent correct duplicate key value.

While if the data-type is changed to VARBINARY, the error message looks fine :

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

Similarly, if the above set of statements (for BINARY data-type ) is tried on 5.1.45, the error message is : 

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

Which again looks incorrect to me.

How to repeat:
CREATE TABLE t1(c1 BINARY(10) NOT NULL, c2 BINARY(10) NOT NULL, c3 BINARY(10) NOT NULL, PRIMARY KEY(c1,c2,c3));
INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
[29 Mar 2010 12:07] MySQL Verification Team
Thank you for the bug report.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

C:\DBS>50

C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table t1;
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 > CREATE TABLE t1(c1 BINARY(10) NOT NULL, c2 BINARY(10) NOT NULL, c3 BINARY(10) NOT NULL,
    -> PRIMARY KEY(c1,c2,c3));
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 > INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
ERROR 1062 (23000): Duplicate entry 'abc' for key 1
mysql 5.0 > exit
Bye

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.46-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >CREATE TABLE t1(c1 BINARY(10) NOT NULL, c2 BINARY(10) NOT NULL, c3 BINARY(10) NOT NULL,
    -> PRIMARY KEY(c1,c2,c3));
Query OK, 0 rows affected (0.07 sec)

mysql 5.1 >INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
ERROR 1062 (23000): Duplicate entry 'abc' for key 'PRIMARY'
mysql 5.1 >exit
Bye

C:\DBS>55

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.99-m4-Win X64 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >CREATE TABLE t1(c1 BINARY(10) NOT NULL, c2 BINARY(10) NOT NULL, c3 BINARY(10) NOT NULL,
    -> PRIMARY KEY(c1,c2,c3));
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >INSERT INTO t1 (c1,c2,c3) VALUES('abc','abc','abc');
ERROR 1062 (23000): Duplicate entry 'abc\x00\x00\x00\x00\x00\x00\x00-abc\x00\x00\x00\x00\x00\x00\x00-' for key 'PRIMARY'
mysql 5.5 >
[27 May 2010 12:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109360
[27 May 2010 13:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109377
[27 May 2010 14:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109379
[27 May 2010 14:20] Alexander Barkov
http://lists.mysql.com/commits/109379 is Ok to push
[27 May 2010 16:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109402
[15 Jun 2010 8:14] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (merge vers: 5.5.5-m3) (pib:16)
[15 Jun 2010 8:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:alik@sun.com-20100615080431-gu5icn0anrt47dsx) (pib:16)
[7 Jul 2010 19:02] Paul Dubois
Noted in 5.5.5 changelog.

Spurious duplicate-key errors occurred for multiple-column indexes on
columns with the BINARY data type.