Bug #40386 Not flushing query cache after truncate
Submitted: 29 Oct 2008 9:24 Modified: 19 Jun 2010 18:00
Reporter: Artur Banul Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.29-rc, 5.1, 6.0 bzr OS:Linux
Assigned to: Calvin Sun CPU Architecture:Any
Tags: regression, truncate query cache

[29 Oct 2008 9:24] Artur Banul
Description:
While truncating table query cache is not flushed.

How to repeat:
mysql> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

mysql> create table test(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(123456);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from test;
Empty set (0.00 sec)

#--------------------------------------------------
#WITH NEW SETTINGS
#--------------------------------------------------
mysql> set global query_cache_type = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.01 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(123456);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from test;
Empty set (0.00 sec)

#--------------------------------------------------
#WITH NEW SETTINGS
#--------------------------------------------------

mysql> set global query_cache_type = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'query_cache_type';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_type | DEMAND |
+------------------+--------+
1 row in set (0.00 sec)

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

mysql> create table test(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(123456);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from test;
Empty set (0.00 sec)
[29 Oct 2008 10:41] Sveta Smirnova
Thank you for the report.

Verified as described.
[29 Oct 2008 11:07] Sveta Smirnova
Bug was introduced after version 5.1.26
[30 Oct 2008 23:17] Kristofer Pettersson
The QC invalidation does work with MyISAM. The problem occurs when we truncate an InnoDB table.
[31 Oct 2008 10:55] Kristofer Pettersson
InnoDB takes this route:  
  if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
      goto trunc_by_del;
[31 Oct 2008 11:33] Kristofer Pettersson
In mysql_delete:
...
    ha_rows const maybe_deleted= table->file->stats.records;
    DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
    if (!(error=table->file->ha_delete_all_rows()))
    {
      error= -1;				// ok
      deleted= maybe_deleted;  <--- maybe_deleted == 0, but we're deleting 1
      goto cleanup;
    }
...
[31 Oct 2008 14:27] Kristofer Pettersson
http://bugs.mysql.com/bug.php?id=29507

Heikki points out: "Calvin Sun now looking at this. InnoDB logically should always return 0 because it does not know the exact row count."

=> maybe qc should be invalidated regardless of number of deleted rows.
[31 Oct 2008 14:44] 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/57565

2690 Kristofer Pettersson	2008-10-31
      Bug#40386 Not flushing query cache after truncate
      
      This patch solves a regression which caused TRUNCATE TABLE on an InnoDB table
      to to keep an invalid result set in the query cache.
[31 Oct 2008 15:11] Sergei Golubchik
It's InnoDB bug.
table->file->stats.records can *never* be 0 if the table is not empty.
[1 Nov 2008 16:25] Kristofer Pettersson
According to this approved patch it can: http://lists.mysql.com/commits/52268
[1 Nov 2008 17:09] Kristofer Pettersson
Sergei: Could you clarify if you mean "can never be zero" or "should never be zero"? If I read the comments to bug-patch 29507 I can read:
"Row count was approximate for TRUNCATE TABLE in the past.
TRUNCATE TABLE returns a row count of 0 from now on (once the patch for this bug is committed). Row count is approximate for every operation - I am not sure about this. Definitely not true for SELECT COUNT(*) :-), but InnoDB does not know the exact number of rows in a table (unless it does full scan)."

Seeing that we now have this special execution branch in the info-method, perhaps a better solution is to add a query_cache_invalidate call in the SQLCOM_TRUNCATE path after mysql_delete call and leave mysql_delete alone. After all, if we truncate the table there won't be many rows to invalidate in the query cache if we happen to do this one too many times.
[1 Nov 2008 18:03] Sergei Golubchik
I mean "not allowed to be zero". This is how the api (and table->file->stats.records in particular) is defined. It is an *exact* muber of records if HA_STATS_RECORDS_IS_EXACT is set. Otherwise it's approximate. But even if it's approximate "equals to zero" property is exact - that is, table->file->stats.records is allowed to be zero if and only if the table is empty.

Unfortunately, InnoDB now breaks the API, which is a bug.
[1 Nov 2008 18:05] Sergei Golubchik
And this bug (truncate and query cache) is a direct consequence of InnoDB breaking the API.
[3 Nov 2008 8:51] 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/57667

2690 Kristofer Pettersson	2008-11-03
      Bug#40386 Not flushing query cache after truncate
            
      This patch solves a regression which caused TRUNCATE TABLE on an InnoDB table
      to to keep an invalid result set in the query cache.
[3 Nov 2008 13:05] Kristofer Pettersson
Patch pending development in Bug#29507.
[4 Nov 2008 21:13] Sergei Golubchik
The bug is caused by a wrong patch (workaround for a server bug #29507) in InnoDB code. It should be reverted.

It won't cause bug#29507 to reappear, as that bug is now fixed properly in the server.
[6 Nov 2008 14:17] 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:31] 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.
[12 Nov 2008 16:41] Heikki Tuuri
Assigning the InnoDB part to Vasil: InnoDB should return 1 as the number of rows from TRUNCATE. The value 0 can confuse MySQL. InnoDB does not know the exact number of rows.
[15 Jan 2009 20:19] 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/63395

2735 MySQL Build Team	2009-01-15
      Cherry-pick fix for bug#40386, "Not flushing query cache after truncate", out of 5.1-bugteam branch.
      
      This fix is r3114 from innodb-5.1-ss3603.
[16 Jan 2009 17:24] Trudy Pelzer
Pushed to 5.1.31 release clone:
[10:26] <kent> trudy: I have it in the release clone, "Cherry-pick fix for bug#40386, "Not flushing query cache after truncate", out of 5.1-bugteam branch."
[16 Jan 2009 20:21] Paul DuBois
Noted in 5.1.31 changelog.

TRUNCATE TABLE for an InnoDB table did not flush cached queries for
the table.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:23] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090119094551-lkht295hv3nn81yt) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:01] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 15:05] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:07] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[27 Jan 2009 3:29] Roel Van de Paar
Please note that:

