Bug #42079 | Falcon does not reset auto_increment on transactional truncate | ||
---|---|---|---|
Submitted: | 13 Jan 2009 12:20 | Modified: | 15 May 2009 13:41 |
Reporter: | Mattias Jonsson | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Falcon storage engine | Severity: | S2 (Serious) |
Version: | 6.0 | OS: | Any |
Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
Tags: | F_DDL |
[13 Jan 2009 12:20]
Mattias Jonsson
[13 Jan 2009 13:49]
Mattias Jonsson
Sorry, forgot to mention that it currently only exists in mysql-6.0-bugteam tree.
[13 Jan 2009 13:58]
John Embretsen
Relevant section from the MySQL 6.0 documentation for Falcon: "When creating a table using AUTO_INCREMENT within Falcon you should be aware that Falcon uses a persistent auto increment counter. Generated values will never be reused, even when the MySQL server is restarted after rolling back a transaction that allocated auto increment values." http://dev.mysql.com/doc/refman/6.0/en/se-falcon-limits.html
[13 Jan 2009 14:15]
Mattias Jonsson
Yes the above is true for delete, update etc. But for TRUNCATE it is per definition not true. It should reset the auto_increment (sequence in falcon), just like it did before, when TRUNCATE was non transactional. TRUNCATE should behave as atomic DROP TABLE + CREATE, otherwise you use DELETE. see http://dev.mysql.com/doc/refman/6.0/en/truncate.html ...'The AUTO_INCREMENT counter is reset by TRUNCATE TABLE'... and ...'The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values. '...
[13 Jan 2009 14:18]
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/63114 2963 Georgi Kodinov 2009-01-13 Disable tests affected by bug #42079
[13 Jan 2009 15:04]
Kevin Lewis
There was a related change last week that was pushed into the falcon-team tree. Bug#41411 - Access invalid memory for sequence after table truncate It shows that there has been an attempt by Falcon to reset the sequence number after a truncate. That happens here; \storage\falcon\Database.cpp(1523): sequence = sequence->recreate(); This is at the end of Database::truncateTable(). Bug#41411 made sure that a higher level pointer to that sequence got refreshed, just in case the sequence location changed during the call to sequence->recreate(); We should check why this code is not being called in the tree where this bug can be seen.
[13 Jan 2009 15:06]
John Embretsen
Someone should tell the doc folks to never say "never"... ;)
[18 Jan 2009 5:32]
Kevin Lewis
Here is the problem. As of BUG#37016, the server sets thd->transaction.on to true for truncate DDL operations. With that change, Falcon no longer does a fast truncate of the table. Falcon only resets the autoincrement counter if it does a fast truncate. So now, Falcon neither resets the counter, nor does the truncate fast. This following change was made for BUG#32730 according to what we were advised to do by Sergei Golubchik; In StorageInterface::delete_all_rows() - if (thd_sql_command(mySqlThread) != SQLCOM_TRUNCATE) + if (current_thd->transaction.on) DBUG_RETURN(my_errno=HA_ERR_WRONG_COMMAND); If rows are deleted from a Falcon Table 1 by 1, Falcon does not and should not reset the autoincrement counter automatically. But if the server could ensure the correct level of locking, it should be able to do it with the Handler::reset_auto_increment() function. As of BUG#37016, it will try this. However, Falcon does not implement this function. It looks like the correct solution to 42079 is to implement reset_auto_increment() so that the server can reset an autoincrement counter if it needs to after an unqualified delete within a stored procedure. But also we should quit using storage\falcon\ha_falcon.cpp(1075): if (current_thd->transaction.on) to determine if the truncate is a real DDL statement that must execute fast and without transactional support within Falcon. Instead, Sergey Golubchik says we can go back to using thd_sql_command(mySqlThread) == SQLCOM_TRUNCATE. Kevin ----------------------------------------------- -------- Original Message -------- Subject: bugs 37016 and 42079 Date: Fri, 16 Jan 2009 15:57:11 -0500 From: Ann W. Harrison <ann@mysql.com> To: Sergei Golubchik <serg@mysql.com>, Kevin Lewis <klewis@mysql.com> Hi Sergei, I'm concerned that the solution to bug 37016 makes it impossible for Falcon to do a fast truncate. You may remember that there's a possible confusion between a truncate (DDL) and an unqualified delete from a procedure. Both call delete_all_rows, but the semantics of the two operations are different. Based on conversation with you, Falcon now decides which operation to execute based on the setting of thd->transaction.on - if it's FALSE, we do a fast truncate, just throwing out the structure that implement the table, and reset the autonumber value. If its TRUE, we go back to a row-by-row delete which is slow and does not reset the autonumber field, but which does provide the correct semantics for an unqualified delete. Now, it appears that thd->transaction.on is TRUE for both cases. We can fix the autonumber problem by supplying a routine that lets the server call Falcon to reset the autonumber - SMOP - but that still means that we're doing a very inefficient truncate. The problem addressed in bug 37016 is that a TRUNCATE can cause an error in InnoDB if the table being truncated is a parent in a foreign key relationship. That condition will never arise in Falcon - the internal foreign key implementation raises an error if a parent table is truncated. Should we decide which sort of delete_all_rows to execute based on whether thd_sql_command(mySqlThread) is SQLCOM_TRUNCATE rather than the transaction state? ------------------------------------------------------ -------- Original Message -------- Subject: Re: bugs 37016 and 42079 Date: Fri, 16 Jan 2009 23:29:43 +0100 From: Sergei Golubchik <serg@mysql.com> To: Ann W. Harrison <ann@mysql.com> CC: Kevin Lewis <klewis@mysql.com> References: <4970F4A7.7030808@mysql.com> Hi, Ann! Yes. I should've included you in CC when I wrote this: https://intranet.mysql.com/secure/mailarchive/mail.php?folder=214&mail=895 "Remove ha_enable_transactions() from mysql_truncate() and fix falcon to test for SQLCOM_TRUNCATE in delete_all_rows() instead of thd->transaction.on (so that falcon could continue to "disable transactions" even when we don't call ha_enable_transactions)" This was early in the discussion of possible solutions, and when we got to actual fixing (changing the asignee on the way), the falcon reference got lost somehow :( Sorry for this. Sergei --------------------------------------------- Ann Harrison wrote on Fri, 16 Jan 2009; Excellent - thanks for the quick reply. I do like easy fixes. Best, Ann
[18 Jan 2009 5:38]
Kevin Lewis
-------- Original Message -------- Subject: [Fwd: Re: bugs 37016 and 42079] Date: Fri, 16 Jan 2009 17:41:13 -0500 From: Ann W. Harrison <ann@mysql.com> To: dev-falcon@mysql.com, Sergey Vojtovich <svoj@mysql.com> Sergey, Based on Sergei's message below, I think a one line change will fix 42079 - actually less than a one line change - uncomment the old line and delete the current test in ha-falcon. At the moment, I seen no reason why we need to provide a autonumber reset method. Someday it might be nice to have, but with this change all our truncates will be fast and will reset the autonumber themselves. Cheers, Ann
[20 Jan 2009 15:45]
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/63612 2969 Sergey Vojtovich 2009-01-20 BUG#42079 - Falcon does not reset auto_increment on transactional truncate With fix for BUG37016 Falcon didn't reset auto_increment counter on TRUNCATE, and was falling back to slow row-by-row delete. With this fix Falcon is accepting quick truncate and is resetting auto_increment counter.
[20 Jan 2009 18:53]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:timothy.smith@sun.com-20090114143745-x2dvnmix6gjlt6z6) (merge vers: 6.0.10-alpha) (pib:6)
[4 Feb 2009 11:16]
Bugs System
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:svoj@mysql.com-20090121120057-qweugfjm36g6opn9) (merge vers: 6.0.10-alpha) (pib:6)
[4 Feb 2009 12:24]
John Embretsen
Doc team: Please consider updating the documentation at http://dev.mysql.com/doc/refman/6.0/en/se-falcon-limits.html This says that "Generated values will never be reused, even when the MySQL server is restarted after rolling back a transaction that allocated auto increment values.". The "never" part is obviously not true for TRUNCATE TABLE operations (see earlier comments).
[15 May 2009 13:41]
MC Brown
A note has been added to the 6.0.10 changelog, and I've added a note to the main docs about the TRUNCATE change: Sequences and auto increment values in Falcon tables would not reset, even when a TRUNCATE TABLE operation was executed. The behavior has now been updated to reset the values to the original table definition when TRUNCATE TABLE is applied.