Bug #29507 TRUNCATE shows too many rows affected

• Submitted: 3 Jul 2007 3:58
• Modified: 20 Jun 2010 0:49
• Reporter: Jared S (Silver Quality Contributor)
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.44
• OS: Windows (Vista)
• CPU Architecture: Any
• Tags: delete, qc, rows effected, truncate, v6

[3 Jul 2007 3: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 4: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 13: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 1: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 10: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 2008 19: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 2008 15:20] Bugs System

Pushed into 5.1.25-rc

[19 May 2008 20:18] Paul DuBois

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

[22 May 2008 9:50] Bugs System

Pushed into 6.0.6-alpha

[27 May 2008 18: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 2008 21: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 2008 1: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.

[5 Oct 2008 16:47] Jon Stephens

Hi,

Can we please get the 3-part version number for the 5.1 merge of this fix, so we can document and close? Thanks!

[27 Oct 2008 18:54] Jon Stephens

Tagged changelog entry for 5.1.28 release.

Left in NDI status per discussion with Joro (waiting on decision whether or not to push to 5.0).

[3 Nov 2008 13:00] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57688

2690 Kristofer Pettersson	2008-11-03
      Bug#29507 TRUNCATE shows to many rows effected
      
      TRUNCATE TABLE for InnoDB tables returned a count showing an approximation
      of the number of rows affected to gain efficiency.
      Now the statement always returns 0 rows affected for clarity.

[3 Nov 2008 13:05] Kristofer Pettersson

Added a proposal for a new patch based on discussions with Sergei G. This patch will help to resolve the regression in bug#40386 "Not flushing query cache after truncate".

The TRUNCATE TABLE specific code in ha_innobase::info must also be reverted to complete this patch because it breaks the API which dictates that stats.records never should be 0 if the table isn't empty.

Turning over to InnoDB.

[3 Nov 2008 13:51] Kristofer Pettersson

Approved on IRC

[6 Nov 2008 14:16] Heikki Tuuri

Sergei,

what value InnoDB can return as the row count?

InnoDB does not know the exact count.

Can it return some huge number?

--Heikki

[6 Nov 2008 14:28] Sergei Golubchik

Heikki, you can return any number, as long as it's not zero.

The rule for table->file->stats.records is (and always was) - it's either exact number of rows, when HA_STATS_RECORDS_IS_EXACT is set, and the approximate number of rows otherwise. But even if it's approximate it can be zero only if the table is empty. Optimizer relies on it.

As for this particular bug - Bug#29507, "TRUNCATE shows too many rows affected" - we've fixed it, see the patch attached. TRUNCATE was never supposed to return a number of rows, it's even in the manual. When TRUNCATE worked as DROP+CREATE it never returned number of rows. The bug was that when TRUNCATE worked via DELETE, we forgot to disable that part of DELETE that returns the number of affected rows. It's now fixed.

So for TRUNCATE it doesn't matter what number you return, as long as the above invariant (it's zero only if the table is empty) holds.

[10 Nov 2008 10:52] Bugs System

Pushed into 6.0.8-alpha  (revid:[e-mail]-20081103130842-axiwbe6lfnmi6mzz) (version source revid:[e-mail]-20081103160635-zv2ux0yweso8lgky) (pib:5)

[10 Nov 2008 11:36] Bugs System

Pushed into 5.1.30  (revid:[e-mail]-20081103130842-axiwbe6lfnmi6mzz) (version source revid:[e-mail]-20081104113355-1xg11r9sdp0k6ovj) (pib:5)

[10 Nov 2008 18:00] Paul DuBois

Already noted in 5.1.28, 6.0.6 changelogs. Re-closing.

[19 Jan 2009 11:22] Bugs System

Pushed into 5.1.31-ndb-6.2.17 (revid:[e-mail]-20090119095303-uwwvxiibtr38djii) (version source revid:[e-mail]-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

[19 Jan 2009 13:00] Bugs System

Pushed into 5.1.31-ndb-6.3.21 (revid:[e-mail]-20090119104956-guxz190n2kh31fxl) (version source revid:[e-mail]-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

[19 Jan 2009 16:06] Bugs System

Pushed into 5.1.31-ndb-6.4.1 (revid:[e-mail]-20090119144033-4aylstx5czzz88i5) (version source revid:[e-mail]-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

[5 May 2010 15:04] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 3:01] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 6:00] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:28] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:56] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 22:57] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:09] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:24] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:03] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:45] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:30] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)