MySQL Bugs: #20773: TRUNCATE empty TABLE returns row count of 1

Bug #20773	TRUNCATE empty TABLE returns row count of 1
Submitted:	29 Jun 2006 13:49	Modified:	18 Aug 2006 18:55
Reporter:	Dean Swift	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0/5.1BK	OS:	Windows (Windows/Linux)
Assigned to:		CPU Architecture:	Any

Description:
TRUNCATE on an empty TABLE always return rows affected as 1, regardless of storage engine. This may be related to bug #20625 ( http://bugs.mysql.com/bug.php?id=20625 ) where behaviour differs with storage engine.

How to repeat:
mysql> CREATE TABLE foo (
    ->   id INT PRIMARY KEY
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> TRUNCATE foo;
Query OK, 1 row affected (0.03 sec)

mysql> DROP TABLE foo;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE foo (
    ->   id INT PRIMARY KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> TRUNCATE foo;
Query OK, 1 row affected (0.02 sec)

Suggested fix:
Perform SELECT COUNT(*) FROM tbl_name before and after TRUNCATE operation to obtain difference in rows. It may be necessary to perform LOCK TABLES tbl_name [LOW_PRIORITY] WRITE before and UNLOCK TABLES after these operations to ensure accurate counts. Alternatively, you need to set your isolation level accordingly. So, the full sequence is:

LOCK TABLES tbl_name [LOW_PRIORITY] WRITE
SELECT COUNT(*) FROM tbl_name
TRUNCATE tbl_name
SELECT COUNT(*) FROM tbl_name
UNLOCK TABLES

I was not able to repeat the behaviour described for MyISAM with 5.0.23-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (id int primary key) engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> truncate foo;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (id int primary key) engine = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> truncate foo;
Query OK, 1 row affected (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

So, it looks like a duplicate of bug #20625 for me.

Thank you for the bug report. I was able for to repeat just for InnoDB
engine on Windows and Linux and > 5.0.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.09 sec)

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

mysql> DROP TABLE foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql> TRUNCATE foo;
Query OK, 1 row affected (0.05 sec)

mysql>

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)

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

mysql> DROP TABLE foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> TRUNCATE foo;
Query OK, 1 row affected (0.00 sec)

mysql> 

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.02 sec)

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

mysql> DROP TABLE foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (
    -> id INT PRIMARY KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

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

mysql>

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See http://dev.mysql.com/doc/refman/5.0/en/truncate.html

"TRUNCATE TABLE  differs from DELETE in the following ways: ... The number of deleted rows is not returned."