Bug #40386 Not flushing query cache after truncate

• Submitted: 29 Oct 2008 9:24
• Modified: 19 Jun 2010 18:00
• Reporter: Artur Banul
• Status: Closed
• 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:[e-mail]-20090119095303-uwwvxiibtr38djii) (version source revid:[e-mail]-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:[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 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:[e-mail]-20090119144033-4aylstx5czzz88i5) (version source revid:[e-mail]-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:[e-mail]-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:[e-mail]-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)

[10 Feb 2009 20:13] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090210194937-s7xshv5l3m1v7wi9) (version source revid:[e-mail]-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:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-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:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:16] 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:44] 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: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:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:26] Bugs System

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

[17 Jun 2010 11:47] 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:24] 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:12] 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)