Bug #16281 Multi-table update failing to change matched rows
Submitted: 7 Jan 2006 20:06 Modified: 6 Apr 2006 0:32
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.16 OS:Linux (Linux)
Assigned to: Evgeny Potemkin

[7 Jan 2006 20:06] Olaf van der Spek
Description:
I've got a multi-join update that only changes the first matched row, see below.
I'm quite sure this used to work correctly on 4.1.

How to repeat:
mysql> update xwi_players inner join xwi_serials using (sid) set pass = 'test' where serial = 'abababababababababababababababab';
Query OK, 1 row affected (0.00 sec)
Rows matched: 7  Changed: 1  Warnings: 0

mysql> select pid, sid, pass, serial from xwi_players inner join xwi_serials using (sid) where serial = 'abababababababababababababababab';
+-------+------+----------------------------------+----------------------------------+
| pid   | sid  | pass                             | serial                           |
+-------+------+----------------------------------+----------------------------------+
| 18146 | 1139 | test                             | abababababababababababababababab |
| 18154 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
| 18164 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
| 35060 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
| 31134 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
| 34475 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
| 53204 | 1139 | cdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd | abababababababababababababababab |
+-------+------+----------------------------------+----------------------------------+
7 rows in set (0.00 sec)

mysql>
[7 Jan 2006 21:49] Miguel Solorzano
Could you please provide a complete test case script, with table create,
inserts and failed query?

Thanks in advance.
[7 Jan 2006 22:16] Olaf van der Spek
I dumped xwi_players and xwi_serials and imported them into another server (also 5.0.16-Debian_1-log) and it did change all 7 on that server.
So I'm not sure how to reproduce this on another server.
[7 Jan 2006 22:21] Miguel Solorzano
So I am changing the status to can't repeat, when you will able for to provide
a repeatable test case please feel free for to open this bug report again
and provide the test case.

Thank you for the feedback.
[7 Jan 2006 22:23] Olaf van der Spek
How can I find out what makes the test case not reproducible?
[7 Jan 2006 22:44] Miguel Solorzano
Do a check table on the host it had failed and be sure you are using the
same server's my.cnf.
[8 Jan 2006 9:52] Olaf van der Spek
mysql> check tables xwi_players, xwi_serials;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| xcc.xwi_players | check | status   | OK       |
| xcc.xwi_serials | check | status   | OK       |
+-----------------+-------+----------+----------+
2 rows in set (4.67 sec)

mysql> 

In another database on the same server I can't reproduce it either. Any other ideas?
[9 Jan 2006 9:09] Olaf van der Spek
With the attached dump (private) I can reproduce this bug on both Debian and Windows.
[9 Jan 2006 11:50] Miguel Solorzano
Just copying the tables provided I got the below result:

mysql> update xwi_players inner join xwi_serials using (sid) set pass = 'test' where
    -> serial = '4a6f18b5c1afac28c1105729c0d17167';
Query OK, 0 rows affected (0.06 sec)

Then doing a dump from the same database:

mysql> update xwi_players inner join xwi_serials using (sid) set pass = 'test' where
    -> serial = '4a6f18b5c1afac28c1105729c0d17167';
Query OK, 6 rows affected (0.03 sec)
Rows matched: 7  Changed: 6  Warnings: 0

So my question is: those tables were created with which server version?

Thanks in advance.
[9 Jan 2006 11:54] Olaf van der Spek
Hmm. I think 4.1, imported from a dump when I had to downgrade from 5.0 to 4.1.
[26 Jan 2006 17:53] William Moore
I had the same exact issue.  If you move a 4.1 DB over to 5.0 without recreating the tables it seems to have an issue with multi-table updates.
[26 Jan 2006 22:54] Olaf van der Spek
Increasing severity as no workaround is possible.
[29 Jan 2006 11:34] Klaus Ridder
I can confirm the bug on linux version 5.0.18, updated from 4.1: UPDATE with a join, 2 different queries, only return 1 row. 

Dumping the tables and re-importing them solves the problem (all rows are then updated).

