Bug #17224 Table Corrupt when optimize table is ran

Description:
Dear MySQL dev team,

I have been able to reproduce a problem on a test system where after running an optimize table and then inserting data and then selecting that data from the table I get a 127 error from MySQL.  I have hesitated to post this bug report becuase it is very similer to bug: http://bugs.mysql.com/bug.php?id=8555 but am using the lastest Fedora Core 4 from RedHat with the applied patches to NTPL.  What first lead me to this problem was a slow query that seem to hang our produciton server.  This slow query occured everytime we dropped and recreated our Rx database.

SELECT d.drug_id,d.route_id,d.medication_name,d.med_status,i.instruction,c.form_id, SUM(ii.qty) s 
FROM rxdb_v1.rxdb_drugquery AS d 
LEFT JOIN rxlist_mostcommon AS m ON d.drug_id=m.drug_id AND m.prescribed='1' AND m.library='Default' 
LEFT JOIN rxdb_v1.rxdb_routed_drugs AS rd ON rd.drug_id = d.drug_id 
LEFT JOIN rxdb_v1.rxdb_rmforms AS rf ON rf.routed_drug_id = rd.routed_drug_id 
LEFT JOIN rxdb_v1.rxdb_fullmeds AS fm ON fm.rf_drug_id = rf.rf_drug_id 
LEFT JOIN inventory_mappings AS im ON im.mapped_id=fm.medication_id AND im.map_type='0' 
LEFT JOIN inventory_items AS ii ON ii.inventory_id = im.inventory_id 
LEFT JOIN rxlist_drug_instructions AS i ON d.drug_id = i.drug_id AND d.route_id=i.route_id 
LEFT JOIN rxlist_calc_forms AS c ON rf.form_id = c.form_id 
WHERE d.medication_name LIKE 'c%' AND d.med_status = 0 AND im.inventory_id IS NULL AND d.med_status=0 
GROUP BY d.drug_id ORDER BY m.total DESC, d.medication_name ASC, s DESC LIMIT 10;

The EXPLAIN of the query was WAY off show that one of the joins wasn't using a primary key as its ref table. 
Notice that tables 'rf' and 'fm' are not using any keys.
+-------+--------+------------------------------+-----------------+---------+------------------------+-------+----------------------------------------------+
| table | type   | possible_keys                | key             | key_len | ref                    | rows  | Extra                                        |
+-------+--------+------------------------------+-----------------+---------+------------------------+-------+----------------------------------------------+
| i     | system | drug_id                      | NULL            |    NULL | NULL                   |     0 | const row not found                          |
| d     | range  | medication_name              | medication_name |      11 | NULL                   |  3006 | Using where; Using temporary; Using filesort |
| m     | eq_ref | uidx,drug_id,prescribed,idx3 | uidx            |     263 | d.drug_id,const,const  |     1 |                                              |
| rd    | ref    | idx1                         | idx1            |       4 | d.drug_id              |     1 |                                              |
| rf    | ALL    | idx1                         | NULL            |    NULL | NULL                   | 45747 |                                              |
| fm    | ALL    | idx2                         | NULL            |    NULL | NULL                   | 52638 |                                              |
| im    | eq_ref | PRIMARY,map_type             | PRIMARY         |       8 | fm.medication_id,const |     1 | Using where; Not exists                      |
| ii    | eq_ref | PRIMARY                      | PRIMARY         |       4 | im.inventory_id        |     1 |                                              |
| c     | eq_ref | PRIMARY                      | PRIMARY         |       4 | rf.form_id             |     1 | Using index                                  |
+-------+--------+------------------------------+-----------------+---------+------------------------+-------+----------------------------------------------+ 

