MySQL Bugs: #22364: Inconsistent "matched rows" when executing UPDATE

Bug #22364	Inconsistent "matched rows" when executing UPDATE
Submitted:	14 Sep 2006 19:53	Modified:	2 May 2007 19:32
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.36, 5.0.33, 5.0.24a	OS:	Linux (Linux, x86_64)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
Using Java, the executeUpdate method returns an erronous value in certain circumstances.

Our test case shows that the method call works and behaves correctly, but when a certain quantity of rows affected is reached, the returned value is n-1 (where n is the actual value). In our example, if the amount of rows updated is below 523843, MySQL behaves correctly. Above or equal to 523843, it behaves incorectly/differently.

We used the connector/J (5.0.3) to reproduce the problem but it does not seem to be limited to this driver. We saw similar problems with the SQL client SQLYog.

This problem only seems to happen using the 64 binaries for Linux: we are running MySQL on an Dual Core AMD64. We reproduced with the stock MySQL binaries provided on the web site.

We were not able to reproduce on a 32 bit AMD Linux nor on a 32 bit Intel Windows machines.

How to repeat:
Use the submitted code.

Java code used to reproduce the problem

Attachment: BugTest.java (application/octet-stream, text), 3.75 KiB.

Thank you for a problem report. Can you, please, try to repeat your test with mysql command line client and inform about the results? I want to be sure that Connector/J is not a problem here.

We'll try to reproduce with the mysql client and let you know.

Here's a little bit more about the problem.

Our example uses 2 tables. We realized that by adding an index on one of the two tables, the problem disapears. I'll submit the code that showes this behaviour.

Using the SQLYog client, we cannot reproduce the problem exactly. But if we look at the network traffic using a network sniffer such as Wireshark, we can see that the server sends the correct value in binary form, but incorrect values in plain text. 

I'll submit the connector/J traffic dump vs. the SQLYog traffic dump (Ethereal dumps). Both show the same plain text error (matched rows != changed rows) but they don't have the same hex value in the packet (SQLYog has the correct value 0x0007FE43 and connector/J doesn't 0x0007FE42).

connector/J network traffic dump

Attachment: connector.cap (application/octet-stream, text), 469 bytes.

SQLYob network traffic dump

Attachment: yog.cap (application/octet-stream, text), 468 bytes.

Valeriy,

Please be aware that Connector/J sets the client options flag CLIENT_FOUND_ROWS (because JDBC expects "true" update counts, not optimized ones), so if the server is indeed reporting incorrect text for matched in the message as the user states, then it is an issue somewhere in the server.

At first we thought we were seeing optimized vs. matched rows. But if you look at the example, you'll notice that these two values should be the same. There is no way MySQL can optimize the update since every matched row is affected (going from NULL to NOT NULL).

It is possible that SQLYog is not displaying the same value as connector/J (matched vs. updated). But in both cases (based on the plain text on the network), the server is saying matched != updated.

As soon as we do the test with the mysql client, we'll provide the info...

We reproduced the problem with the mysql command line. Here is the script used:

drop table if exists table_a;
drop table if exists table_b;
create table table_a (id int(10) unsigned default null, a integer, b integer, INDEX idx (a,b));
create table table_b (id int(10) unsigned auto_increment, a integer, b integer, primary key table_a (id));
load data local infile 'bug_data.csv' into table table_a fields terminated by ',' (a,b);
insert into table_b (a,b) select a,b from table_a;
update table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b) set t.id=t2.id;

I'll submit bug_data.csv in a second. Here is the result for the last UPDATE statement:
Query OK, 523843 rows affected (16.50 sec)
Rows matched: 523842  Changed: 523843  Warnings: 0

BTW, if you add an index to table_b, like so:
create table table_b (id int(10) unsigned auto_increment, a integer, b integer, primary key table_a (id), INDEX idx (a,b));

The problem goes away:
Query OK, 523843 rows affected (7.91 sec)
Rows matched: 523843  Changed: 523843  Warnings: 0

Well, apperently I can't upload a file larger than 200k... So forget about the file "bug_data.csv". You can build it yourself since it's simply a two column CSV file with integers in both columns:
0,0
1,1
2,2
3,3
...
523841,523841
523842,523842

Sorry for a delay with this problem report. I was not able to repeat the behaviour described with latest 5.0.32BK on Linux:

openxs@suse:~/dbs/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.32-debug Source distribution

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

mysql> load data infile '/tmp/bug_data.csv' into table table_a fields terminate
d by ',' (a,b);
Query OK, 523843 rows affected (5.13 sec)
Records: 523843  Deleted: 0  Skipped: 0  Warnings: 0

