MySQL Bugs: #1711: Error 127 from table handler, unknown cause

Bug #1711	Error 127 from table handler, unknown cause
Submitted:	30 Oct 2003 7:54	Modified:	11 Dec 2003 6:33
Reporter:	Adam Clauss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (Windows XP/2003)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
I have an application which uses the embedded server to add many items into a table (as they are parsed in from a file).  At the end of all these inserts, I do a couple updates to ensure that certain information is in the right place.
I just upgraded the application to use the 4.0.16 server (previously using 4.0.12) and now the very first update returns the following error:
Got error 127 from table handler

I then confirmed that this bug is not unique to the embedded server by running an identical set of queries on a standalone MySQL server (running on Windows Server 2003) and it gave me the same error, in the same spot.

Running CHECK TABLE on all involved tables reveals nothing.

I am not real sure why this is occuring (as it worked just fine in 4.0.12), so I have attached a complete .sql file of all the queries I ran that lead up to this.

How to repeat:
Run the attached .sql file.  It should create its own database/tables.  The final line of the file causes the 127 error.

Note that if you run it twice, you will get key errors - so clear out or (drop the entire majestic_css database) before running again.

Queries that will cause the error

Attachment: test.sql (text/plain), 27.67 KiB.

The UPDATE statement in the supplied test reports errno 127 on both Windows XP and Linux with 4.0.16.  Neither CHECK TABLE nor myisamchk itself find any problems with the table.

Thank you

I just installed 4.0.15 and can confirm that the problem exists there as well.  Was unable to locate 4.0.13 or 4.0.14 for testing.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The problem is the last update:

UPDATE items, items AS items2 SET items.catID = items2.catID, items.name = items2.name, items.id = items2.id, items.description = items2.description, items.added = 1 WHERE items.dupeitem = items2.scriptID;

MySQL can't handle the case where you both use and update the same table in the same query in a multi-update statements.

Beasue of caching problems MySQL thinks the table is crashed (becasue a row changed unexpectedly) and stops and gives an error.

I have now fixed this by disabling the row cache for any tables that are updated.  After this the test case passed without any problems.

The fix will be in 4.0.17