This is a quite severe bug as it hides quite well; at least now I know why we got so many complaints from new customers why their applycations hasn't been forwarded ... :/
[29 Jan 2006 11:40] Klaus Ridder
( http://bugs.mysql.com/bug.php?id=16345 seems to be the same problem )
[29 Jan 2006 11:42] Klaus Ridder
Update: "Repair Table" doesn's fix the problem, only dumping and re-importing does.
[29 Jan 2006 11:43] Klaus Ridder
Update: "Repair Table" doesn's fix the problem, only dumping and re-importing does.

Old DB was on Mysql 4.1 on debian woody, migrated to mysql 5.0.18 on fedora core 4.
[30 Jan 2006 15:17] Olaf van der Spek
If I alter any index of xwi_players, both the .myd and .myi files change significantly.
[13 Feb 2006 15:35] Matthieu DOUAY
I confirm the bug on Windows version 5.0.18, updated from 4.1, updated from 4.0 and never "dump and and re-imported". 

I want to do an UPDATE with a double join : the SELECT corresponding to the UPDATE return 3 rows, but the UPDATE only update 1 row. I excute the UPDATE three times in order to update all the corresponding row.

I try to dump and to re-import the base and it solve the problem. Thanks the trick !
[13 Feb 2006 16:30] Miguel Solorzano
Thank you for the bug report.

Just copying the tables provided:
mysql> use xcc5
Database changed
mysql> update xwi_players inner join xwi_serials using (sid) set pass = 'test' where
    -> serial = '4a6f18b5c1afac28c1105729c0d17167';
Query OK, 0 rows affected (0.14 sec)
Rows matched: 1  Changed: 0  Warnings: 0

And doing the dump/restore of table  xwi_players:

mysql> update xwi_players inner join xwi_serials using (sid) set pass = 'test' where
    -> serial = '4a6f18b5c1afac28c1105729c0d17167';
Query OK, 6 rows affected (0.14 sec)
Rows matched: 7  Changed: 6  Warnings: 0
[13 Feb 2006 16:35] Olaf van der Spek
Didn't you post the same at [9 Jan 12:50]?
[13 Feb 2006 16:46] Miguel Solorzano
The dump/restore from prior version is currently the recommend process
as stated in our download page:

http://dev.mysql.com/downloads/mysql/5.0.html

Note: It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, you should protect your data by making a backup. MySQL generally recommends that you dump and reload your tables from any previous version to upgrade to 5.0.
[13 Feb 2006 17:09] Olaf van der Spek
That's not a good solution.
In this case, MySQL >= 5 should refuse to open tables created by < 5.

Could you maybe also explain the technical details of the cause of this?
[15 Feb 2006 11:10] Christian Hammers
Hello

I received this issue as bug report (#352704) against the Debian packages I maintain, too and would like to mention that from the point of view of the distributions a recommendation of dumping and restoring the complete database when upgrading from 4.1 to 5.0 is absolutely not acceptable as we're talking about GB or even TB of data here in many installations. Even a general "REPAIR TABLE" over all tables would be too much
as it can take hours to run. Please talk with the developers again if they maybe have more ideas! :)

bye,

-christian-
[15 Feb 2006 14:36] Sergei Golubchik
Christian, Olaf - the bug is currently "Verified", so it's not closed yet, and a developer is working on it.
[15 Feb 2006 17:30] Olaf van der Spek
This made me believe otherwise:
>  Updated by:       Miguel Solorzano
> -Status:           Verified
> +Status:           Won't fix

But I'm glad it's being worked on.
[15 Feb 2006 17:49] Miguel Solorzano
Right. However after your following comment I discussed that internally
with Sergei and changed again to verified.
[15 Feb 2006 17:52] Olaf van der Spek
Ah, that isn't/wasn't clear. May I suggest you also add a comment in such cases?
[15 Feb 2006 18:09] Miguel Solorzano
Of course I will do and gain thank you for all feedback you've
provided.
[3 Apr 2006 10:29] Bugs System
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/4408
[3 Apr 2006 10:32] Evgeny Potemkin
Mutli-table uses temporary table to store new values for fields. With the
new values the rowid of the record to be updated is stored in a Field_string
field. Table to be updated is set as source table of the rowid field.
But when the temporary table creates the tmp field for the rowid field it
converts it to a varstring field because the table to be updated was created by
the v4.1. Due to this the stored rowids were broken and no records for 
update were found.
[5 Apr 2006 9:29] Bugs System
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/4478
[5 Apr 2006 11:01] Evgeny Potemkin
Fixed in 5.0.21, cset 1.2086.24.1
[5 Apr 2006 17:49] Evgeny Potemkin
Fixed in 5.1.9
[6 Apr 2006 0:32] Paul Dubois
Noted in 5.0.21, 5.1.9 changelogs.

For tables created in a MySQL 4.1 installation upgraded to
MySQL 5.0 and up, multiple-table updates could update only the
first matching row. (Bug #16281)
[27 Apr 2006 17:41] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=16345 was marked as
duplicate of this one.