| 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: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.16 | OS: | Linux (Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[7 Jan 2006 20:06]
Olaf van der Spek
[7 Jan 2006 21:49]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=16345 was marked as duplicate of this one.
