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 22:59]
Jan Steemann
[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.