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.
