| Bug #31892 | ON DUPLICATE KEY UPDATE does not refresh query cache. | ||
|---|---|---|---|
| Submitted: | 26 Oct 2007 21:48 | Modified: | 26 Oct 2007 22:47 |
| Reporter: | Devon Yates | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | 5.0.37-community-nt-log | OS: | Windows (Windows 2000 5.00.2195 SP4) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cache, ON DUPLICATE KEY UPDATE | ||
[26 Oct 2007 22:27]
MySQL Verification Team
Thank you for the bug report. Could you please upgrade to latest released
version. Thanks in advance.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> show variables like "%query_cache%";
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
mysql> CREATE TABLE test.cache_test (
-> id int(6) NOT NULL auto_increment,
-> char_field char(50) NOT NULL default '',
-> varchar_field varchar(50) NOT NULL default '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO test.cache_test
-> (id,char_field,varchar_field) VALUES(1,"same","same")
-> ON DUPLICATE KEY UPDATE
-> id=VALUES(id),char_field=VALUES(char_field),
-> varchar_field=VALUES(varchar_field);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.cache_test where id=1;
+----+------------+---------------+
| id | char_field | varchar_field |
+----+------------+---------------+
| 1 | same | same |
+----+------------+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test.cache_test
-> (id,char_field,varchar_field) VALUES(1,"different","same")
-> ON DUPLICATE KEY UPDATE
-> id=VALUES(id),`char_field`=VALUES(char_field),
-> varchar_field=VALUES(varchar_field);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test.cache_test where id=1;
+----+------------+---------------+
| id | char_field | varchar_field |
+----+------------+---------------+
| 1 | different | same |
+----+------------+---------------+
1 row in set (0.00 sec)
mysql>
[26 Oct 2007 22:47]
Devon Yates
O.k., you got me...I tested it on the latest release, and it does appear to be fixed. Thanks for the prompt response. -Devon Yates
Description: I have found a situation where a table is updated, but the cache does not appear to refresh. I have put together a simple example which appears to trigger the error. In this example, I insert a set of fields. I then reinsert using the "on duplicate key update" functionality, and change one of the values. When using the cache, I can not see the change, but when disabling the cache, I can see it. Strangely, if I remove the column varchar_field, the error does not occur. Additionally, I have run this test case on version 5.0.27, and it also does not occur. Hope this helps, Devon Yates How to repeat: mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.0.37-community-nt-log | +-------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE test.cache_test ( -> id int(6) NOT NULL auto_increment, -> char_field char(50) NOT NULL default '', -> varchar_field varchar(50) NOT NULL default '', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO test.cache_test -> (id,char_field,varchar_field) VALUES(1,"same","same") -> ON DUPLICATE KEY UPDATE -> id=VALUES(id),char_field=VALUES(char_field), -> varchar_field=VALUES(varchar_field); Query OK, 1 row affected (0.02 sec) mysql> select * from test.cache_test where id=1; +----+------------+---------------+ | id | char_field | varchar_field | +----+------------+---------------+ | 1 | same | same | +----+------------+---------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test.cache_test -> (id,char_field,varchar_field) VALUES(1,"different","same") -> ON DUPLICATE KEY UPDATE -> id=VALUES(id),`char_field`=VALUES(char_field), -> varchar_field=VALUES(varchar_field); Query OK, 0 rows affected (0.00 sec) !! HERE IS WHERE I EXPECT TO SEE char_field=different !!! mysql> select * from test.cache_test where id=1; +----+------------+---------------+ | id | char_field | varchar_field | +----+------------+---------------+ | 1 | same | same | +----+------------+---------------+ 1 row in set (0.00 sec) mysql> select SQL_NO_CACHE * from test.cache_test where id=1; +----+------------+---------------+ | id | char_field | varchar_field | +----+------------+---------------+ | 1 | different | same | +----+------------+---------------+ 1 row in set (0.00 sec) mysql> quit;