MySQL Bugs: #53290: wrong duplicate key error when adding a unique index via fast alter table

Bug #53290	wrong duplicate key error when adding a unique index via fast alter table
Submitted:	29 Apr 2010 15:31	Modified:	11 Jan 2011 0:49
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.46, plugin 1.0.7	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any
Tags:	Contribution, duplicate key, fast alter table

Description:
when using fast alter table, adding a unique key on multiple columns where one of the columns is null can lead to duplicate key errors where none would occur if the unique key was already created at table creation:

ERROR 1062 (23000): Duplicate entry '7777-0' for key 'c'

How to repeat:
import attached file.

if you change to old_alter_table=1 it works.
if you put the unique index in the create table, it works.

testcase. import using innodb plugin and see duplicate key error

Attachment: bug53290.sql.gz (application/x-gzip, text), 189.00 KiB.

I wonder if this can be related to bug #47622.

reproduced with mysql-trunk-innodb. I doubt it has to do with the question
of NULLs. Here's a test case where the INSERTs are obtained by
grep 7777 original_test_case

drop table if exists `t1`;
create table `t1` (
  `a` bigint(20) not null auto_increment,
  `c` bigint(20) default null,
  `b` bigint(20) default null,
  primary key (`a`)
) engine=innodb;

insert into `t1` values (7777,7777,7247);
insert into `t1` values (8370,8370,7777);
insert into `t1` values (17777,17777,16407);
insert into `t1` values (19212,19212,17777);
insert into `t1` values (27777,27777,25912);
insert into `t1` values (29694,29694,27777);
insert into `t1` values (31862,7777,null);
insert into `t1` values (37777,37776,35831);
insert into `t1` values (37778,37777,35832);
insert into `t1` values (39748,39747,37777);
insert into `t1` values (41017,7777,null);
set session old_alter_table=0;
commit;
alter table `t1` add key (b);
alter table `t1` add unique key (`c`,`b`);

now: 1. The key is created even though we have both 7777-null rows here.
2. any FIC index creation report '0 rows affected' unlike slow ALTER
which counts the rows. Not sure if it's not a feature.

It looks like the fast index creation is done through filesort.  The bug only occurs with a sufficiently large dataset.  Reducing the size of the test case causes the bug to go away.   My guess is the bug only occurs when the two rows are in separate merge blocks.

Yeah, I think the issue is with this code in row0merge.c row_merge_blocks():

                switch (row_merge_cmp(mrec0, mrec1,
                                      offsets0, offsets1, index)) {
                case 0:
                        if (UNIV_UNLIKELY
                            (dict_index_is_unique(index))) {
                                innobase_rec_to_mysql(table, mrec0,
                                                      index, offsets0);
                                mem_heap_free(heap);
                                return(DB_DUPLICATE_KEY);

I think it needs an additional check that the values are non-NULL.

Possible fix. Makes my test case pass.

Attachment: bug53290.patch (application/octet-stream, text), 3.18 KiB.

Short MTR repro case.

Attachment: innodb_bug53290.test (application/octet-stream, text), 713 bytes.

Yes, as shown in following comments in row_ins_scan_sec_index_for_duplicate(), NULL are defined not to equal to each other: 

        /* If the secondary index is unique, but one of the fields in the
        n_unique first fields is NULL, a unique key violation cannot occur,
        since we define NULL != NULL in this case */

        for (i = 0; i < n_unique; i++) {
                if (UNIV_SQL_NULL == dfield_get_len(
                            dtuple_get_nth_field(entry, i))) {

                        return(DB_SUCCESS);
                }
        }

We will need to apply the same rule in the new merge sort.

The patch and the test case look OK to me, with two minor points.

* We use TRUE, FALSE for ibool literals instead of 0, 1.
* Remove the *null_eq = 0 assignment from cmp_rec_rec_simple(). Let the caller initialize the variable when it is needed.

Jimmy, can you revise the patch as above, and also include the supplied mysql-test-run case? I will then approve your patch.

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/107945

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/107946

Pushed into 5.1.48 (revid:vasil.dimov@oracle.com-20100531082307-9x08gg1g7zybx2jy) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)

Pushed into 5.5.5-m3 (revid:alexey.kopytov@sun.com-20100615145247-8bj0vmuqlotbqsn9) (version source revid:marko.makela@oracle.com-20100511105308-grp2t3prh3tqivw0) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615150216-cubqoyn1fj9b6a2p) (version source revid:marko.makela@oracle.com-20100511105308-grp2t3prh3tqivw0) (pib:16)

Text for the change log:

      Adding a unique key on multiple columns, where one of the
      columns is null, could mistakenly report duplicate key errors.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)