MySQL Bugs: #28638: INSERT ... ON DUPLICATE KEY UPDATE broken

Bug #28638	INSERT ... ON DUPLICATE KEY UPDATE broken
Submitted:	23 May 2007 23:11	Modified:	26 Jul 2007 22:03
Reporter:	Jordi Boggiano	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.37-community-nt	OS:	Windows (Windows Vista, 32bit)
Assigned to:		CPU Architecture:	Any
Tags:	cache, UPDATE

Description:
If I use the INSERT ... ON DUPLICATE KEY UPDATE syntax, the server says that no row has been updated, if I select it it is indeed not updated but if I restart the server then the data shows up updated as it should have all along...

How to repeat:
-- Setting it up

CREATE DATABASE `bugcheck` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `bugcheck`;

CREATE TABLE `test` (
  `a` varchar(5) NOT NULL,
  `b` varchar(5) NOT NULL,
  `c` varchar(5) NOT NULL,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test` (`a`, `b`, `c`) VALUES 
('a', 'b', 'c');

-- test case 1: UPDATE works as expected

mysql> UPDATE bugcheck.test SET c="d" WHERE a="a" AND b="b";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM bugcheck.test;
+---+---+---+
| a | b | c |
+---+---+---+
| a | b | d |
+---+---+---+
1 row in set (0.00 sec)

-- test case 2: INSERT ... ON DUPLICATE KEY UPDATE does not work

mysql> INSERT INTO bugcheck.test (a,b,c) VALUES ("a","b","c") ON DUPLICATE KEY UPDATE c="c";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM bugcheck.test;
+---+---+---+
| a | b | c |
+---+---+---+
| a | b | d |
+---+---+---+
1 row in set (0.00 sec)

-- restarting the server shows that it in fact did work

mysql> SELECT * FROM bugcheck.test;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+---+---+---+
| a | b | c |
+---+---+---+
| a | b | c |
+---+---+---+
1 row in set (0.01 sec)

Suggested fix:
No idea sorry.

Thank you for the bug report. Could you please provide your my.ini file
I was not able to repeat:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL)

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

mysql> CREATE DATABASE `bugcheck` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.05 sec)

mysql> USE `bugcheck`;
Database changed
mysql>
mysql> CREATE TABLE `test` (
    ->   `a` varchar(5) NOT NULL,
    ->   `b` varchar(5) NOT NULL,
    ->   `c` varchar(5) NOT NULL,
    ->   PRIMARY KEY  (`a`,`b`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> INSERT INTO `test` (`a`, `b`, `c`) VALUES
    -> ('a', 'b', 'c');
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE bugcheck.test SET c="d" WHERE a="a" AND b="b";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM bugcheck.test;
+---+---+---+
| a | b | c |
+---+---+---+
| a | b | d |
+---+---+---+
1 row in set (0.00 sec)

mysql> INSERT INTO bugcheck.test (a,b,c) VALUES ("a","b","c") ON DUPLICATE KEY
    -> UPDATE c="c";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM bugcheck.test;
+---+---+---+
| a | b | c |
+---+---+---+
| a | b | c |
+---+---+---+
1 row in set (0.00 sec)

mysql>

mysql config file

Attachment: my.ini (application/octet-stream, text), 5.02 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am able to duplicate the bug as described. 

5.0.37-standard-log, FC4

Turning off the query cache (query_cache_type = 0) resolved the error, but it not a practical solution for the entire server.  As a temporary measure, I have used SET SESSION query_cache_type = 0 to disable the query cache before the query is run, and enabling it again afterwards.

All reporters:
Could you please test against the latest released server. Thanks in
advance.

Important additional information:

I was able to duplicate the problem using a varchar primary (unique) key, but was NOT able to duplicate using an integer key.

Also verified in 5.0.37 OS X Intel.  I am currently in the process of upgrading to determine if 5.0.41 fixes the problem.  (I can only upgrade the OS X machine.  The sysadmin of the FC4 server is unwilling to upgrade due to maintaining parity with our production environment.)

I can no longer duplicate the bug in 5.0.41 OS X Intel.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".