Bug #41904 create unique index problem

• Submitted: 6 Jan 2009 21:39
• Modified: 20 Jun 2010 17:42
• Reporter: Marco Mezzena Segundo
• Status: Closed
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S2 (Serious)
• Version: MySql 5.1.30 and InnoDB Plugin 1.0.2
• OS: Windows (Vista Business SP1)
• Assigned to: Marko Mäkelä
• CPU Architecture: Any
• Tags: innodp plugin unique index null

[6 Jan 2009 21:39] Marco Mezzena Segundo

Description:
MySql alert Duplicate entry "" on creation of unique index on column that accept null value. On traditional InnoDB version or MyIsam tables, the creation works fine. If the index was created before insert commands, works fine too.

How to repeat:
DROP TABLE IF EXISTS `test`

CREATE TABLE `test` ( 
`id` int(9) NOT NULL AUTO_INCREMENT, 
`uniquecol` char(15) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB 

insert into `test` 
(`id`,`uniquecol`) 
values (1,NULL) 

insert into `test` 
(`id`,`uniquecol`) 
values (2,NULL) 

create unique index ui on test (uniquecol) 

Suggested fix:
To work like previous versions.

[6 Jan 2009 22:56] MySQL Verification Team

Thank you for the bug report. I compiled source 5.1.30 and InnoDB source 1.0.2:

c:\dbs>5.1p\bin\mysqld --standalone --console
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
090106 20:49:02  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
090106 20:49:03  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
090106 20:49:03  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090106 20:49:04 InnoDB Plugin 1.0.2 started; log sequence number 0
090106 20:49:15 [Note] Event Scheduler: Loaded 0 events
090106 20:49:15 [Note] 5.1p\bin\mysqld: ready for connections.
Version: '5.1.30-nt'  socket: ''  port: 3306  Source distribution

c:\dbs>5.1p\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>
mysql> CREATE TABLE `test` (
    -> `id` int(9) NOT NULL AUTO_INCREMENT,
    -> `uniquecol` char(15) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> insert into `test`
    -> (`id`,`uniquecol`)
    -> values (1,NULL) ;
Query OK, 1 row affected (0.06 sec)

mysql>
mysql> insert into `test`
    -> (`id`,`uniquecol`)
    -> values (2,NULL) ;
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> create unique index ui on test (uniquecol) ;
ERROR 1062 (23000): Duplicate entry '' for key 'ui'
mysql>

Below the current source behavior without plugin:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.22 sec)

mysql 5.1 >
mysql 5.1 >CREATE TABLE `test` (
    -> `id` int(9) NOT NULL AUTO_INCREMENT,
    -> `uniquecol` char(15) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 >
mysql 5.1 >insert into `test`
    -> (`id`,`uniquecol`)
    -> values (1,NULL) ;
Query OK, 1 row affected (0.17 sec)

mysql 5.1 >
mysql 5.1 >insert into `test`
    -> (`id`,`uniquecol`)
    -> values (2,NULL) ;
Query OK, 1 row affected (0.03 sec)

mysql 5.1 >
mysql 5.1 >create unique index ui on test (uniquecol) ;
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.1 >

[7 Jan 2009 12:15] Marko Mäkelä

The bug is in row_merge_tuple_cmp(). It should check that none of the columns are NULL before calling row_merge_dup_report:

		/* Report a duplicate value error if the tuples are
		logically equal.  NULL columns are logically inequal,
		although they are equal in the sorting order.  Find
		out if any of the fields are NULL. */

		for (b = field; b != a; b++) {
			if (dfield_is_null(b)) {

				return(cmp);
			}
		}

[7 Jan 2009 14:18] Marko Mäkelä

The fix will be included in InnoDB Plugin 1.0.3, due later.

[18 Mar 2009 13:29] Marko Mäkelä

InnoDB Plugin 1.0.3 was released on March 11, 2009.

[5 May 2010 15:13] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:58] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 5:48] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:18] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:46] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[30 May 2010 0:11] Paul DuBois

Noted in 5.1.47, 5.5.5 changelogs.

InnoDB could fail to create a unique index on NULL columns.

[15 Jun 2010 8:14] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:30] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 11:49] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:27] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:14] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)