mysql> insert into table_b (a,b) select a,b from table_a;
Query OK, 523843 rows affected (7.71 sec)
Records: 523843  Duplicates: 0  Warnings: 0

mysql> update table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b) set
    -> t.id=t2.id;
Query OK, 523843 rows affected (31.26 sec)
Rows matched: 523843  Changed: 523843  Warnings: 0

mysql> show create table table_b\G
*************************** 1. row ***************************
       Table: table_b
Create Table: CREATE TABLE `table_b` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `a` int(11) default NULL,
  `b` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=523844 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, please, upgrade.

I have the same problem. With an amd64 server. Did you try to repeat the problem on a 64-bit linux machine or on a 64-bit? I don't have the problem on a 32-bit machine.

Where can I get 5.0.32? The latest available version is 5.0.27.

Please, try to repeat with a newr version, 5.0.33. Sources are available at http://dev.mysql.com/downloads/mysql/5.0.html.

I tried the 5.0.33 version which I built from source (since it's the only available options) and I have the same problem.

Here is my build setup:
lletourn@ci12 $ uname -an
Linux ci12 2.6.18.3 #1 SMP Tue Nov 28 12:06:54 EST 2006 x86_64 AMD Opteron(tm) Processor 250 GNU/Linux

lletourn@ci12 $ export CFLAGS="-march=opteron -O2 -pipe"
lletourn@ci12 $ export CXX="gcc"
lletourn@ci12 $ export CHOST="x86_64-pc-linux-gnu"
lletourn@ci12 $ export CXXFLAGS="-march=opteron -O2 -pipe"
lletourn@ci12 $ ./configure --prefix=/home/lletourn/mysql-standard-5.0.33-linux-x86_64 --enable-assembler --with-mysqld-ldflags=-all-static
lletourn@ci12 $ make -j3
lletourn@ci12 $ make install

After this I ran the same steps as mentioned at '[15 Sep 2006 20:30] [ name withheld ]'

and I got:
mysql> update table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b) set
    -> t.id=t2.id;
Query OK, 523843 rows affected (15.92 sec)
Rows matched: 523842  Changed: 523843  Warnings: 0

The problem is still there.

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 submitted a comment on the 18 of January and I'm still waiting for some feedback.

I tried with the build 5.0.33 and the bug is still there.

I've downloaded, compiled and tested the 5.0.33 version and the bug is still reproducible by following the steps previously mentioned.

This time, I've created a bash script that, once executed, will reproduce the bug starting with the MySQL source files. Please use or study the script to reproduce the bug. Also, please use a 64 bit machine to reproduce this bug since it does not seem to be reproducible on a 32 bit architecture.

Here is the environment used to reproduce the bug:
$ uname -a
Linux ziggy 2.6.18-gentoo-r3 #1 SMP Wed Dec 6 21:45:19 EST 2006 x86_64 Intel(R) Core(TM)2 CPU          6400  @ 2.13GHz GenuineIntel GNU/Linux
$ gcc -v
Using built-in specs.
Target: x86_64-pc-linux-gnu
Configured with: /var/tmp/portage/gcc-4.1.1-r3/work/gcc-4.1.1/configure --prefix=/usr --bindir=/usr/x86_64-pc-linux-gnu/gcc-bin/4.1.1 --includedir=/usr/lib/gcc/x86_64-pc-linux-gnu/4.1.1/include --datadir=/usr/share/gcc-data/x86_64-pc-linux-gnu/4.1.1 --mandir=/usr/share/gcc-data/x86_64-pc-linux-gnu/4.1.1/man --infodir=/usr/share/gcc-data/x86_64-pc-linux-gnu/4.1.1/info --with-gxx-include-dir=/usr/lib/gcc/x86_64-pc-linux-gnu/4.1.1/include/g++-v4 --host=x86_64-pc-linux-gnu --build=x86_64-pc-linux-gnu --disable-altivec --disable-nls --with-system-zlib --disable-checking --disable-werror --enable-secureplt --disable-libunwind-exceptions --enable-multilib --disable-libmudflap --disable-libssp --disable-libgcj --enable-languages=c,c++ --enable-shared --enable-threads=posix --enable-__cxa_atexit --enable-clocale=gnu
Thread model: posix
gcc version 4.1.1 (Gentoo 4.1.1-r3)

Script file that can be used to reproduce the bug automagicaly.

Attachment: reproduce_bug.sh (application/x-sh, text), 1.39 KiB.

Verified just as described in your last test case, also - with 5.0.36:

