MySQL Bugs: #16345: Multi-table update sometimes updates only one row (in tables from 4.x.y)

Bug #16345	Multi-table update sometimes updates only one row (in tables from 4.x.y)
Submitted:	10 Jan 2006 17:30	Modified:	27 Apr 2006 17:11
Reporter:	Surinder Singh	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.19-BK, 5.0.18-nt	OS:	Linux (Linux, Windows 2000 Server SP4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The following query should update 3 rows, but only updates the first one:

UPDATE products, productcategories SET SalePrice = 100
WHERE productcategories.productcode = products.productcode
AND productcategories.Category = "Main";

Running the following SELECT returns 3 rows (i.e. the ones the UPDATE should be updating):

SELECT * FROM products, productcategories
WHERE productcategories.productcode = products.productcode
AND productcategories.Category = "Main";

I've done a "REPAIR TABLE" on both of the tables to make sure they're not corrupt.

When I change the query to the following it works okay, all 3 rows are updated:

UPDATE products RIGHT JOIN productcategories
ON products.productcode = productcategories.productcode
SET SalePrice = 100
WHERE productcategories.Category = "Main";

The update query works fine in 4.0.18 and 4.1.10.

How to repeat:
If I create a fresh copy of the tables using CREATE and INSERT statements, it works okay. So it may be best if you test it directly on my server or if I email you the FRM, MYD & MYI files.

Suggested fix:
By changing the UPDATE query to use RIGHT JOIN and ON. This a pain because it means I have to check and change all my existing MySQL apps.

Thank you for a problem report. First of all, can you, please, upgrade to a newer version, 5.0.18, already available. Your 5.0.15 is old enough, and many bugs were fixed. Please, check with newer version and inform about the results.

Anyway, we'll need a repeatable test case. Because of the following your statement:

"If I create a fresh copy of the tables using CREATE and INSERT statements, it
works okay. So it may be best if you test it directly on my server or if I email
you the FRM, MYD & MYI files."

looks like it is impossible to repeat with a specially created test case. 

Please, try to perform CHECK for your tables used. You may also try to create table with the same structure and then INSERT INTO new SELECT * FROM old.

We can use your files, if they are not too large, but as last resort. Try to fix the problem with usual maintenance actions first.

Thanks for your reply. I've tried the latest version 5.0.18 as you suggested. It has made no difference. The FRM, MYD & MYI files are only a few kilobytes. Please let me know how I can I send these to you.

You can upload the FRM, MYD & MYI files using the File tab at the bug page. 

Have you tried to CHECK your table, by the way? Maybe, it is just corrupted?

I've uploaded the files for you to have look at. I've run the Check and Repair routines. They seems to indicate the tables are okay. When I do a mysqldump and import the data to create fresh tables, the behaviour seems to correct itself. If the tables are corrupt then in some ways it's even more worrying because the Check and Repair routines are not picking this up.

I can confirm this bug on mysql on linux 5.0.18.

The tables have initially been set up on mysql 4.

The bug seems to occur in every UPDATE-Query with a JOIN, for example

UPDATE Customers C JOIN Dokuments D ON D.Customer = C.ID AND D.Form = 36 SET Date = NOW() WHERE Date IS NULL 

It always updates only 1 row per query, so I had to run it 36 times to update all 36 rows ... quite annoying, and quite a source of errors.

Worked fine before I updated to mysql 5.

I continue to discuss this bug in 

http://bugs.mysql.com/bug.php?id=16281

Verified just as described on your tables (uploaded as private file) with 5.0.19-BK (ChangeSet@1.2024, 2006-02-01 14:46:30+01:00) on Linux. The real reason for the problem is that tables are created in pre-5.0.x version and simply copied to 5.0.x. 

(Note, that the only right way to upgrade to 5.0.x is with mysqldump and then restore in 5.0.x) 

But anyway, this should be fixed.

The reason why we didn't use mysqldump was because it was failing on some tables. For example the following from mysqldump:

DROP TABLE IF EXISTS ws_reports;
CREATE TABLE ws_reports (
  ReportNo int(11) NOT NULL default '0',
  ReportName varchar(70) default NULL,
  ReportType varchar(50) default NULL,
  ReportParameter varchar(50) default NULL,
  Lookup1 varchar(12) default NULL,
  Lookup2 varchar(12) default NULL,
  Lookup3 varchar(12) default NULL,
  Lookup4 varchar(12) default NULL,
  SQL mediumtext,
  PRIMARY KEY  (ReportNo)
) TYPE=MyISAM;

Fails with error:

"ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SQL mediumtext,
  PRIMARY KEY  (ReportNo)
) TYPE=MyISAM' at line 10"

The error here is caused by a field called "SQL". But mysqldump shouldn't generate SQL statements that will fail.

We had many databases to import and were short of time to investigate why mysqldump was failing.

The check and repair tools are not doing their job. They should at least detect that tables are from version 4 and give a warning and perhaps offer to recreate them using version 5.

Duplicate of bug #16281