Bug #29507 TRUNCATE shows to many rows effected

• Submitted: 3 Jul 2007 5:58
• Modified: 25 Jun 3:45
• Reporter: Jared S (Silver Quality Contributor)
• Status: Need Doc Info
• Category: Server: InnoDB
• Severity: S3 (Non-critical)
• Version: 5.0.44
• OS: Microsoft Windows (Vista)
• Assigned to: Tim Smith
• Target Version: 5.0
• Tags: qc, rows effected, delete, truncate, v6
• Triage: D4 (Minor)

[3 Jul 2007 5:58] Jared S

Description:
Hi,

I have a very strange problem when I am deleting rows from table that has 1309 rows.  When
I execute TRUNCATE TABLE command, Query Browser shows 1695 rows effected.

How to repeat:
1. Upload my private script
2. truncate table  `sale-contacts`;

RESULT return 1695 rows, but table has 1309

[3 Jul 2007 6:06] Jared S

Further more, if you run truncate command again on empty table, it always returns 1 row
effected, but table is empty, and query should not yeild any results of any type.

[3 Jul 2007 15:06] Heikki Tuuri

Hmm... maybe the row count is just an estimate? InnoDB does not keep and store an accurate
row count of a table like MyISAM does.

[10 Jul 2007 3:39] Jared S

DELETE FROM `sale-contacts` -- Works fine, gives accurate count, so InnoDB has the
'brains' to do the math.  

I have tried few different things like updating NULLs to '' and deleting all columns
execpt for PRIMARY, but the 'rows effected' just keeps getting weirder

Congrates on the takeover.

[10 Jul 2007 12:26] Valeriy Kravchuk

Verified just as described:

mysql> show create table tbiggg;
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
| Table  | Create Table
                                                       |
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
| tbiggg | CREATE TABLE `tbiggg` (
  `c1` char(100) default NULL,
  `c2` char(100) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------
-------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select count(*) from tbiggg;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (15.75 sec)

mysql> update tbiggg set c2='abcdefgh';
Query OK, 2097152 rows affected (3 min 5.13 sec)
Rows matched: 2097152  Changed: 2097152  Warnings: 0

mysql> truncate table tbiggg;
Query OK, 2097243 rows affected (0.25 sec)

mysql> truncate table tbiggg;
Query OK, 1 row affected (0.03 sec)

mysql> truncate table tbiggg;
Query OK, 1 row affected (0.03 sec)

[25 Apr 21:28] Calvin Sun

Pushed into InnoDB 5.1 repository.

Note to doc: In InnoDB, the row count is only a rough estimate used by SQL
optimization. InnoDB is now return row count 0 for TRUNCATE operation.

[14 May 17:20] Bugs System

Pushed into 5.1.25-rc

[19 May 22:18] Paul DuBois

Setting report to Patch queued pending re-push of this patch.

[22 May 11:50] Bugs System

Pushed into 6.0.6-alpha

[27 May 20:39] Paul DuBois

The push into 6.0.x was a null merge that changed nothing. Resetting report to Patch
Approved pending further push of patch into 6.0.x.

The "parent" report for this set of bugs is Bug#32440.

[24 Jun 23:28] Calvin Sun

Merged into 6.0.6-alpha, according to Tim. But the patch has not been pushed into 5.1 yet.

[25 Jun 3:45] Paul DuBois

Noted in 6.0.6 changelog.

TRUNCATE TABLE for InnoDB tables returned a count showing too many
rows affected. Now the statement returns 0 for InnoDB tables. 

Setting report to Need Doc Info pending push into 5.1.x.