| Bug #73604 | Query cache returns stale value after populating timezone tables | ||
|---|---|---|---|
| Submitted: | 15 Aug 2014 15:16 | Modified: | 18 Sep 2014 11:17 |
| Reporter: | Chris Wilson | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S2 (Serious) |
| Version: | 5.5.38-0ubuntu0.12.04.1 | OS: | Linux (Ubuntu 12.04.5 LTS) |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 2014 11:17]
MySQL Verification Team
Thank you for the report.
I'm not able to repeat with latest builds.. Could you please provide exact repeatable test case, conf file used, is this Oracle official builds?
// Session 1
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.40 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.40 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> show global variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
mysql>
mysql> USE mysql;
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> SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user;
+----------------------------------------+
| CONVERT_TZ('2014-01-01', 'UTC', 'UTC') |
+----------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+----------------------------------------+
6 rows in set (0.00 sec)
// // Session 2
[ushastry@cluster-repo mysql-5.5.40]$ bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |bin/mysql -u root -p mysql
Enter password:
// session 1
mysql> USE mysql;
Database changed
mysql> SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user;
+----------------------------------------+
| CONVERT_TZ('2014-01-01', 'UTC', 'UTC') |
+----------------------------------------+
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
+----------------------------------------+
6 rows in set (0.00 sec)
// 5.6.21
// session 1
[ushastry@cluster-repo mysql-advanced-5.6.21]$ bin/mysql -u root -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show global variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
mysql> USE mysql;
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> SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user;
+----------------------------------------+
| CONVERT_TZ('2014-01-01', 'UTC', 'UTC') |
+----------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+----------------------------------------+
6 rows in set (0.00 sec)
// session 2
[ushastry@cluster-repo mysql-advanced-5.6.21]$ bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |bin/mysql -u root -p mysql
Enter password:
// session 1
mysql> SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user;
+----------------------------------------+
| CONVERT_TZ('2014-01-01', 'UTC', 'UTC') |
+----------------------------------------+
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
| 2014-01-01 00:00:00 |
+----------------------------------------+
6 rows in set (0.01 sec)
[19 Sep 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: The documentation says that "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed." But this is not true in at least one case: loading data into the timezone tables does not invalidate cached queries using CONVERT_TZ. The following query was returning NULL instead of a valid datetime: mysql> SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`aldryn_blog_post`.`publication_start`, 'UTC', 'Europe/London'), '%Y-01-01 00:00: 00') AS DATETIME) FROM `aldryn_blog_post` WHERE (`aldryn_blog_post`.`language` IS NULL OR `aldryn_blog_post`.`language` = 'en' ) ORD ER BY 1 DESC; +--------------------------------------------------------------------------------------------------------------------------------+ | CAST(DATE_FORMAT(CONVERT_TZ(`aldryn_blog_post`.`publication_start`, 'UTC', 'Europe/London'), '%Y-01-01 00:00:00') AS DATETIME) | +--------------------------------------------------------------------------------------------------------------------------------+ | NULL | +--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) So I loaded the timezone tables, but the problem did not go away; the statement gave the same wrong result every time. Trying to debug it, I discovered that changing the query in any way, even the case of a single letter, made it work: mysql> sELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`aldryn_blog_post`.`publication_start`, 'UTC', 'Europe/London'), '%Y-01-01 00:00: 00') AS DATETIME) FROM `aldryn_blog_post` WHERE (`aldryn_blog_post`.`language` IS NULL OR `aldryn_blog_post`.`language` = 'en' ) ORD ER BY 1 DESC; +--------------------------------------------------------------------------------------------------------------------------------+ | CAST(DATE_FORMAT(CONVERT_TZ(`aldryn_blog_post`.`publication_start`, 'UTC', 'Europe/London'), '%Y-01-01 00:00:00') AS DATETIME) | +--------------------------------------------------------------------------------------------------------------------------------+ | 2014-01-01 00:00:00 | +--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) I realised at this point that the query cache was being used to return the incorrect cached results of a previous query, from before the timezone tables were loaded, when the statement matched exactly. So it seems that modifying the timezone tables does not correctly flush the cache of any statement that depends on them, for example those using the CONVERT_TZ function. How to repeat: I haven't tested this (I tried but the statement is not cached so it doesn't reproduce the issue), but something like this should do it: USE mysql; SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user; -- returns NULL, because timezone tables are empty -- now execute: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user; -- returns NULL, because the previous wrong result is cached FLUSH TABLES; SELECT SQL_CACHE CONVERT_TZ('2014-01-01', 'UTC', 'UTC') from user; -- should return '2014-01-01 00:00:00' as expected Suggested fix: Invalidate statements using CONVERT_TZ (or if necessary the whole query cache) whenever the timezone tables in the mysql database are modified.