Description:
Queries that use the CONVERT_TZ() function are not inserted into the query cache, with the obvious consequence that they also cannot be read from the query cache later. This is not an issue of the query cache statistics being wrong as for slow queries the time difference is quite obvious.
There is nothing special about the data in the attached dump, it was an arbitrary construction to demonstrate the bug. The actual data with which I spotted the bug is much too large to attach here. I have not found any times where a query that uses CONVERT_TZ() is cached. Furthermore, the CONVERT_TZ() need not be in the column portion of the SELECT, I have also seen this issue where
How to repeat:
Import the attached dump (which assumes the existence of an empty `testtz` database) and restart mysqld to reset the query cache, then login to the mysql monitor and follow the mysql monitor session as detailed below.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE `testtz`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16768408 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Questions | 6 |
+-------------------------+----------+
9 rows in set (0.00 sec)
mysql> SELECT CONVERT_TZ(`date1`, 'GMT', 'EST') FROM `dates` WHERE `date2` = '2006-11-14 12:46:29';
+-----------------------------------+
| CONVERT_TZ(`date1`, 'GMT', 'EST') |
+-----------------------------------+
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
| 2006-11-14 07:46:29 |
+-----------------------------------+
7 rows in set (0.01 sec)
mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16768408 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Questions | 8 |
+-------------------------+----------+
9 rows in set (0.00 sec)
mysql> SELECT `date1` FROM `dates` WHERE `date2` = '2006-11-14 12:46:29';
+---------------------+
| date1 |
+---------------------+
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
| 2006-11-14 12:46:29 |
+---------------------+
7 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16766872 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
| Questions | 10 |
+-------------------------+----------+
9 rows in set (0.00 sec)
Note that the query is only cached when it does not use CONVERT_TZ().
Suggested fix:
Queries that use CONVERT_TZ() should be cached, provided they do not use NOW(), RAND(), etc as described in <http://mysql.org/doc/refman/5.0/en/query-cache-how.html>.
Alternatively, if for some reason, queries using CONVERT_TZ() cannot be cached, CONVERT_TZ() should be added to the list of functions in the above link.
