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:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: F_DDL
Triage: Triaged: D2 (Serious)

[13 Jan 2009 12:20] Mattias Jonsson
Description:
After the fix of bug#37016, 'TRUNCATE TABLE' is now transactional, which is forbidden in falcons ::delete_all_rows().

Which results in a row by row deletion of the table. And that the reset of the auto_increment is not done.

test parts.partition_auto_increment_falcon fails now (both the non partitioned and the partitioned truncate test).

How to repeat:
create table t1 (a int not null auto_increment primary key) engine falcon;
insert into t1 values (NULL), (NULL);
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=Falcon AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
select * from t1;
a
1
2
truncate table t1;
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=Falcon AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
insert into t1 values (NULL), (NULL);
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=Falcon AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
select * from t1;
a
3
4
drop table t1;

Suggested fix:
Implement ::reset_auto_increment() or support transactions in ::delete_all_rows()
[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.