| Bug #50065 | query cache invalidated in insert...select if no rows inserted | ||
|---|---|---|---|
| Submitted: | 4 Jan 2010 22:59 | Modified: | 29 Jul 2012 23:08 |
| Reporter: | Jan Steemann (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S4 (Feature request) |
| Version: | 5.0.89, 5.1.37, 5.5-mr2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | insert, query cache, regression | ||
[4 Jan 2010 23:04]
Jan Steemann
Issue also present in 5.1.37
[4 Jan 2010 23:06]
Jan Steemann
Issue not present in MySQL 4.1.22 so it seems to have been introduced in either 5.0 or 5.1.
[5 Jan 2010 12:32]
Valeriy Kravchuk
Thank you for the problem report. Verified just as described: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.89-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set query_cache_type=1; Query OK, 0 rows affected (0.08 sec) mysql> show variables like 'query\_cache\_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | query_cache_size | 8388608 | +------------------+---------+ 1 row in set (0.05 sec) mysql> drop table if exists temp; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> create table temp (i int) engine=myisam; Query OK, 0 rows affected (0.19 sec) mysql> insert into temp (i) values (1); Query OK, 1 row affected (0.03 sec) mysql> select sql_cache i from temp; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like 'qcache\_%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8378312 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 32 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> select sql_cache i from temp; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like 'qcache\_%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8378312 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 33 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> insert into temp (i) select 1 from dual where 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select sql_cache i from temp; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like 'qcache\_%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8378312 | | Qcache_hits | 1 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 34 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec)
[5 Jan 2010 12:33]
Valeriy Kravchuk
Test case for copy-paste: set query_cache_type=1; show variables like 'query\_cache\_size'; drop table if exists temp; create table temp (i int) engine=myisam; insert into temp (i) values (1); select sql_cache i from temp; show status like 'qcache\_%'; select sql_cache i from temp; show status like 'qcache\_%'; insert into temp (i) select 1 from dual where 0; select sql_cache i from temp; show status like 'qcache\_%';
[29 Jul 2012 23:08]
Paul DuBois
Noted in 5.7.0 changelog. An INSERT INTO ... SELECT statement that inserted no rows unnecessarily invalidated statements in the query cache that used the target table.
Description: When the query cache is enabled and an INSERT...SELECT query is executed that does not actually insert any rows, the query cache entries for the table mentioned in the INSERT still get invalidated. This is unnecessary because the table is not changed and makes the query cache less effective than necessary. The issue does NOT seem to be present for (i.e. everything works fine for): - REPLACE...SELECT statements that do not modify rows - single or multi table UPDATE statements that do not modify any rows - single or multi table DELETE statements that do not modify any rows How to repeat: # make sure the query cache is active: set query_cache_type=1; # should have some value greater 0 show variables like 'query\_cache\_size'; # prepare a simple test table: drop table if exists temp; create table temp (i int) engine=myisam; insert into temp (i) values (1); # warm up query cache and note qcache_% server status values: select sql_cache i from temp; # first status show status like 'qcache\_%'; # validate that cache works select sql_cache i from temp; # second status, 'qcache_inserts' value should not have changed since the previous select and 'qcache_hits' should have increased by 1 (i.e. cache hit) show status like 'qcache\_%'; # the following query will unnecessarily trigger the query cache invalidation for table temp even if it does not produce any rows and therefore will not modify rows in table temp insert into temp (i) select 1 from dual where 0; # this select cannot use the query cache select sql_cache i from temp; # third status, the 'qcache_hits' value will not have changed but 'qcache_inserts' will have increased by 1 (cache miss). show status like 'qcache\_%'; Ideally, the third select should produce a cache hit as well. Suggested fix: The reason for the issue seems to be in file sql/sql_parse.cc in function mysql_execute(), case SQLCOM_INSERT_SELECT. In line 3373, just before the query cache invalidation code, there is an if condition that does not take into account if any rows in the table have been changed: sql/sql_parse.cc (function mysql_execute): ... 3373 if (first_table->lock_type == TL_WRITE_CONCURRENT_INSERT && 3374 thd->lock) 3375 { 3376 /* INSERT ... SELECT should invalidate only the very first table */ 3377 TABLE_LIST *save_table= first_table->next_local; 3378 first_table->next_local= 0; 3379 query_cache_invalidate3(thd, first_table, 1); 3380 first_table->next_local= save_table; 3381 } ... The if condition should be modified so it takes into account whether any rows in first_table have been changed.