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

[8 Sep 2007 21:48] Omer Barnir
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
[9 Sep 2007 3:25] 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/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.
[12 Sep 2007 17:08] Paul Dubois
Noted in 5.1.22 changelog.

Report remains in Patch Pending state.
[12 Sep 2007 17:16] Paul Dubois
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.
[5 Oct 2007 17:57] Bugs System
Pushed into 5.1.23-beta
[8 Oct 2007 16:24] 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 Paul.
[30 Jan 2009 13:28] Bugs System
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)
[30 Jan 2009 15:08] Bugs System
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)
[17 Feb 2009 14:55] Bugs System
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)
[17 Feb 2009 16:43] Bugs System
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)
[17 Feb 2009 18:19] Bugs System
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)
[5 May 2010 15:22] Bugs System
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)
[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:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:38] Bugs System
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)
[28 May 2010 7:06] Bugs System
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)
[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:15] Bugs System
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)
[17 Jun 2010 13:02] Bugs System
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)
[17 Jun 2010 13:43] Bugs System
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)