MySQL Bugs: #22173: TRUNCATE does not reset auto

Bug #22173	TRUNCATE does not reset auto_increment counter
Submitted:	9 Sep 2006 10:24	Modified:	3 Dec 2007 14:11
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Any (all)
Assigned to:	Christopher Powers	CPU Architecture:	Any

Description:
TRUNCATE does not reset auto_increment counter.

The manual says:
- about InnoDB behaviour (which Falcon should mostly show):
  Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)
- about other storage engines:
  TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0:
  ...
   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.

How to repeat:
Example derived from innodb.test:
SET storage_engine= Falcon;
DROP TABLE IF EXISTS t1;
create table t1 (a integer auto_increment primary key);
insert into t1 (a) values (NULL),(NULL);
truncate table t1;
insert into t1 (a) values (NULL),(NULL);
SELECT * from t1;
a
3 <---- I expect here 1
4 <---- 2
drop table t1;

ToDo: Falcon is seeing the truncate as a row-by-row delete.  If it could recognize the TRUNCATE, then maybe it could reset the sequence.

Assign to Chris Powers

For TRUNCATE, the server first tries to invoke the "delete all rows" interface, such as that provided by InnoDB. If the storage engine does not support "delete all rows", then all rows are deleted individually.

In the absence of "delete all rows", the server must provide some indication to the storage engine that the delete row operations are part of a TRUNCATE operation.

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

ChangeSet@1.2666, 2007-11-12 17:13:28-06:00, chris@xeno.mysql.com +20 -0
  Bug#22173, "TRUNCATE does not reset auto_increment counter"
  - Implemented delete_all_rows() in handler.
  - Added fast truncate to StorageTable, StorageTableShare, StorageDatabase, Database, and Transaction classes.
  - Added Sequence::recreate() to drop and add autoincrement sequence.

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

ChangeSet@1.2675, 2007-11-13 16:09:39-06:00, chris@xeno.mysql.com +20 -0
  Bug#22173, "TRUNCATE does not reset auto_increment counter"
  - Implemented fast truncate via delete_all_rows() interface.
  - Added recreate sequence capability to reset auto_increment counters.

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

ChangeSet@1.2681, 2007-11-15 18:46:54-06:00, chris@xeno.mysql.com +5 -0
  Bug#22173, "TRUNCATE does not reset auto_increment counter"
  
  - Added multiclient testcase, falcon_bug_22173a.
  - Disabled falcon_bug_22173a until concurrency issues with TRUNCATE are resolved.
  - Enabled simple TRUNCATE testcase, falcon_bug_22173
  - Table::retireRecords() - ensure 'records' is non-null after acquiring scavenger lock

falcon_bug_22173.test passes now.

Pushed into 6.0.4-alpha

A note has been added to the 6.0.4 changelog: 

Using TRUNCATE on a Falcon table did not reset the auto-increment counters and used an inefficient method of deleting existing data.

Still doesn't work after the introduction of transaction TRUNCATE TABLE (bug#37016): see bug #42079.