Bug #30256 Query cache not emptied after a INSERT ... ON DUPLICATE KEY UPDATE

• Submitted: 6 Aug 2007 14:27
• Modified: 6 Aug 2007 15:15
• Reporter: André Bieleman
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: affected rows, ON DUPLICATE KEY UPDATE, query cache

[6 Aug 2007 14:27] André Bieleman

Description:
After executing a INSERT ... ON DUPLICATE KEY UPDATE query which hits a duplicate on the PRIMARY index and the update part does update the table,
the query cache isn't emptied. Also note that it notifies with zero affected rows.

When performing a RESET QUERY CACHE or a normal UPDATE afterwards, it is emptied and the updated value appears.

Tested on 5.0.37 (x86), 5.0.41 (x86_64) and 5.0.45 (x86).

A workaround could be disabling query cache or use two seperate query's.

How to repeat:
mysql> CREATE TABLE `test_tbl` (`name` varchar(255) NOT NULL, `number` int(10) unsigned NOT NULL default '0', `time` datetime default NULL, PRIMARY KEY  (`name`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test_tbl` SET `name` = 'test';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `test_tbl`;
+------+--------+------+
| name | number | time |
+------+--------+------+
| test |      0 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO `test_tbl` SET `name` = 'test', `number` = 1 ON DUPLICATE KEY UPDATE `number` = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `test_tbl`;
+------+--------+------+
| name | number | time |
+------+--------+------+
| test |      0 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

mysql> UPDATE `test_tbl` SET `time` = NOW();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_tbl`;
+------+--------+---------------------+
| name | number | time                |
+------+--------+---------------------+
| test |      2 | 2007-08-06 15:57:43 |
+------+--------+---------------------+
1 row in set (0.00 sec)

[6 Aug 2007 15:15] MySQL Verification Team

Thank you for the bug report. I can't repeat with latest source server:

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.48-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test_tbl` (`name` varchar(255) NOT NULL, `number` int(10) unsigned
    -> NOT NULL default '0', `time` datetime default NULL, PRIMARY KEY  (`name`)) ENGINE=MyISAM
    -> DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `test_tbl` SET `name` = 'test';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `test_tbl`;
+------+--------+------+
| name | number | time |
+------+--------+------+
| test |      0 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

mysql>  INSERT INTO `test_tbl` SET `name` = 'test', `number` = 1 ON DUPLICATE KEY UPDATE
    -> `number` = 2;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM `test_tbl`;
+------+--------+------+
| name | number | time |
+------+--------+------+
| test |      2 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

mysql> show variables like "%cache%";
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| binlog_cache_size            | 32768      |
| have_query_cache             | YES        |
| key_cache_age_threshold      | 300        |
| key_cache_block_size         | 1024       |
| key_cache_division_limit     | 100        |
| max_binlog_cache_size        | 4294967295 |
| query_cache_limit            | 1048576    |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 10485760   |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
| table_cache                  | 64         |
| thread_cache_size            | 0          |
+------------------------------+------------+
13 rows in set (0.00 sec)

mysql>