Bug #52551 Incorrect key file error for temporary tables with large join-clause query
Submitted: 2 Apr 2010 15:23 Modified: 6 Apr 2010 23:29
Reporter: Patrick Crews Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1-bugteam, 6.0-codebase-bugfixing OS:Any
Assigned to: Patrick Crews CPU Architecture:Any
Tags: Incorrect key file, temporary table

[2 Apr 2010 15:23] Patrick Crews
Description:
Receiving an incorrect key file error for a temporary table:
Incorrect key file for table '/<vardir>/tmp/#sql_5a62_0.MYI'; try to repair it

This is occurring for both 5.1 and 6.0 and occurs consistently - the same RQG command line will produce this error every time.

Still trying to produce an exact MTR test case as well as researching what (if any) optimizer variables affect this.  It should be noted that I did not run into this bug when testing other optimizer_join_cache_level values (0-8), but I feel this needs to be re-verified.

Failing query:
SELECT table1 . `col_varchar_1024_utf8_key` AS field1, 
       table1 . `col_varchar_1024_utf8_key` AS field2, 
       table1 . `col_date_key`              AS field3 
FROM   p AS table1 
       LEFT JOIN j AS table2 
                 LEFT OUTER JOIN k AS table3 
                   ON table2 . `col_varchar_1024_latin1` > table3 . `col_varchar_10_utf8` 
                 LEFT OUTER JOIN j AS table4 
                   ON table2 . `col_varchar_10_latin1_key` > table4 . `col_varchar_1024_utf8_key` 
                 RIGHT JOIN p AS table5 
                   ON table2 . `col_varchar_10_utf8_key` > table5 . `col_varchar_1024_latin1_key` 
         ON table1 . `col_varchar_10_latin1` <= table4 . `col_varchar_10_latin1` 
       LEFT JOIN o AS table6 
                 LEFT OUTER JOIN f AS table7 
                   ON table6 . `col_varchar_10_utf8` > table7 . `col_varchar_10_utf8_key` 
         ON table3 . `pk` > table6 . `col_int_key` 
       LEFT JOIN m AS table8 
         ON table3 . `pk` < table8 . `col_int_key` 
       LEFT OUTER JOIN i AS table9 
         ON table6 . `pk` = table9 . `col_int_key` 
WHERE  table1 . `col_int_key` >= 0 
       AND table1 . `col_int_key` < ( 0 + 8 ) 
       AND table1 . `col_int_key` IS NOT NULL 
       AND table1 . `col_int_key` < table5 . `pk` 
       AND table1 . `col_int_key` <> 2 
        OR table1 . `col_int_key` >= 0 
           AND table1 . `col_int_key` <= ( 0 + 5 ) 
           AND table1 . `pk` BETWEEN 0 AND ( 0 + 8 ) 
           AND table1 . `col_int_key` > 0 
           AND table1 . `col_int_key` <= ( 4 + 4 ) 
ORDER  BY field1, 
          field2, 
          field3 
LIMIT  100 

How to repeat:
Both mysql directories were built with BUILD/compile-pentium-debug-max-no-ndb

RQG command line:
perl ./runall.pl --basedir1=/mysql-6.0 --vardir1=/6var2 --basedir2=/mysql51 --vardir2=/5var2 --mysqld1=--init-file=/jcl2.sql --mtr-build-thread=121 --threads=1 --queries=50000 --Validator=ResultsetComparatorSimplify --debug   --grammar=conf/optimizer/optimizer_access_exp.yy --gendata=conf/optimizer/range_access.zz --seed=1270154068

jcl2.sql contents:
SET GLOBAL OPTIMIZER_SWITCH = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,mrr=on,mrr_cost_based=off,index_condition_pushdown=on';

SET GLOBAL optimizer_join_cache_level = 2;
[4 Apr 2010 13:53] MySQL Verification Team
you sure tmpdir doesn't just run out of disk space?
[6 Apr 2010 23:29] Patrick Crews
You are totally correct.  I was working on a new test and hadn't run across this before.  Research showed that it is not a bug.  Closing out as such.