Bug #21959 QUERY CACHE: INSERT ... SELECT wrongly invalidates the table being selected from
Submitted: 31 Aug 2006 23:32 Modified: 9 Nov 2006 17:14
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0/5.1 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[31 Aug 2006 23:32] Konstantin Osipov
Description:
INSERT ... SELECT invalidates not only the table that is being inserted into, but also the one which is used in SELECT.

How to repeat:
mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int); insert into t1 (a) values (1), (2), (3);
Query OK, 0 rows affected (0.02 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.01 sec)

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 1     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     | 
+---------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Don't pass the full table list into query_cache_invalidate3, only the first table.
[1 Sep 2006 0:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11202

ChangeSet@1.2257, 2006-09-01 04:24:29+04:00, kostja@bodhi.local +3 -0
  A fix and a test case for Bug#20045 "cache invalidate crashes when 
  INSERT .. SELECT the query uses a VIEW".
  
  When INSERT ... SELECT used a view in the SELECT
  list that was not inlined, and there was an active transaction, the 
  server could crash in Query_cache::invalidate.
  The fix is to distinguish views from base tables in query cache
  and invalidate them by name.
  
  The following alternative solutions were considered:
  1) Skip views when invalidating tables.
  This would introduce a bug as queries like select * from v1 wouldn't get
  invalidated after after an insert via a view.
  2) Exclude views from query cache altogether. Bad, as we'd like queries 
  that use views (like select * from v1) to be cacheable.
  3) Invalidate only the first table in insert .. select. The fact
  that insert ... select invalidates all used tables is a bug
  and was reported separately (Bug#21959). Besides, fixing only
  insert .. select won't fix the problem as there're also multi-delete
  and multi-update.
  4) Update query cache code to properly handle views. (Implemented in this 
  patch).
[1 Sep 2006 0:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11204

ChangeSet@1.2257, 2006-09-01 04:26:30+04:00, kostja@bodhi.local +3 -0
  A fix and a test case for Bug#20045 "cache invalidate crashes when 
  INSERT .. SELECT the query uses a VIEW".
  
  When INSERT ... SELECT used a view in the SELECT
  list that was not inlined, and there was an active transaction, the 
  server could crash in Query_cache::invalidate.
  The fix is to distinguish views from base tables in query cache
  and invalidate them by name.
  
  The following alternative solutions were considered:
  1) Skip views when invalidating tables.
  This would introduce a bug as queries like select * from v1 wouldn't get
  invalidated after after an insert via a view.
  2) Exclude views from query cache altogether. Bad, as we'd like queries 
  that use views (like select * from v1) to be cacheable.
  3) Invalidate only the first table in insert .. select. The fact
  that insert ... select invalidates all used tables is a bug
  and was reported separately (Bug#21959). Besides, fixing only
  insert .. select won't fix the problem as there're also multi-delete
  and multi-update.
  4) Update query cache code to properly handle views. (Implemented in this 
  patch).
[1 Sep 2006 9:40] Hartmut Holzgraefe
verified on 5.0 and 5.1, 4.1 is not affected though
[9 Nov 2006 17:14] Evgeny Potemkin
Duplicate of bug#20045.