MySQL Bugs: #32247: Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table)

Bug #32247	Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table)
Submitted:	9 Nov 2007 20:30	Modified:	14 Dec 2007 16:52
Reporter:	Joerg Bruehe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.23 (pre) + 6.0.4-alpha (pre)	OS:	Linux (PPC)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
I get this test failure in current 5.1 and 6.0 trees.
It passed in a test run of 6.0 on Oct 30,
but fails in both 5.1 and 6.0 since Nov 7.

The machine is a local Linux box:  Debian lenny/sid on a PPC (gcc 4.1.2)

rpl.rpl_innodb_bug28430        [ fail ]

--- /MySQL/M51/work-5.1/mysql-test/suite/rpl/r/rpl_innodb_bug28430.result       2007-11-02 00:27:08.000000000 +0300
+++ /MySQL/M51/work-5.1/mysql-test/suite/rpl/r/rpl_innodb_bug28430.reject       2007-11-09 03:08:49.448252527 +0300
@@ -113,7 +113,7 @@
   `fkid` mediumint(9) DEFAULT NULL,
   `filler` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION pa2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION pa3 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION pa4 VALUES LESS THAN (40) ENGINE = InnoDB, PARTITION pa5 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION pa6 VALUES LESS THAN (60) ENGINE = InnoDB, PARTITION pa7 VALUES LESS THAN (70) ENGINE = InnoDB, PARTITION pa8 VALUES LESS THAN (80) ENGINE = InnoDB, PARTITION pa9 VALUES LESS THAN (90) ENGINE = InnoDB, PARTITION pa10 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+) ENGINE=InnoDB AUTO_INCREMENT=2289 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION pa2 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION pa3 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION pa4 VALUES LESS THAN (40) ENGINE = InnoDB, PARTITION pa5 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION pa6 VALUES LESS THAN (60) ENGINE = InnoDB, PARTITION pa7 VALUES LESS THAN (70) ENGINE = InnoDB, PARTITION pa8 VALUES LESS THAN (80) ENGINE = InnoDB, PARTITION pa9 VALUES LESS THAN (90) ENGINE = InnoDB, PARTITION pa10 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
 show slave status;
 Slave_IO_State Waiting for master to send event
 Master_Host    127.0.0.1

After seeing this for the first time (= yesterday), I had a mail exchange about this with Omer who experimented a bit.
Part of his results might be helpful:

| I did a little more checking, with tables with no data 5 rows and different
| partitions.
| - Before inserting any values to tables the  AUTO_INCREMENT= is not
|   included in the SHOW command output.
| - After rows are inserted it is included and holds the next value of the
|   AUTO_INCREMENT, so if I insert 5 rows, it will say AUTO_INCREMENT=6
| - When I added a show command for the other tables in the test case it
|   returned with AUTO_INCREMENT=1001 which is expected (since the test
|   case inserts 1000 rows.
|
| So why does the 'byrange_tbl' has a '9'?
| Because for tables partitioned by range there seems to be a bug where instead
| of looking at the whole table, it looks only in the *first* partition. The
| test case inserts only 9 rows into the first partition. When I change the 
| nuber of rows in the first partition, the '9' changes accordingly.
|
| Note there seems to be a secondary bug that if the number of rows in the first
| partition is more then 10 the value is off by one (so for 5 rows it will be 6 
| but for 12 it will be 12.
|
| So this is clearly a partitioning bug.
|
| Any way, I can't answer why on your machine is is suddenly 2289, but I am 
| happy it was. Maybe in addition to all of the above there is an 
| initialization issue somewhere.

Note that the value "9" is in the result file since its very beginning,
Omer considers this value to be wrong since it takes only the first partition into account.

How to repeat:
Run the test suite.

To clarify, the correct value that should be listed in the show command is 1001 as the check is done after 1000 rows are inserted into the table.

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

ChangeSet@1.2645, 2007-11-13 23:01:43+04:00, holyfoot@mysql.com +4 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.

This patch would work if all engines used this variable in a consistent manner.
However at least archive uses it somewhat differently.
So I suggest that you fix the archive engines use of the auto_increment_value
variable to be correct and retain the patch as is.

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

ChangeSet@1.2645, 2007-11-23 15:19:51+04:00, holyfoot@mysql.com +7 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.

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

ChangeSet@1.2645, 2007-11-23 18:26:46+04:00, holyfoot@mysql.com +6 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.
  HA_ARCHIVE:stats.auto_increment handling made consistent with other engines

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

ChangeSet@1.2645, 2007-11-30 17:58:14+04:00, holyfoot@mysql.com +8 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.
  HA_ARCHIVE:stats.auto_increment handling made consistent with other engines

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

ChangeSet@1.2645, 2007-11-30 19:38:27+04:00, holyfoot@mysql.com +8 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.
  HA_ARCHIVE:stats.auto_increment handling made consistent with other engines

Mikael approved this, OK to push.

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

ChangeSet@1.2645, 2007-12-06 16:39:42+04:00, holyfoot@mysql.com +6 -0
  Bug #32247 Test reports wrong value of "AUTO_INCREMENT" (on a partitioned InnoDB table).
  
  ha_partition::update_create_info() just calls update_create_info
  of a first partition, so only get the autoincrement maximum
  of the first partition, so SHOW CREATE TABLE can show
  small AUTO_INCREMENT parameters.
  Fixed by implementing ha_partition::update_create_info() in a way
  other handlers work.
  HA_ARCHIVE:stats.auto_increment handling made consistent with other engines

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in the 5.1.23 and 6.0.5 changelogs as follows:

      
        SHOW CREATE TABLE misreported the value of
        AUTO_INCREMENT for partitioned tables using
        either of the InnoDB or
        ARCHIVE storage engines.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha