Details
Minor
Description
I've confirmed this bug with mariadb 5.1.49 from our delta rep for lucid (5.1.49-mariadb82) it is not present on mysql 5.1.41 on lucid (5.1.41-3ubuntu12.6)
Can be replicated as follows
MariaDB [(none)]> CREATE DATABASE `foo.bar`; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use foo.bar; Database changed MariaDB [foo.bar]> CREATE TABLE moocow (a int); Query OK, 0 rows affected (0.01 sec) MariaDB [foo.bar]> SHOW STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 268415928 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 15311 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec) MariaDB [foo.bar]> INSERT INTO moocow VALUES (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [foo.bar]> SELECT * FROM moocow; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) MariaDB [foo.bar]> SHOW STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 268415928 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 15312 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It is also repeatable on MySQL 5.1 (with innobase build from sources)
Fixing this bug require niticeble overhead for each table in the query during caching or returnung from the cache, when we have uncached only 1% of queries.
To make the impact minimum we have to change handler interface and makes QC storing both variants of the table/db names (i.e. it can't be done as just a bugfix in old version).
It will not be fixed in 5.* , but in 10.0, because fix will be expensive or need intrusive changes in QC which could be done only in 10.0
it's not a bug in 10.0.2!?
reset query cache;
create database `foo.bar`;
use `foo.bar`;
CREATE TABLE moocow (a int);
INSERT INTO moocow VALUES (1), (2), (3);
SELECT SQL_CACHE * FROM moocow;
select * from information_schema.QUERY_CACHE_QUERIES;
ENTRY_POSITION_IN_CACHE|STATEMENT_SCHEMA|STATEMENT_TEXT|QUERY_ROWS|QUERY_HITS|QUERY_HITS_TOTAL_TIME_US|QUERY_LAST_HIT_TIME|SELECT_EXPEND_TIME_US|SELECT_LOCK_TIME_US|SELECT_ROWS_READ|TABLES|QUERY_INSERT_TIME|RESULT_LENGTH|RESULT_BLOCKS_COUNT|RESULT_BLOCKS_SIZE|RESULT_BLOCKS_SIZE_USED|RESULT_TABLES_TYPE|RESULT_FOUND_ROWS|FLAGS_CLIENT_LONG_FLAG|FLAGS_CLIENT_PROTOCOL_41|FLAGS_PROTOCOL_TYPE|FLAGS_MORE_RESULTS_EXISTS|FLAGS_IN_TRANS|FLAGS_AUTOCOMMIT|FLAGS_PKT_NR|FLAGS_CHARACTER_SET_CLIENT|FLAGS_CHARACTER_SET_RESULTS|FLAGS_COLLATION_CONNECTION|FLAGS_LIMIT|FLAGS_TIME_ZONE|FLAGS_SQL_MODE|FLAGS_MAX_SORT_LENGTH|FLAGS_GROUP_CONCAT_MAX_LEN|FLAGS_DIV_PRECISION_INCREMENT|FLAGS_DEFAULT_WEEK_FORMAT|FLAGS_LC_TIME_NAMES
0|foo.bar|SELECT * FROM moocow|3|0|0|0.000000|208|41|3|`foo.bar`.`moocow`|1370765538.000000|88|1|512|152|0|3|1|1|0|0|0|1|1|utf8|utf8|utf8_general_ci|-1|SYSTEM|PIPES_AS_CONCAT,ALLOW_INVALID_DATES|1024|1024|5|0|en_US
fixed in upcoming 10.0. (now in 10.0-merge)
Re: Query cache is not used for tables or databases with dots in their names
So this is effectively a regression then, since an older (upstream) codebase does not exhibit the problem.
Of course it's utterly nasty to have table names with dots in them, but since it's allowed...