Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.0, 5.5.28, 5.3.9, 5.2.12, 5.1.62
-
None
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)
|
Attachments
Activity
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
It is also repeatable on MySQL 5.1 (with innobase build from sources)