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: | |
Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
Version: | 5.1+ | OS: | Any |
Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[22 Dec 2007 12:51]
Philip Stoev
[23 Dec 2007 5:39]
Kevin Lewis
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.
[23 Dec 2007 5:58]
Kevin Lewis
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
[23 Dec 2007 7:13]
Philip Stoev
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).
[23 Dec 2007 12:41]
Philip Stoev
Setting to Verified, two individual test cases are available.
[3 Jan 2008 15:05]
Kevin Lewis
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.
[9 Jan 2008 17:54]
Mattias Jonsson
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?
[9 Jan 2008 18:45]
Kevin Lewis
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.
[11 Feb 2008 13:02]
Mattias Jonsson
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);
[11 Feb 2008 21:18]
Mattias Jonsson
Since it can occur in MyISAM in 5.1 I changed the synopsis and version fields
[11 Feb 2008 21:28]
Mattias Jonsson
For reference, Bug#34366 is a duplicate of this (MyISAM insert with auto_increment on secondary indexed column). Changing Target Version to 5.1
[12 Feb 2008 16:03]
Mattias Jonsson
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)
[13 Feb 2008 12:14]
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/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.
[13 Feb 2008 12:17]
Mattias Jonsson
the proposed patch is for 5.1, I will add a small patch for 6.0 (only containing the test file + result for Falcon)
[13 Feb 2008 12:22]
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/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
[16 Feb 2008 8:58]
Philip Stoev
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.
[18 Feb 2008 14:26]
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/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.
[19 Feb 2008 8:57]
Mattias Jonsson
Will add reserved range handling (better replication support)
[21 Feb 2008 9:09]
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/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.
[21 Feb 2008 9:46]
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/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
[21 Feb 2008 12:10]
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/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.
[21 Feb 2008 12:39]
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/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
[5 Mar 2008 22:56]
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/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.
[6 Mar 2008 9:37]
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/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
[14 Mar 2008 8:35]
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/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.
[1 Apr 2008 21:04]
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/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.
[2 Apr 2008 12:01]
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/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
[25 Apr 2008 11:39]
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/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.
[30 Apr 2008 11:05]
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/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
[8 May 2008 13:41]
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/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...)
[9 May 2008 7:18]
Mattias Jonsson
Pushed into mysql-6.0-bugteam
[22 May 2008 9:50]
Bugs System
Pushed into 6.0.6-alpha
[24 May 2008 17:05]
Jon Stephens
Documented in the 6.0.6 changelog as follows: Inserts failed on partitioned tables containing user-supplied values for an AUTO_INCREMENT column.
[3 Sep 2008 19:43]
Kevin Lewis
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.
[1 Oct 2008 12:01]
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/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)
[3 Oct 2008 9:31]
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/55199 2686 Mattias Jonsson 2008-10-03 post push fix for bug#38804 (back port of bug#33479) Removes the regression bug#38751.
[9 Oct 2008 17:51]
Bugs System
Pushed into 5.1.30 (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)
[17 Oct 2008 16:41]
Bugs System
Pushed into 6.0.8-alpha (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[28 Oct 2008 21:01]
Bugs System
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)
[28 Oct 2008 22:20]
Bugs System
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)
[1 Nov 2008 9:45]
Bugs System
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)