o (As also seen in the original test case) the ON or OFF status of the query cache does *not* affect the output returned after a truncate.
o A size setting of 0 (while ON) for the query cache *does* affect the truncate. (This can be used a temporary workaround)

Question: why is a cached result being returned even while the query cache is OFF?

Testcase:

mysql>set global query_cache_type = 0; /* Turn query cache off */
Query OK, 0 rows affected (0.00 sec)

14:09 [world] mysql>show global variables like 'query%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 104857600 | <<<
| query_cache_type             | OFF       | <<<
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+
7 rows in set (0.00 sec)

mysql>create table test22(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql>insert into test22 values(123456);
Query OK, 1 row affected (0.01 sec)

mysql>select * from test22;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql>truncate table test22;
Query OK, 0 rows affected (0.00 sec)

mysql>select * from test22; /* A result is returned even with the query cache off */
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql>set global query_cache_type = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>create table test25(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql>insert into test25 values(123456);
Query OK, 1 row affected (0.01 sec)

mysql>select * from test25;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql>truncate table test25;
Query OK, 0 rows affected (0.00 sec)

mysql>select * from test25;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql>set global query_cache_size=0;
Query OK, 0 rows affected (0.00 sec)

mysql>show global variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       | <<<
| query_cache_type             | ON      | <<<
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

mysql>create table test26(id integer not null) engine InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql>insert into test26 values(123456);
Query OK, 1 row affected (0.01 sec)

mysql>select * from test26;
+--------+
| id     |
+--------+
| 123456 |
+--------+
1 row in set (0.00 sec)

mysql>truncate table test26;
Query OK, 0 rows affected (0.00 sec)

mysql>select * from test26; /* No result returned with query_cache_size=0 */
Empty set (0.00 sec)
[28 Jan 2009 21:39] Paul DuBois
Setting report to NDI pending push into 6.0.x.
[9 Feb 2009 22:34] Bugs System
Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)
[10 Feb 2009 20:13] Bugs System
Pushed into 6.0.10-alpha (revid:alik@sun.com-20090210194937-s7xshv5l3m1v7wi9) (version source revid:tomas.ulin@sun.com-20090123110300-k3vjhnm1iop8c0h7) (merge vers: 6.0.10-alpha) (pib:6)
[12 Feb 2009 10:06] 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/66011

2814 He Zhenxing	2009-02-12 [merge]
      Auto merge 6.0 -> 6.0-rpl
[13 Feb 2009 20:43] Paul DuBois
Noted in 6.0.10 changelog.
[16 Feb 2009 12:25] Sergey Vojtovich
BUG#41565 was marked as duplicate.
[17 Feb 2009 6:42] Sveta Smirnova
Bug #42720 was marked as duplicate of this one.
[4 Jun 2009 17:36] Valeriy Kravchuk
Bug #45321 was marked as a duplicate of this one.
[5 May 2010 15:07] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 16:52] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:44] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:45] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:10] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:47] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:24] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:12] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)