Bug #29197 Query cache and warnings
Submitted: 19 Jun 2007 5:23 Modified: 1 Apr 2008 11:26
Reporter: Lachezar Balev Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.1.17, 5.0 BK, 4.1 BK OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Triage: D3 (Medium)

[19 Jun 2007 5:23] Lachezar Balev
Description:
I think that there is one more case when a query does not get cached which is not described in the MySQL docs. The query is not cached when the server issues a warning.

How to repeat:
mysql> create table t1 (id int(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 18    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Now issue this query (it produces a warning) several times and explore the status variables:
mysql> select 'hi' as id from t1 group by id;
mysql> select 'hi' as id from t1 group by id;

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 18    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 5     |
+---------------+-------+
1 row in set (0.00 sec)

No cache hits are observed.

Suggested fix:
I do not think that this behaviour is incorrect. But I think it should be documented.
[19 Jun 2007 6:40] Sveta Smirnova
Thank you for the report.

Verified as described.
[18 Jul 2007 22:19] Oleksandr Byelkin
It looks like table is optimized out in this query (can be checked with help of EXPLAIN) and query cache do not caches queries without tables, so is does not look like bug.
[20 Jul 2007 5:29] Lachezar Balev
Oleksandr, this is not exactly a bug, maybe it should be only documented.
The reason is not because the table is optimized. It can be easily checked by adding some more rows in the example given above.
[24 Jul 2007 18:42] Oleksandr Byelkin
Aster some more debugging it looks like I was wrong, the table is not optimized out, but closed after optimization. So QC do not see locked tables and thinks thatthere is no tables in the query. IMHO it is better to fix QC (check all query tables list instead of locked tables) then write tricky documentation.
[24 Jul 2007 21:39] Oleksandr Byelkin
During fixing I found yet another cause why the query do not go to the cache: the query produce warning and QC do not store queries with warnings.

I have no idea (maybe yet) how to make the query of such type without warning...