MySQL Bugs: #49634: Cached queries do not clear warnings

Bug #49634	Cached queries do not clear warnings
Submitted:	11 Dec 2009 22:14	Modified:	19 Jun 2013 17:44
Reporter:	Daniel Nichter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:	5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	query cache, warnings

Description:
If there are warnings in the message list, a successful SELECT from the query cache will not clear those warnings.  The manual on SHOW WARNINGS states that:

It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Strictly speaking, a SELECT returned from the query cache does not use a table, but since the query cache operates transparently (i.e. user does not know if query is or is not from the cache), cached queries should affect the message list in the same ways as non-cached queries.

How to repeat:
-- Create any minimal table.

mysql> show create table test.t;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

-- Cache a successful SELECT:

mysql> select * from t where i = 1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.01 sec)

-- Execute any query that causes any kind of warning:

mysql> select broken;
ERROR 1054 (42S22): Unknown column 'broken' in 'field list'
mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Error | 1054 | Unknown column 'broken' in 'field list' | 
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- Everything ok so far.

mysql> select * from t where i = 1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

-- One would expect that query to have cleared the warnings but...

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Error | 1054 | Unknown column 'broken' in 'field list' | 
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- warning from the broken query is still there.
-- And the warning stays there...

mysql> select * from t where i = 1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Error | 1054 | Unknown column 'broken' in 'field list' | 
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- To prove its only because the successful SELECT is from the query
-- cache, use SQL_NO_CACHE to bypass the query cache:

mysql> select SQL_NO_CACHE * from t where i = 1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

Suggested fix:
I would like that the fix makes cached queries affect the message list in the same ways as non-cached queries.  Alternatively, the documentation could be updated to warn of this "bug" and that using SQL_NO_CACHE works around it.

Verified just as described with recent 5.1.43 from bzr on Mac OS X.

> I would like that the fix makes cached queries affect the message
> list in the same ways as non-cached queries.

Why should we change a documented behavior and slow down the query cache hit path?  Also, whether a particular statement generated messages is informed. The message list is not associated to any particular statement.

> [..] cached queries should affect the message list in the same ways as non-cached queries.

One could say so, but truth is that queries that generate messages are simply not cached. I'm not sure we should make the message list clearance rule more complicate and strange than it already is. At least, not without a compelling argument.

> Why should we change a documented behavior and slow down the query cache hit path?

It's not documented, at least not specifically.  That is why I made a second suggestion: "Alternatively, the documentation could be updated ..."

> One could say so, but truth is that queries that generate messages are simply not cached.

This is about queries that do not generate messages and are cached.

> I'm not sure we should make the message list clearance rule more complicate and strange
> than it already is. At least, not without a compelling argument.

My central argument was: "since the query cache operates transparently (i.e. user does not know if query is or is not from the cache), cached queries should affect the message list in the same ways as non-cached queries."

I think the fact that the query cache "operates transparently" makes either fixing this or documenting this clearly compelling.  Otherwise, the query cache becomes more complicated because users have to take whether a query is or is not from cache into consideration under certain circumstances, e.g., this circumstance.  But since the query cache is transparent, that amounts to asking users to see what they're supposed to be blind to.

Furthermore, I think it's fair to ask if there are other MySQL features that depend on query properties (e.g. does the query access a table) that would break in a similar fashion due to these properties not being (re)exposed when the query is taken from cache?

I do NOT see this behavior (not clearings warnings) explicitly documented anywhere in the manual. So, this is a bug either in the code or in documentation. Also workaround is not always possible, as one has to modify query to be sure that correct warnings are always got, with query cache or without.

> [..] query properties (e.g. does the query access a table) that would break
> in a similar fashion due to these properties not being (re)exposed when the
> query is taken from cache?

Sure. Cached queries won't reproduce all side effects of the original statement (eg quite a few counters won't be increased). What is tracked is changes that might affect the result.

There is a further subtlety to this bug: a cached query *does* clear the message list the first time that it is taken from the cache but not the 2nd or subsequent times that is taken from the cache.  Here are my results:

Let QUERIES =
  select * from t limit 1;
  select * from t where name='jane';
  select * from t limit 1;
  select broken;
  select * from t limit 1;
  select * from t where name='bob';
  show warnings;

Reset everything, reload tables, etc.--start fresh.  Before any queries are executed, we have:

| Qcache_hits             | 202    | 
| Qcache_inserts          | 144    | 
| Qcache_lowmem_prunes    | 0      | 
| Qcache_not_cached       | 649    | 
| Qcache_queries_in_cache | 0      | 
| Qcache_total_blocks     | 1      | 
| Com_select    | 1     | 

Now, execute QUERIES and recheck the stats:

| Qcache_hits             | 204    | 
| Qcache_inserts          | 147    | 
| Qcache_lowmem_prunes    | 0      | 
| Qcache_not_cached       | 652    | 
| Qcache_queries_in_cache | 3      | 
| Qcache_total_blocks     | 8      | 
| Com_select    | 5     | 

This bug is *not* reproduced, i.e., the final 'show warnings' does *not* show a warning for "select * from t where name='bob'"; this is correct/expected.  Comparing the stats before and after, everything is correct (i.e., there were 2 qc hits corresponding to the 2nd and 3rd executions of "select * from t limit 1;").

Execute QUERIES again:

| Qcache_hits             | 209    | 
| Qcache_inserts          | 147    | 
| Qcache_lowmem_prunes    | 0      | 
| Qcache_not_cached       | 655    | 
| Qcache_queries_in_cache | 3      | 
| Qcache_total_blocks     | 8      | 
| Com_select    | 6     | 

This bug *is* reproduced now.  The stats are still correct so nothing else strange seems to be happening.

So the evidence suggests that MySQL treats differently the first usage of a cached query from its second and subsequent usages.

Sorry, let me retract my previous comment as it is incorrect.  The reason why the warning does not show up after the first execution is *not* related to the cached query being used the 1st or 2nd time, it is because the query before "show warnings", which is "select * from t where name='bob'", clears the warning because it counts as regular, non-cached SELECT.

I view it as an artifact of a broader design decision: 
when a query is returned from the query cache, warning count in the result set metadata is 0, regardless of the actual warning count. 
SERVER_STATUS_IN_TRANS is irrelevant, as well as any other connection state flag. 
I would hesitate to fix it before we change the query cache to stop cache the terminating EOF packet.

http://bugs.mysql.com/bug.php?id=64626 marked as duplicate of this one.

Noted in 5.7.2 changelog.

Successful queries served from the query cache did not clear
warnings.