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;
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;