MySQL Bugs: #43547: Incorrect/Inconsistent Truncate Behavior

Bug #43547	Incorrect/Inconsistent Truncate Behavior
Submitted:	10 Mar 2009 21:35	Modified:	16 Apr 2009 9:02
Reporter:	Amit Saha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
'TRUNCATE' doesn't seem to work correctly on a single table, on MyISAM, Maria, InnoDB and Falcon. Using PBXT, the results are as expected.

I am using a 'pentium-debug-max' build of MySQl 6.0.8-alpha. 

How to repeat:
Please copy the following statements into a .test file:

<code>

CREATE TABLE t1(c1 TINYINT   AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;

TRUNCATE TABLE t1;
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;

ALTER TABLE t1 AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;

</code>

and run it using 'MTR'. Record the .test file with MyISAM and see the .result file. The .result file matches for MyISAM, Maria, Falcon, InnoDB, which however  is probably incorrect. The correct behaviour can be observed using PBXT 1.0.7-rc sources.

Thank you for the report.

Please provide result which you consider correct.

The correct result should be:

<code>
CREATE TABLE t1(c1 TINYINT   AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=PBXT AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
10
11
12
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
10
ALTER TABLE t1 AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=PBXT AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
10
11
12
13

</code>

Please ignore the 'ENGINE=PBXT' part, while comparing the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Thank you for the feedback.

According to http://dev.mysql.com/doc/refman/5.1/en/truncate.html "The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning.". So behavior of MyISAM and other engines is correct and PBXT's is not.

Actual result:

CREATE TABLE t1(c1 TINYINT   AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
10
11
12
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
TRUNCATE TABLE t1;
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
1
ALTER TABLE t1 AUTO_INCREMENT=10;
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
INSERT INTO t1 VALUES(null);
SELECT * FROM t1;
c1
1
10
11
12

Bug filed in PBXT: https://bugs.launchpad.net/pbxt/+bug/369086