MySQL Bugs: #40386: Not flushing query cache after truncate

Bug #40386	Not flushing query cache after truncate
Submitted:	29 Oct 2008 10:24	Modified:	13 Feb 21:43
Reporter:	Artur Banul
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.1.29-rc, 5.1, 6.0 bzr	OS:	Linux
Assigned to:	Calvin Sun	Target Version:	5.1.31
Tags:	regression, truncate query cache
Triage:	Triaged: D2 (Serious)

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)

Thank you for the report.

Verified as described.

Bug was introduced after version 5.1.26

The QC invalidation does work with MyISAM. The problem occurs when we truncate an InnoDB
table.

InnoDB takes this route:  
  if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
      goto trunc_by_del;

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;
    }
...

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.

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.

It's InnoDB bug.
table->file->stats.records can *never* be 0 if the table is not empty.

According to this approved patch it can: http://lists.mysql.com/commits/52268

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.

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.

And this bug (truncate and query cache) is a direct consequence of InnoDB breaking the
API.

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.

Patch pending development in Bug#29507.

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.

Sergei,

what value InnoDB can return as the row count?

InnoDB does not know the exact count.

Can it return some huge number?

--Heikki

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.

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.

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.

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."

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.

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)

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)

Setting status back to NDI pending merge to 6.0 tree.

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)

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)

Setting report to NDI pending push into 6.0.x.

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)

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)

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

Noted in 6.0.10 changelog.

BUG#41565 was marked as duplicate.

Bug #42720 was marked as duplicate of this one.

Bug #45321 was marked as a duplicate of this one.