Bug #30907 Regression: "--innodb_autoinc_lock_mode=0" (off) not same as older releases

• Submitted: 7 Sep 2007 17:18
• Modified: 18 Jun 2010 23:10
• Reporter: Omer Barnir (OCA)
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.1.22
• OS: Any
• Assigned to: Sunny Bains
• CPU Architecture: Any
• Tags: innodb_autoinc_lock_mode

[7 Sep 2007 17:18] Omer Barnir

Description:
In 5.1.22 we introduced a new innodb auto_inc mechanism and a flag --innodb_autoinc_lock_mode to control it. Setting the value to zero is supposed to revert to the old behavior.

However setting the flag does something 'different' as inserts fail on duplicate key error when done against partitioned tables.
as demonstrated by the following:

CREATE TABLE test.regular_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT,
                           dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
                           fkid MEDIUMINT, filler VARCHAR(255),
                           PRIMARY KEY(id)) ENGINE=innodb
                                PARTITION BY RANGE(id)
                                SUBPARTITION BY hash(id) subpartitions 2
                                (PARTITION pa1 values less than (10),
                                 PARTITION pa2 values less than (20),
                                 PARTITION pa3 values less than (30),
                                 PARTITION pa4 values less than (40),
                                 PARTITION pa5 values less than (50),
                                 PARTITION pa6 values less than (60),
                                 PARTITION pa7 values less than (70),
                                 PARTITION pa8 values less than (80),
                                 PARTITION pa9 values less than (90),
                                 PARTITION pa10 values less than (100),
                                 PARTITION pa11 values less than MAXVALUE);

delimiter |;
CREATE PROCEDURE test.proc_norm()
BEGIN
   DECLARE ins_count INT DEFAULT 1000;
   DECLARE del_count INT;
   DECLARE cur_user VARCHAR(255);
   DECLARE local_uuid VARCHAR(255);
   DECLARE local_time TIMESTAMP;

   SET local_time= NOW();
   SET cur_user= CURRENT_USER();
   SET local_uuid= UUID();

   WHILE ins_count > 0 DO
     INSERT INTO test.regular_tbl VALUES (NULL, NOW(), USER() , UUID(),
                                   ins_count,'Going to test MBR for MySQL');
     SET ins_count = ins_count - 1;
   END WHILE;

END|

delimiter ;|

CALL test.proc_norm();
select count(*) as "Row Count:" from test.regular_tbl;

Running the above against 5.1.21 results in: 
Row Count
1000

Running it with --innodb_autoinc_lock_mode=0 against 5.1.22 results in:
query 'CALL test.proc_norm()' failed: 1022: Can't write; duplicate key in table 'regular_tbl'

Note: the problem is not observed with myisam engine

How to repeat:
Use the attached test case and mysql-test-run.pl 

First run:
perl ./mysql-test-run.pl ins_part_tbl.test
against 5.1.21 binaries
>> The test will pass - the count will be returned

Then run:
perl ./mysql-test-run.pl --mysqld="--innodb_autoinc_lock_mode=0" ins_part_tbl.test
against 5.1.22
>> The error will show

Suggested fix:
Fix the duplicate key bug and have --mysqld="--innodb_autoinc_lock_mode=0" be identical to previous releases

[7 Sep 2007 17:19] Omer Barnir

test case

Attachment: ins_part_tbl.test (application/octet-stream, text), 2.38 KiB.

[7 Sep 2007 17:19] Omer Barnir

expected result file

Attachment: ins_part_tbl.result (application/octet-stream, text), 1.28 KiB.

[8 Sep 2007 18:15] 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/33969

ChangeSet@1.2599, 2007-09-08 11:14:23-07:00, [e-mail] +6 -0
  Bug#30907
    "Regression: "--innodb_autoinc_lock_mode=0" (off) not same as older releases"
  Bug#28430
    "Failure in replication of innodb partitioned tables on row/mixed format"
  Bug#30888
    "Innodb table + stored procedure + row deletion = server crash"
  
    Apply Oracle patch from Sunny
    Include tests cases by Omer
    Ensure that innobase_read_and_init_auto performs table autoinc lock when lock_mode = 0
    No need for "if" guard around row_unlock_table_autoinc_for_mysql() because
    it already performs same check.
    Make autoinc_lock_mode variable read-only for duration of running mysqld process.

[8 Sep 2007 18:20] 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/33970

ChangeSet@1.2599, 2007-09-08 11:19:35-07:00, [e-mail] +8 -0
  Bug#30907
    "Regression: "--innodb_autoinc_lock_mode=0" (off) not same as older releases"
  Bug#28430
    "Failure in replication of innodb partitioned tables on row/mixed format"
  Bug#30888
    "Innodb table + stored procedure + row deletion = server crash"
  
    Apply Oracle patch from Sunny
    Include tests cases by Omer
    Ensure that innobase_read_and_init_auto performs table autoinc lock when lock_mode = 0
    No need for "if" guard around row_unlock_table_autoinc_for_mysql() because
    it already performs same check.
    Make autoinc_lock_mode variable read-only for duration of running mysqld process.

[5 Oct 2007 17:57] Bugs System

Pushed into 5.1.23-beta

[5 Oct 2007 18:10] Calvin Sun

the fix is already in 5.1.22 release. no need to doc.

[8 Oct 2007 16:22] Jon Stephens

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Closed without further action per comment from Calvin.

[7 Nov 2007 1:02] Timothy Smith

Slightly different patch included in InnoDB snapshot 5.1-ss1989, and queued to 5.1-build.

[21 Nov 2007 18:53] Bugs System

Pushed into 5.1.23-rc

[21 Nov 2007 18:53] Bugs System

Pushed into 6.0.4-alpha

[5 May 2010 15:06] 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 3:01] Paul DuBois

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

[28 May 2010 6:00] 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:29] 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:56] 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)

[29 May 2010 15:24] Paul DuBois

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

[17 Jun 2010 12:03] 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:45] 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:30] 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)