Bug #5269 Error 127 from query, check/repair report no problems
Submitted: 27 Aug 2004 21:56 Modified: 30 Oct 2004 1:01
Reporter: Dave Dyer Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version: OS:
Assigned to: CPU Architecture:Any

[27 Aug 2004 21:56] Dave Dyer
Description:
I have a case where a specific query on a specific database
fails with error 127, but the same query on other databases,
or on the same database dropped and rebuilt, succeeds.

At the same time, check/repair/optimize of the affected
database report no problems.

I've preserved the binary of the problem database.

Here is the query:
SELECT j_numberid,j_yearid,count(color_indicator) FROM jobpage LEFT JOIN job ON jobpage.number=job.number 
  WHERE pagenum>0 AND pagenum<=job.numpages AND j_numberid is NOT NULL AND j_yearid IS NOT NULL  AND 
   ((pagenum-1)&15)<2  AND color_indicator is not null AND color_indicator!='no color'  
    GROUP BY jobpage.number ORDER BY j_numberid,j_yearid;

How to repeat:

Here is the query:
SELECT j_numberid,j_yearid,count(color_indicator) FROM jobpage LEFT JOIN job ON jobpage.number=job.number 
  WHERE pagenum>0 AND pagenum<=job.numpages AND j_numberid is NOT NULL AND j_yearid IS NOT NULL  AND 
   ((pagenum-1)&15)<2  AND color_indicator is not null AND color_indicator!='no color'  
    GROUP BY jobpage.number ORDER BY j_numberid,j_yearid;

of couse, you need the database too.

Suggested fix:
 Hard to say, but either the query should work or repair/check should
report damage.
[27 Aug 2004 22:35] Dave Dyer
Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)
[30 Aug 2004 11:47] Hartmut Holzgraefe
> I've preserved the binary of the problem database.

Can you please attach these files to the bug report 
using the "Files" tab?
[30 Aug 2004 18:49] Dave Dyer
the binary is 7mb, too large to be accepted as
an attachment.
[31 Aug 2004 11:32] Hartmut Holzgraefe
You can upload the data to our FTP server

ftp://ftp.mysql.com/pub/mysql/upload/

please zip  or tar.gz the file using a name identifying this bug report
[31 Aug 2004 19:53] Dave Dyer
look for bug-5269.zip
[29 Oct 2004 23:26] Dave Dyer
this bug is stuck in "need feedback" state.  The requested 
feedback was supplied long ago.
[30 Oct 2004 1:01] MySQL Verification Team
Thank you for the feedback. I wasn't able to repeat:

mysql> use year2004;
Database changed
mysql> SELECT j_numberid,j_yearid,count(color_indicator) FROM jobpage LEFT JOIN job ON
    -> jobpage.number=job.number
    ->   WHERE pagenum>0 AND pagenum<=job.numpages AND j_numberid is NOT NULL AND
    -> j_yearid IS NOT NULL  AND
    ->    ((pagenum-1)&15)<2  AND color_indicator is not null AND color_indicator!='no
    '> color'
    ->     GROUP BY jobpage.number ORDER BY j_numberid,j_yearid;
+------------+----------+------------------------+
| j_numberid | j_yearid | count(color_indicator) |
+------------+----------+------------------------+
| 10004      | 2004     |                      8 |
| 10007      | 2004     |                      8 |
| 10012      | 2004     |                     14 |
| 10052      | 2004     |                      2 |
| 10056      | 2004     |                      2 |
| 10059      | 2004     |                      2 |
| 10114      | 2004     |                      2 |
| 1022       | 2004     |                      2 |
| 10247      | 2004     |                      2 |
| 10295      | 2004     |                      2 |
| 1109       | 2004     |                      2 |
| 5434       | 2004     |                      2 |
| 6309       | 2004     |                      2 |
| 8031       | 2004     |                      8 |
| 8130       | 2004     |                      2 |
| 8682       | 2004     |                      2 |
| 89         | 2004     |                      4 |
| 9          | 2004     |                      5 |
+------------+----------+------------------------+
18 rows in set (5.39 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 4.0.20a-nt-log |
+----------------+
1 row in set (0.01 sec)