Bug #58492 Allow TRUNCATE TABLE to remember the original AUTO_INCREMENT value

• Submitted: 25 Nov 2010 13:22
• Reporter: Rene' Cannao'
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: any
• OS: Any
• CPU Architecture: Any

[25 Nov 2010 13:22] Rene' Cannao'

Description:
At http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html the Manual says:

"The AUTO_INCREMENT counter is reset to zero 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."

In some case a user can find useful to not reset the AUTO_INCREMENT value.

How to repeat:
mysql> CREATE TABLE tbl1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE tbl1\G
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> TRUNCATE TABLE tbl1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE tbl1\G
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Please introduce some way to keep the current auto_increment value in the table definition.
For example an extension of the TRUNCATE TABLE statement or some system variable.