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

Bug #30907	Regression: "--innodb_autoinc_lock_mode=0" (off) not same as older releases
Submitted:	7 Sep 2007 19:18	Modified:	8 Oct 2007 18:22
Reporter:	Omer BarNir
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.1.22	OS:	Any
Assigned to:	Sunny Bains	Target Version:
Tags:	innodb_autoinc_lock_mode

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

test case

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

expected result file

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

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, acurtis@xiphis.org +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.

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, acurtis@xiphis.org +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.

Pushed into 5.1.23-beta

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

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.

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

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha