MySQL Bugs: #33479: Insert on a partitioned auto_increment table gives ER_AUTOINC_READ

Bug #33479	Insert on a partitioned auto_increment table gives ER_AUTOINC_READ_FAILED
Submitted:	22 Dec 2007 12:51	Modified:	24 May 2008 17:05
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
An INSERT on a partitioned Falcon table would sometimes fail with:

ERROR HY000: Failed to read auto-increment value from storage engine

the error log will show:

071222 13:31:33 [ERROR] Failed to calculate auto_increment value for partition

The structure of the table is

CREATE TABLE `tb1_eng1` (
  `i1` int(11) NOT NULL AUTO_INCREMENT,
  `f1` int(11) DEFAULT NULL,
  `f2` char(15) DEFAULT NULL,
  `f3` decimal(5,3) DEFAULT NULL,
  `f4` datetime DEFAULT NULL,
  PRIMARY KEY (`i1`)
) ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (i1) (PARTITION part1 DATA DIRECTORY = '/data1/6.0.4/systest_vardir/master-data/tmpdata' INDEX DIRECTORY = '/data1/6.0.4/systest_vardir/master-data/tmpindex' ENGINE = Falcon, PARTITION part2 DATA DIRECTORY = '/data1/6.0.4/systest_vardir/master-data/tmpdata' INDEX DIRECTORY = '/data1/6.0.4/systest_vardir/master-data/tmpindex' ENGINE = Falcon) */ 

Meaning that it is partitioned by KEY on an AUTO_INCREMENT column.

The error from the log is generated in ha_partition::get_auto_increment()

How to repeat:
Run the systems iuds6 test using Falcon as a storage engine.

Here is an even more deterministic test;
DROP TABLE t1;
INSERT INTO t1 (f1) VALUES (246);
INSERT INTO t1 (f1) VALUES (254);
INSERT INTO t1 (f1) VALUES (0);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

The problem is in ha_partition::get_auto_increment().  This function has this comment;   "We will always let the first handler keep track of the auto increment value for all partitions."  Well that will never work of the storage engine finds the next autoincrement value by looking at the last committed sequence value, which is what Falcon does. 

This test uses a hash partition.  Explicit value 246 is hashed to the first partition and 254 is hashed to the second.  So now the second partition is more than 1 delta higher than the first partition, which ha_partition::get_auto_increment() assumes can never happen.  Also, ha_partition::get_auto_increment() seems to assume that the handler->get_auto_increment() will not increment the sequence value every time it is called, independent of what is put into the table.  Falcon does this.

For Falcon, the sequence for any autoincrement field is increased whenever get_auto_increment() is called and also when a value higher than the current sequence value is added to the table.

Triage Values: D3: No crash or loss of data, but when a partitioned table gets into this state, where the highest sequence is not in the first table, then all the following inserts will fail until a higher value is explicitly entered that happens to hash into the first table.  W2 The unacceptable workaround is to avoid explicit assignment of autoincrement fields when using hash partitioning of the autoincrement field.  I3 - This is a dysfunction in non-core functionality.

The deterministic test above should be;
DROP TABLE t1;
CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1))
   ENGINE=falcon PARTITION BY HASH(f1) PARTITIONS 2;
INSERT INTO t1 (f1) VALUES (246);
INSERT INTO t1 (f1) VALUES (254);
INSERT INTO t1 (f1) VALUES (0);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

I can confirm that this problem only occurs when the values in the auto_increment column are user-specified and not autogenerated. E.g. this insert:

insert into t1 values ();

never causes the issue. It is a tough call for me if this bug is important or not -- on one hand, user-specified values in an auto_increment column do not make much sense, on the other hand, using autogenerated values in a table partitioned on a KEY of an auto_increment column does not appear to provide any benefits as far as partitioning is concerned -- partition pruning is unlikely to kick in, and the two partitions will not be scanned in parallel (however this is a general limitation of partitioning).

Setting to Verified, two individual test cases are available.

Mattias, It looks like this needs to be fixed in ha_partition::get_auto_increment().  Note that Falcon increments the sequence for each of its tables/partitions each time StorageInterface::get_auto_increment() is called.  So you may have to explicitly use the highest autoincrement among all segments.

Problem seems to be this:
the test case actually insert the two first values into the first partition (partition 0) and that is handled correctly by falconengine->write_row trought ha_partition->write_row get_auto_increment.
now the first partition have auto increment value = 254 (should be 255) and the second have 0.
So when the third insert occurs it first tries to do update_auto_increment which calls ha_partition->get_auto_increment (which incorrectly assumes it can ask for an auto increment value) and it gets 255 for the first partition and one value reserved and for the second partition it gets 1 and one value reserved and since this is an empty intersection it fails (after trying the same operation again).

If the mutex (which was altered to only be used for innodb in Bug#33480) was used by all engines, then it would probably be OK to just use the highest auto increment value and regardless if the intersection was empty or not. But without it it can not be solved that easy.

Are there any way to set a the auto_increment value of a handler through the storage engine api after the handler is created?

IRC question by Mattias,
--------------------
Mattiasj-> I am now looking at Bug#33479 - get_auto_increment bug and think I found a new bug: when doing SHOW CREATE TABLE on a non partitioned falcon table it does not give the current auto_increment_value But a partitioned falcon table does...  It does use it to initialize auto_increment_value if given the option AUTO_INCREMENT at create though.
--------------------
There was a recent bug fix which changed this; Bug#33661.  This changeset is probably not yet in mysql-6.0-engines.  It was a one line change if you want to look at the patch.

You make a good point below about the using the mutex to check current values among all partitions.  Falcon's implementation of Handler::get_auto_increment is StorageInterface::get_auto_increment, ha_falcon.cpp.  It makes an internal call to storageShare->getSequenceValue(1);  The value 1 indicates how much it should be incremented.  If the value was zero, the sequence would be returned without incrementing, not re-written, so there would be not possibility of a lock conflict or a wait state.

Is there a way to indicate no increment in Handler::get_auto_increment?  Maybe you can use the mutex to just read the current values from the partitions and the set it in the first partition outside the mutex.  If multiple threads do this at the same time, the returned value will be a unique sequence number.

This can also happen in MyISAM (if using auto_increment on a secondary column in a multiple-column index:
CREATE TABLE t1 (
c1 INT NOT NULL,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1, c2))
ENGINE=MyISAM
PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (7,7);
INSERT INTO t1 VALUES (7,NULL);

Since it can occur in MyISAM in 5.1 I changed the synopsis and version fields

For reference, Bug#34366 is a duplicate of this (MyISAM insert with auto_increment on secondary indexed column). Changing Target Version to 5.1

Found a bug in InnoDB partitioning with auto_increment too:
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE=InnoDB
PARTITION BY HASH(c1)
PARTITIONS 2;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (5);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (13);
INSERT INTO t1 VALUES (15);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
2
3
4
5
13
15

(It should be 1,5,6,13,15,16,17)

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

ChangeSet@1.2518, 2008-02-13 13:14:23+01:00, mattiasj@witty. +7 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon)
  
  Changed the auto_increment handling for partitioning:
  Added a variable in table_share for auto_increment value
  and using the table_share->mutex to lock around read + update.

the proposed patch is for 5.1, I will add a small patch for 6.0 (only containing the test file + result for Falcon)

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

ChangeSet@1.2531, 2008-02-13 13:22:42+01:00, mattiasj@witty. +2 -0
  Bug#33479: Failures using auto_increment and partitioning
  
  This is 6.0 test cases for falcon only, the real patch is separate, since
  it applies to 5.1

This problem is still present in the last 6.0.4 build -- both my non-deterministic test case and Kevin's deterministic one fail.

The latest 6.0.4 build does not include the partition_auto_increment_falcon test mentioned in the last commit message.

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

ChangeSet@1.2518, 2008-02-18 15:25:50+01:00, mattiasj@witty.ndb.mysql.com +8 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon)
  
  Changed the auto_increment handling for partitioning:
  Added a variable in table_share for auto_increment value
  and using the table_share->mutex to lock around read + update.

Will add reserved range handling (better replication support)

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

ChangeSet@1.2518, 2008-02-21 10:09:43+01:00, mattiasj@witty. +3 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon)
  
  Code only patch (the test cases will be in a separate patches
  5.1+ (InnoDB+MyISAM) and 6.0 (Falcon)
  
  Changed the auto_increment handling for partitioning:
  Added a ha_data variable in table_share for storage engine specific data such as
  auto_increment value handling in partitioning
  and using the ha_data->mutex to lock around read + update.
  
  The idea is this:
  Store the table's reserved auto_increment value in
  the TABLE_SHARE and use a mutex to lock it for reading and updateing it,
  only accessing all partitions when it is not initialized.
  Also allow reservations of ranges, and if no one has done a reservation
  afterwards, lower the reservation to what was actually used after
  the statement is done (via release_auto_increment from WL#3146).
  
  This should be quite fast and work with any local storage engine.

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

ChangeSet@1.2519, 2008-02-21 10:46:18+01:00, mattiasj@witty. +5 -0
  Bug#33479: auto_increment failures in partitioning
  
  This is a test case patch only
  
  This is a new test case include file for partitioning and auto_increment,
  and test + result files for InnoDB and MyISAM to be used in 5.1 and up

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

ChangeSet@1.2520, 2008-02-21 13:10:34+01:00, mattiasj@witty. +2 -0
  Bug#33479: auto_increment failures in partitioning
  
  This patch build on the previous 'code only' patch
  
  Missed some special case initializations found when trying with falcon.

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

ChangeSet@1.2548, 2008-02-21 13:39:07+01:00, mattiasj@witty. +2 -0
  Bug#33479: auto_increment failures in partitioning
  
  Test case only patch, separated for falcon, 6.0

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

ChangeSet@1.2518, 2008-03-05 23:56:06+01:00, mattiasj@witty. +3 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon)
  
  Updated code only patch (the test cases will be in a separate patches
  5.1+ (InnoDB+MyISAM) and 6.0 (Falcon)
  This is a full code only patch which I have updated according to sergs
  comments
  Changed the auto_increment handling for partitioning:
  Added a ha_data variable in table_share for storage engine specific data such as
  auto_increment value handling in partitioning
  and using the ha_data->mutex to lock around read + update.
  
  The idea is this:
  Store the table's reserved auto_increment value in
  the TABLE_SHARE and use a mutex to lock it for reading and updateing it,
  only accessing all partitions when it is not initialized.
  Also allow reservations of ranges, and if no one has done a reservation
  afterwards, lower the reservation to what was actually used after
  the statement is done (via release_auto_increment from WL 3146).
  
  This should also lead to better concurrancy
  and work with any local storage engine.

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

ChangeSet@1.2519, 2008-03-06 10:36:45+01:00, mattiasj@witty.ndb.mysql.com +5 -0
  Bug#33479: auto_increment failures in partitioning
  
  This is the updated 5.1+ tests

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

ChangeSet@1.2520, 2008-03-14 09:35:13+01:00, mattiasj@witty. +7 -0
  Bug#33479: auto_increment failures in partitioning
  
  This patch is on based on the previous patches.
  
  I have updated according to guilhems review comments.
  
  Changed the locking handling to lock, execute, unlock in
  the same block/function.
  Simplifyed handling of auto_increment as a secondary index column.
  Better doxygen comments.

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

ChangeSet@1.2518, 2008-04-01 23:04:40+02:00, mattiasj@witty. +11 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon. Locking issues, not handling
  multi row inserts properly etc.)
  
  This is a full patch which I have updated according to sergs and
  guilhems comments.
  Changed the auto_increment handling for partitioning:
  Added a ha_data variable in table_share for storage engine specific data such as
  auto_increment value handling in partitioning
  and using the ha_data->mutex to lock around read + update.
  
  The idea is this:
  Store the table's reserved auto_increment value in
  the TABLE_SHARE and use a mutex to, lock it for reading and updating it
  and unlocking it, in one block. Only accessing all partitions
  when it is not initialized.
  Also allow reservations of ranges, and if no one has done a reservation
  afterwards, lower the reservation to what was actually used after
  the statement is done (via release_auto_increment from WL 3146).
  The lock is kept from the first reservation if it is statement based
  replication and a multi row insert statement
  (insert select, load data etc.)
  
  This should also lead to better concurrancy
  and work with any local storage engine.

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

ChangeSet@1.2519, 2008-04-02 14:00:59+02:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +1 -0
  Bug#33479 auto_increment failures in partitioning
  
  changed one row for better check if statement based binlogging

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

ChangeSet@1.2518, 2008-04-25 13:37:58+02:00, mattiasj@witty. +9 -0
  Bug#33479: auto_increment failures in partitioning
  
  Several problems with auto_increment in partitioning
  (with MyISAM, InnoDB and Falcon. Locking issues, not handling
  multi-row INSERTs properly etc.)
  
  This is a full patch which I have updated according to sergs and
  guilhems comments.
  Changed the auto_increment handling for partitioning:
  Added a ha_data variable in table_share for storage engine specific data
  such as auto_increment value handling in partitioning, also see WL 4305
  and using the ha_data->mutex to lock around read + update.
  
  The idea is this:
  Store the table's reserved auto_increment value in
  the TABLE_SHARE and use a mutex to, lock it for reading and updating it
  and unlocking it, in one block. Only accessing all partitions
  when it is not initialized.
  Also allow reservations of ranges, and if no one has done a reservation
  afterwards, lower the reservation to what was actually used after
  the statement is done (via release_auto_increment from WL 3146).
  The lock is kept from the first reservation if it is statement based
  replication and a multi-row INSERT statement where the number of
  candidate rows to insert is not known in advance (like INSERT SELECT,
  LOAD DATA, unlike INSERT VALUES (row1), (row2),,(rowN)).
  
  This should also lead to better concurrancy (no need to have a mutex
  protection around write_row in all cases)
  and work with any local storage engine.

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

ChangeSet@1.2641, 2008-04-30 13:04:22+02:00, mattiasj@witty. +2 -0
  Bug#33479 - auto_increment failures in partitioning tables
  
  Additional testcases for auto_increment in partitioned falcon tables
  only for 6.0

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

ChangeSet@1.2655, 2008-05-08 15:41:16+02:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +3 -0
  Bug#33479 - auto_increment failures in partitioning tables
  
  Additional testcases for auto_increment in partitioned falcon tables
  only for 6.0
  
  (Due to bug 36595 this is disabled...)

Pushed into mysql-6.0-bugteam

Pushed into 6.0.6-alpha

Documented in the 6.0.6 changelog as follows:

        Inserts failed on partitioned tables containing user-supplied values for
        an AUTO_INCREMENT column.

Kristofer Pettersson has asked that I reconsider the risk assessment.  Since the feature has now been implemented in 6.0, I think the risk can now be re-evaluated.

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

2844 Mattias Jonsson	2008-10-01 [merge]
      merge (basically a null merge, since it contains a backport of bug#33479 as a fix for Bug#38804)

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

2686 Mattias Jonsson	2008-10-03
      post push fix for bug#38804 (back port of bug#33479)
      Removes the regression bug#38751.

Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)

Pushed into 5.1.29-ndb-6.2.17  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)