| Bug #27744 | command "insert .. on duplicate key update" does not update and/or AFR is wrong | ||
|---|---|---|---|
| Submitted: | 11 Apr 2007 1:41 | Modified: | 12 Apr 2007 11:58 |
| Reporter: | Tomas Telensky | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.37-log | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[11 Apr 2007 2:06]
MySQL Verification Team
Thank you for the bug report. Could you please try with latest released
version (I was not able to repeat with current source tree). Thanks in
advance.
mysql> insert into a
-> (a, b)
-> values ('a', 2)
-> on duplicate key update b = 2
->
-> ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 2 |
+---+------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.40-debug |
+--------------+
1 row in set (0.00 sec)
[11 Apr 2007 15:15]
Tomas Telensky
Sorry, it was version 5.0.37-log, which is the latest released stable version. Unfortunatelly, I cannot try development versions, since I'm dependent on what webhosting provides.
[11 Apr 2007 20:21]
MySQL Verification Team
Thank you for the feedback. Please provide the feedback when you will able to test with the next released version. Thanks in advance.
[11 Apr 2007 22:50]
MySQL Verification Team
Now I tested with 5.0.37 on Windows and also I was not able to
repeat, are you sure you have tested against 5.0.37 server?
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> CREATE TABLE a (
-> a varchar(30) primary key,
-> b int
-> )
-> ;
Query OK, 0 rows affected (0.50 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 1)
-> on duplicate key update b = 1
->
-> ;
Query OK, 1 row affected (0.07 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 2)
-> on duplicate key update b = 2
->
-> ;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 2 |
+---+------+
1 row in set (0.00 sec)
mysql>
However the behavior of rows affected on Linux with current
source is 2 rows not 0 how above:
[miguel@light 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.40-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE a (
-> a varchar(30) primary key,
-> b int
-> )
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 1)
-> on duplicate key update b = 1
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 2)
-> on duplicate key update b = 2
-> ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 2 |
+---+------+
1 row in set (0.00 sec)
mysql>
and yes I meant to wait for the future release. Thanks in advance.
[11 Apr 2007 23:26]
Tomas Telensky
1) yes, the version is 5.0.37-log, running on linux. Could you try on linux? 2) but on the 5.0.37 windows version you see the other part of buggy behaviour - the affected rows is zero, even if the row is updated! This bug is actually two bugs: 1) the command does _not_ update - this is more fatal and more hard to reproduce! If I omit the "step 4) select * from a", the update is done! So this behaviour is not always present, and I think it would be kind of a phantom, because the condition of doing "select * from a" looks really odd. So it probably could be hard to reproduce in other builds, even if the bug was not fixed. 2) the command does update, but the _affected rows is zero_! The affected rows should be 2! This is easier to reproduce, and it happens even if the step 4) is omitted. And the new fact I realized: It doesn't depend on primary key to be varchar! It suffices if the table contains _any_ field which is varchar or text. You can substitute the step 1) with the following command: CREATE TABLE a ( a int primary key, b int, c text ) or CREATE TABLE a ( a int primary key, b int, c varchar(3) ) I changed the title of the bug accordingly.
[12 Apr 2007 0:00]
MySQL Verification Team
Thank you for the feedback. I tested on Windows 5.0.37 because it is
that version I have available just now, however notice below that the
Windows source version behaves how my FC 6.0 source version:
Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.
c:\>cd build
c:\build>cd 5.0
c:\build\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.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE a (
-> a varchar(30) primary key,
-> b int
-> )
-> ;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 1)
-> on duplicate key update b = 1
->
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> insert into a
-> (a, b)
-> values ('a', 2)
-> on duplicate key update b = 2
-> ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from a
-> ;
+---+------+
| a | b |
+---+------+
| a | 2 |
+---+------+
1 row in set (0.00 sec)
mysql>
That is the reason why I am asking you to wait for the future next release.
However I will ask to my co-worker to try too. Thanks.
[12 Apr 2007 11:58]
Sveta Smirnova
Thank you for the report and the comments. I was able to repeat it with 5.0.36 binaries and wasn't with current sources. It can be duplicate of bug #27210 which was alive only in MySQL 5.0.36-5.0.37 and has been fixed in MySQL 5.0.38
Description: When I use "insert .. on duplicate key update" and the "duplicate key" case occurs, the update is not done! It works fine in mysql 5.0.22. Here is the sequence of commands (see below for php code): 1) drop table if exists a 2) CREATE TABLE a ( a varchar(30) primary key, b int ) note: if the field is char(30) instead, it works! 3) insert into a (a, b) values ('a', 1) on duplicate key update b = 1 4) select * from a note: if I don't do this select in the php code, the update is done! But the value of affected rows is 0 - another bug?! 5) insert into a (a, b) values ('a', 2) on duplicate key update b = 2 you see that affected rows is 0!! 6) select * from a you see that b is still 1 - table was not updated!! I reported this bug because I don't see the bug here in database. It seems to be quite a fatal one, at least for me. Unfortunatelly I don't have direct access to the mysql console, since it is webhosting - I can use only PHP to access mysql. But I believe this would be the same. How to repeat: This is my simplified testcase (php code): <?php /* this is a bug !!! */ mysql_connect(NULL, "user", "xxx"); mysql_select_db("xxx"); mysql_query("drop table if exists a"); mysql_query(" CREATE TABLE a ( a varchar(30) primary key, b int ) "); mysql_query("insert into a (a, b) values ('a', 1) on duplicate key update b = 1 "); $afr = mysql_affected_rows(); echo "Affected rows: $afr<br>\n"; state(); // even this is important!!! mysql_query("insert into a (a, b) values ('a', 2) on duplicate key update b = 2 "); $afr = mysql_affected_rows(); echo "Affected rows: $afr<br>\n"; state(); if ($afr == 0) echo "This is wrong !!!<br>\n"; function state() { $res = mysql_query("select * from a "); while ($line = mysql_fetch_assoc($res)) echo "a is {$line['a']}, b is {$line['b']}<br>"; } ?>