-bash-2.05b$ echo "drop table if exists table_a; drop table if exists table_b;
create table table_a (id int(10) unsigned default null, a integer, b integer, I
NDEX idx (a,b)); create table table_b (id int(10) unsigned auto_increment, a in
teger, b integer, primary key table_a (id)); load data infile '`pwd`/bug_data.c
sv' into table table_a fields terminated by ',' (a,b); insert into table_b (a,b
) select a,b from table_a; update table_a as t join table_b as t2 on (t.a=t2.a
AND t.b=t2.b) set t.id=t2.id;" | ./bin/mysql -u root --socket=/tmp/valery.sock
-vv test
--------------
drop table if exists table_a
--------------

Query OK, 0 rows affected

--------------
drop table if exists table_b
--------------

Query OK, 0 rows affected

--------------
create table table_a (id int(10) unsigned default null, a integer, b integer, IN
DEX idx (a,b))
--------------

Query OK, 0 rows affected

--------------
create table table_b (id int(10) unsigned auto_increment, a integer, b integer,
primary key table_a (id))
--------------

Query OK, 0 rows affected

--------------
load data infile '/users/vkravchuk/mysql-enterprise-gpl-5.0.36-linux-x86_64-glib
c23/bug_data.csv' into table table_a fields terminated by ',' (a,b)
--------------

Query OK, 523843 rows affected
Records: 523843  Deleted: 0  Skipped: 0  Warnings: 0

--------------
insert into table_b (a,b) select a,b from table_a
--------------

Query OK, 523843 rows affected
Records: 523843  Duplicates: 0  Warnings: 0

--------------
update table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b) set t.id=t2.id

--------------

Query OK, 523843 rows affected
Rows matched: 523842  Changed: 523843  Warnings: 0

Bye
-bash-2.05b$ uname -a
Linux hammer.mysql.com 2.4.21-32.EL #1 Fri Apr 15 21:18:42 EDT 2005 x86_64 x86_6
4 x86_64 GNU/Linux

Finally I managed to get these:

Rows matched: 523842  Changed: 523843  Warnings: 0

Not repeatable on ANY 32-bit Linux. 64-bit specific bug. Sorry for a long delay with its processing.

This bug is _not_ 64-bit only, and occurs on a condition when, during a multi-update, an internal temporary HEAP table becomes larger than tmp_table_size, and therefore has to be converted to MyISAM. 

Decreasing the value of tmp_table_size from its default value triggers this condition on 32-bit systems as well.

Here is a short testcase which reproduces the bug on any system:

set @@tmp_table_size=1024;

create table t1 (id int, a int, key idx(a));
create table t2 (id unsigned not null auto_increment primary key, a int);
insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
insert into t2(a) select a from t2; update t2 set a=id; truncate t1; insert into t1 select * from t2; update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
insert into t2(a) select a from t2; update t2 set a=id; truncate t1; insert into t1 select * from t2; update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
insert into t2(a) select a from t2; update t2 set a=id; truncate t1; insert into t1 select * from t2; update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
insert into t2(a) select a from t2; update t2 set a=id; truncate t1; insert into t1 select * from t2; update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;

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/24778

ChangeSet@1.2445, 2007-04-18 18:14:15+04:00, kaa@polly.local +3 -0
  Fix for bug #22364 "Inconsistent "matched rows" when executing UPDATE"
  
  In multi_update::send_data(), the counter of matched rows was not correctly incremented, when during insertion of a new row to a temporay table it had to be converted from HEAP to MyISAM.
  
  This fix changes the logic to increment the counter of matched rows in the following cases:
  1. If the error returned from write_row() is zero.
  2. If the error returned from write_row() is non-zero, is neither HA_ERR_FOUND_DUPP_KEY nor HA_ERR_FOUND_DUPP_UNIQUE, and a call to create_myisam_from_heap() succeeds.

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/25126

ChangeSet@1.2445, 2007-04-23 18:22:33+04:00, kaa@polly.local +3 -0
  Fix for bug #22364 "Inconsistent "matched rows" when executing UPDATE"
  
  In multi_update::send_data(), the counter of matched rows was not correctly incremented, when during insertion of a new row to a temporay table it had to be converted from HEAP to MyISAM.
  
  This fix changes the logic to increment the counter of matched rows in the following cases:
  1. If the error returned from write_row() is zero.
  2. If the error returned from write_row() is non-zero, is neither HA_ERR_FOUND_DUPP_KEY nor HA_ERR_FOUND_DUPP_UNIQUE, and a call to create_myisam_from_heap() succeeds.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.18 changelogs.

A multiple-table UPDATE could return an incorrect rows-matched value
if, during insertion of rows into a temporary table, the table had to
be converted from a MEMORY table to a MyISAM table.