After Optimizing the table:
+-------+--------+------------------------------+-----------------+---------+------------------------+------+----------------------------------------------+
| table | type   | possible_keys                | key             | key_len | ref                    | rows | Extra                                        |
+-------+--------+------------------------------+-----------------+---------+------------------------+------+----------------------------------------------+
| i     | system | drug_id                      | NULL            |    NULL | NULL                   |    0 | const row not found                          |
| d     | range  | medication_name              | medication_name |      11 | NULL                   | 3006 | Using where; Using temporary; Using filesort |
| m     | eq_ref | uidx,drug_id,prescribed,idx3 | uidx            |     263 | d.drug_id,const,const  |    1 |                                              |
| rd    | ref    | idx1                         | idx1            |       4 | d.drug_id              |    1 |                                              |
| rf    | ref    | idx1                         | idx1            |       4 | rd.routed_drug_id      |    1 |                                              |
| fm    | ref    | idx2                         | idx2            |       4 | rf.rf_drug_id          |    1 |                                              |
| im    | eq_ref | PRIMARY,map_type             | PRIMARY         |       8 | fm.medication_id,const |    1 | Using where; Not exists                      |
| ii    | eq_ref | PRIMARY                      | PRIMARY         |       4 | im.inventory_id        |    1 |                                              |
| c     | eq_ref | PRIMARY                      | PRIMARY         |       4 | rf.form_id             |    1 | Using index                                  |
+-------+--------+------------------------------+-----------------+---------+------------------------+------+----------------------------------------------+

Note: I am sure the query on its own could be a whole other debate, I personally didn't write this query but its it safe to join table 'A' to its self and then again?

Here is a snippet of the PROCESS LIST when the query above ran:

| 32998 | webchart_m25     | xxx.x.xxx.xx:32912 | webchart_m25     | Query   | 1079 | Copying to tmp table  |SELECT d.drug_id,d.route_id,d.medication_name,d.med_status, |

A short while after the running the optimize table the table became corrupt and I had to run a REPAIR table on all tables to fix the 127 error code that MySQL reported.  The temparary fix was *not* to optimize tables.

Here are some error logs recorded into mysql.err:

060207 11:24:34 [ERROR] Got error 127 when reading table './webchart_fwobgyn/rxlist_refills'
060207 11:24:34 [ERROR] /usr/local/mysql/bin/mysqld: Got error 127 from storage engine

I will not that I am only having this problem on an EM64T server using MySQL 4.x and 5.0.x.  Below is the system specs

# MySQL Version
mysql-pro-4.1.15-unknown-linux-gnu-x86_64-glibc23
mysql-pro-5.0.15-linux-x86_64-glibc23

# Linux Kern version
Linux 2.6.15.2 #1 SMP Mon Feb 6 16:16:15 EST 2006 x86_64 x86_64 x86_64 GNU/Linux

# libc version 
[root@dhcp54 lib]# ./libc-2.3.5.so 
GNU C Library development release version 2.3.5, by Roland McGrath et al.
Copyright (C) 2005 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
Compiled by GNU CC version 4.0.0 20050525 (Red Hat 4.0.0-9).
Compiled on a Linux 2.4.20 system on 2005-05-30.

How to repeat:
I do not have an sql file or script that will demonstrate this behavior, but I an provide access to the test server the problem is occurring on which is reproducible.

MySQL config

Attachment: my.cnf (application/octet-stream, text), 16.70 KiB.

Update:

Today I compiled MySQL and ran the sets that caused the table corruption.  To my surprise all whet well and no data was corrupt, below are the config options.

CC=gcc \
CXX=gcc \
CFLAGS="-O3 -mpentiumpro" \
CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti" \
./configure \
--enable-assembler \
--enable-shared=no \
--with-charset=latin1 \
--without-uca \
--with-big-tables \
--with-archive-storage-engine \
--without-ndb \
--without-berkeley \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--prefix="/usr/local/mysql" \
--exec-prefix="/usr/local/mysql/" \
--libexecdir="/usr/local/mysql/bin" \
--sbindir="/usr/local/mysql/bin"

Thank you for a problem report. Please, reopen it when you will see the corruption in similar case again. But, please, try to repeat on the smallest possible set of data in that table, and upload it to the report as a file. We really need a repeatable test case to be able to work on any bug.

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".