MySQL Bugs: #30919: Rows not deleted from innodb partitioned tables if --innodb_autoinc_lock

Bug #30919	Rows not deleted from innodb partitioned tables if --innodb_autoinc_lock_mode=0
Submitted:	8 Sep 2007 21:48	Modified:	18 Jun 2010 23:11
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.22	OS:	Any
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	innodb_autoinc_lock_mode

Description:
If running the server in --innodb_autoinc_lock_mode=0 mode the server fails to delete rows from a partitioned table if the rows are not part of the first partition

How to repeat:
1) Start the server with --innodb_autoinc_lock_mode=0
2) Create the following test case:
--source include/have_innodb.inc
--vertical_results
let $engine_type= 'innodb';

###### CLEAN UP SECTION ##############
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;

######## Creat Table Section #########
use test;

eval CREATE TABLE test.part_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT,
                           dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
                           fkid MEDIUMINT, filler VARCHAR(255),
                           PRIMARY KEY(id)) ENGINE=$engine_type
                                PARTITION BY RANGE(id)
                                SUBPARTITION BY hash(id) subpartitions 2
                                (PARTITION pa3 values less than (42),
                                 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);

######## Create SPs, Functions, Views and Triggers Section ##############

delimiter |;

CREATE PROCEDURE test.proc_part()
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.part_tbl VALUES (NULL, NOW(), USER() , UUID(),
                                   ins_count,'Going to test MBR for MySQL');
     SET ins_count = ins_count - 1;
   END WHILE;
   SELECT MAX(id) FROM test.part_tbl INTO del_count;
   WHILE del_count > 0 DO
     DELETE FROM test.part_tbl WHERE id = del_count;
     select count(*) as internal_count, del_count  -- these two lines are for
     FROM test.part_tbl;                           -- debug to show the problem
     SET del_count = del_count - 2;
   END WHILE;
END|

delimiter ;|

############ Finish Setup Section ###################

############ Test Section ###################
--horizontal_results

CALL test.proc_part();

select count(*) as Part from test.part_tbl;

DROP PROCEDURE test.proc_part;
DROP TABLE test.part_tbl;

3) save the above as innodb_del_part.test
4) Run the following command:
  ./perl ./mysql-test-run.pl --mysqld="--innodb" 
          --mysqld="--innodb_autoinc_lock_mode=0" innodb_del_part.test

Note: when running the set better to pipe the above to a file since 
      the output is long

From the output you will see that the lines are deleted from the tables only for values of id<42 (i.e. in the first partition), before that, none of the even lines are deleted although they should be (you can change the value of the first partition to see it change).

Note: The problem is NOT observed if
 --innodb_autoinc_lock_mode=1,2
 the table is not innodb
 the table does not have a partition
 running against 5.1.21 (and below)
 

Suggested fix:
Rows should be deleted regardless of the partition they are in

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

ChangeSet@1.2601, 2007-09-08 20:26:12-07:00, antony@pcg5ppc.xiphis.org +6 -0
  Bug#30919
    "Rows not deleted from innodb partitioned tables if --innodb_autoinc_lock_mode=0"
  
    Due to a previous bugfix which initializes a previously uninitialized
    variable, ha_partition::get_auto_increment() may fail to operate
    correctly when the storage engine reports that it is only reserving
    one value and one or more partitions have a different 'next-value'.
    Currently, only affects Innodb's new-style auto-increment code which
    reserves larger blocks of values and has less inter-thread contention.

Noted in 5.1.22 changelog.

Report remains in Patch Pending state.

Actually ... This bug report needs no changelog entry. The problem here is fixed in 5.1.22, the same version in which the new InnoDB auto-inc locking appeared. The bug does not appear in any released version.

Pushed into 5.1.23-beta

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 Paul.

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:sp1r-msvensson@pilot.mysql.com-20080325174049-18586) (merge vers: 5.1.24-rc) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

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

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

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

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)