Bug #20625 | TRUNCATE TABLE returns wrong affected rows counts | ||
---|---|---|---|
Submitted: | 22 Jun 2006 11:56 | Modified: | 18 Aug 2006 18:56 |
Reporter: | Hartmut Holzgraefe | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0bk/5.1BK | OS: | Linux (linux) |
Assigned to: | CPU Architecture: | Any |
[22 Jun 2006 11:56]
Hartmut Holzgraefe
[22 Jun 2006 13:04]
MySQL Verification Team
Thank you for the bug report. create table t1 (i int) engine=myisam; insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0); create table t2 engine=myisam select a1.i*100+a2.i*10+a3.i as i from t1 a1, t1 a2, t1 a3; create table t3 engine=myisam select a1.i*1000+a2.i as i from t2 a1, t2 a2; create table t4 (i int, v varchar(255)) engine=innodb; insert into t4 select i, md5(i) as v from t3; select version(); truncate table t4; truncate table t3; mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.23-debug | +--------------+ 1 row in set (0.00 sec) mysql> truncate table t4; Query OK, 1000357 rows affected (0.09 sec) mysql> truncate table t3; Query OK, 0 rows affected (0.01 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.12-beta-debug | +-------------------+ 1 row in set (0.00 sec) mysql> truncate table t4; Query OK, 1000357 rows affected (0.09 sec) mysql> truncate table t3; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select version(); +--------------+ | version() | +--------------+ | 4.1.21-debug | +--------------+ 1 row in set (0.01 sec) mysql> truncate table t4; Query OK, 1000000 rows affected (44.89 sec) mysql> truncate table t3; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select version(); +------------------+ | version() | +------------------+ | 4.0.27-debug-log | +------------------+ 1 row in set (0.00 sec) mysql> truncate table t4; Query OK, 1000000 rows affected (6 min 58.82 sec) mysql> truncate table t3; Query OK, 0 rows affected (0.01 sec) mysql>
[29 Jun 2006 13:32]
Dean Swift
Observed functionality is arguably correct because InnoDB removes rows from a file which is shared by all tables whereas MyISAM re-initializes files for each table. For cluster, functionality of ALTER TABLE returns different row counts for version 5.0 and 5.1. Again, this is arguably correct due to differences in structure and functionality.
[29 Jun 2006 20:44]
Hartmut Holzgraefe
> Observed functionality is arguably correct ... i can live with some handlers returning zero while others return the actual row count (although i'd prefer to see zero being returned in all cases for consistency), but InnoDB and cluster obviously return numbers not really related to the actual table size at all. They should either return the correct count or just zero, too.
[18 Aug 2006 18:56]
Sergei Golubchik
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."