Linux 2.6.32-279.1.1.el6.x86_64 (Red Hat EL6)

Uploaded the mysqldump of the 5 tables to ftp.askmonty.org/private/mdev_3801.mysqldump.gz

I tested with mysql-5.1.66-linux-x86_64-glibc23 and it does not crash.

I tested with mysql-5.1.65-linux-x86_64-glibc23 and it does not crash.

With a "./BUILD/compile-pentium64-debug-max" of 5.3.9 on kernel 2.6.32-279.1.1.el6.x86_64 (Red Hat EL6) I see an assertion from within maria_create(...) in storage/maria/ma_create.c

if (keyseg->flag & HA_SPACE_PACK)
{
560 DBUG_ASSERT(!(keyseg->flag & (HA_VAR_LENGTH_PART | HA_BLOB_PART)));
keydef->flag |= HA_SPACE_PACK_USED | HA_VAR_LENGTH_KEY;
options|=HA_OPTION_PACK_KEYS; /* Using packed keys */
length++; /* At least one length uchar */

ERROR 1030 (HY000): Got error 140 from storage engine

mysqld: ma_create.c:560: maria_create: Assertion `!(keyseg->flag & (8 | 32))' failed.
121008 4:03:39 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 5.3.9-MariaDB-debug
key_buffer_size=134213632
read_buffer_size=131072
max_used_connections=1
max_threads=152
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 463889 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x443fa88
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f6c3012fe60 thread_stack 0x48000
./bin/mysqld(my_print_stacktrace+0x35) [0xc3ddf9]
./bin/mysqld(handle_fatal_signal+0x2e3) [0x86635b]
/lib64/libpthread.so.0() [0x3f5dc0f4a0]
/lib64/libc.so.6(gsignal+0x35) [0x3f5d832885]
/lib64/libc.so.6(abort+0x175) [0x3f5d834065]
/lib64/libc.so.6() [0x3f5d82b9fe]
/lib64/libc.so.6(__assert_perror_fail+0) [0x3f5d82bac0]
./bin/mysqld(maria_create+0x1398) [0xa29568]
./bin/mysqld(create_internal_tmp_table(st_table*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long)+0x5e0) [0x7730f7]
./bin/mysqld() [0x77350c]
./bin/mysqld(create_internal_tmp_table_from_heap(THD*, st_table*, st_maria_columndef*, st_maria_columndef**, int, bool)+0x60) [0x77325e]
./bin/mysqld(select_union::send_data(List<Item>&)+0x208) [0x8d4e64]
./bin/mysqld() [0x7775ff]
./bin/mysqld() [0x7754dc]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x4e6) [0x774f0b]
./bin/mysqld() [0x77451f]
./bin/mysqld(JOIN::exec()+0x2d24) [0x754dda]
./bin/mysqld(mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x3b6) [0x75567a]
./bin/mysqld(st_select_lex_unit::exec()+0x97c) [0x8d73bc]
./bin/mysqld(mysql_derived_fill(THD*, st_lex*, TABLE_LIST*)+0xfa) [0x8d95cb]
./bin/mysqld(mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int)+0x10a) [0x8d82fd]
./bin/mysqld(st_join_table::preread_init()+0xa1) [0x767977]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x107) [0x774b2c]
./bin/mysqld(join_tab_execution_startup(st_join_table*)+0x1e6) [0x83d80b]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x278) [0x774c9d]
./bin/mysqld() [0x77451f]
./bin/mysqld(JOIN::exec()+0x2d24) [0x754dda]
./bin/mysqld(mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x3b6) [0x75567a]
./bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x1a4) [0x74be39]
./bin/mysqld() [0x6d893d]
./bin/mysqld(mysql_execute_command(THD*)+0x7e1) [0x6cf746]
./bin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2ef) [0x6db32d]
./bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xa79) [0x6ccea3]
./bin/mysqld(do_command(THD*)+0x286) [0x6cc17e]
./bin/mysqld(handle_one_connection+0x199) [0x6c8fcb]
/lib64/libpthread.so.0() [0x3f5dc077f1]
/lib64/libc.so.6(clone+0x6d) [0x3f5d8e570d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f6ba80094f0): SELECT t1.deal_id AS `deal_id` FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) AND (t10.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_c AS t11 ON ((t10.deal_id = t11.deal_id) AND (t10.feid = t11.feid)) AND (t10.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') WHERE ((t1.deal_id) IN ( SELECT * FROM ( (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target_all ) = '1') JOIN table_c AS t3 ON ((t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid)) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_f AS t4 ON t3.exchange = t4.exchange WHERE t4.exchange IN ('CT','ND','NY','AM')) UNION (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target_all ) = '1') JOIN table_c AS t3 ON ((t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid)) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_b AS t5 ON t3.country = t5.country WHERE t5.country = 'US') ) tU )) AND ((t1.deal_id) IN ( SELECT * FROM ( (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t6 ON (t1.deal_id = t6.deal_id) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_c AS t7 ON ((t6.deal_id = t7.deal_id) AND (t6.feid = t7.feid)) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_f AS t8 ON t7.exchange = t8.exchange WHERE t8.exchange IN ('CT','ND','NY','AM')) UNION (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t6 ON (t1.deal_id = t6.deal_id) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_c AS t7 ON ((t6.deal_id = t7.deal_id) AND (t6.feid = t7.feid)) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') ) ) tU )) LIMIT 200
Connection ID (thread ID): 1
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

If I:
set @@optimizer_switch='materialization=off'

it no longer crashes.

I tested with mariadb-5.3.7-Linux-x86_64 and it does crash.
I tested with mariadb-5.3.0-beta-Linux-x86_64 and it does not crash.

I tested with mariadb-5.3.3-rc-Linux-x86_64 and it does not crash.

I tested with mariadb-5.3.5-ga-Linux-x86_64 and it does not crash.

I tested with mariadb-5.3.6-Linux-x86_64 and it does crash, the backtrace differs:

121008 7:28:29 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 5.3.6-MariaDB
key_buffer_size=134213632
read_buffer_size=131072
max_used_connections=1
max_threads=152
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 463787 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x2c00e60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f6924480e68 thread_stack 0x48000
./bin/mysqld(my_print_stacktrace+0x2e) [0xa2b06e]
./bin/mysqld(handle_fatal_signal+0x3f9) [0x762339]
/lib64/libpthread.so.0() [0x3f5dc0f4a0]
./bin/mysqld(JOIN::cleanup(bool)+0x113) [0x6bd4c3]
./bin/mysqld(JOIN::join_free()+0x30) [0x6bd6b0]
./bin/mysqld() [0x6bee91]
./bin/mysqld(JOIN::exec()+0x851) [0x6cf711]
./bin/mysqld(mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x199) [0x6d1809]
./bin/mysqld(st_select_lex_unit::exec()+0xf29) [0x7ac199]
./bin/mysqld(mysql_derived_fill(THD*, st_lex*, TABLE_LIST*)+0x198) [0x7adb28]
./bin/mysqld(mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int)+0xb8) [0x7aec48]
./bin/mysqld(st_join_table::preread_init()+0xa1) [0x6aacd1]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x496) [0x6bead6]
./bin/mysqld(join_tab_execution_startup(st_join_table*)+0xd8) [0x744aa8]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x251) [0x6be891]
./bin/mysqld() [0x6bec23]
./bin/mysqld(JOIN::exec()+0x851) [0x6cf711]
./bin/mysqld(mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x199) [0x6d1809]
./bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x16f) [0x6d21df]
./bin/mysqld() [0x647a2e]
./bin/mysqld(mysql_execute_command(THD*)+0x3a58) [0x64d628]
./bin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x299) [0x650409]
./bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xa9b) [0x6512fb]
./bin/mysqld(do_command(THD*)+0x101) [0x651e51]
./bin/mysqld(handle_one_connection+0xfd) [0x64325d]
/lib64/libpthread.so.0() [0x3f5dc077f1]
/lib64/libc.so.6(clone+0x6d) [0x3f5d8e570d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f68d4004bc8): SELECT t1.deal_id AS `deal_id` FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) AND (t10.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_c AS t11 ON ((t10.deal_id = t11.deal_id) AND (t10.feid = t11.feid)) AND (t10.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') WHERE ((t1.deal_id) IN ( SELECT * FROM ( (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target_all ) = '1') JOIN table_c AS t3 ON ((t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid)) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_f AS t4 ON t3.exchange = t4.exchange WHERE t4.exchange IN ('CT','ND','NY','AM')) UNION (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target_all ) = '1') JOIN table_c AS t3 ON ((t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid)) AND (t2.co_role_id IN ( SELECT co_role_id FROM table_a WHERE target ) = '1') JOIN table_b AS t5 ON t3.country = t5.country WHERE t5.country = 'US') ) tU )) AND ((t1.deal_id) IN ( SELECT * FROM ( (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t6 ON (t1.deal_id = t6.deal_id) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_c AS t7 ON ((t6.deal_id = t7.deal_id) AND (t6.feid = t7.feid)) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_f AS t8 ON t7.exchange = t8.exchange WHERE t8.exchange IN ('CT','ND','NY','AM')) UNION (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t6 ON (t1.deal_id = t6.deal_id) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') JOIN table_c AS t7 ON ((t6.deal_id = t7.deal_id) AND (t6.feid = t7.feid)) AND (t6.co_role_id IN ( SELECT co_role_id FROM table_a WHERE acquirer_all ) = '1') ) ) tU )) LIMIT 200
Connection ID (thread ID): 1
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Hello Peter,

Could you please upload the table dump again, because the uploaded file is of size 0.
I have no idea what went wrong. We need both the schema and data.

Thank you!

Hello Timour,
Thanks for taking a look.
Is there enough free space on the FTP disk? Or maybe a max file size limit? The file is 115M. Here is the FTP log:

Status: Resolving address of ftp.askmonty.org
Status: Connecting to 195.218.93.115:21...
Status: Connection established, waiting for welcome message...
Response: 220 Welcome to ftp.askmonty.org. Anonymous logins allowed.
Command: USER anonymous
Response: 331 Please specify the password.
Command: PASS **************
Response: 230-Welcome to ftp.askmonty.org.
Response: 230-
Response: 230-Anonymous logins are allowed.
Response: 230-
Response: 230-There are two main folders here: private and public.
Response: 230-
Response: 230-You may upload to the private directory, but you are not allowed to
Response: 230-download from it.
Response: 230-
Response: 230-You may download from the public directory, but you are not allowed to
Response: 230-upload to it.
Response: 230-
Response: 230-
Response: 230 Login successful.
Command: SYST
Response: 215 UNIX Type: L8
Command: FEAT
Response: 211-Features:
Response: EPRT
Response: EPSV
Response: MDTM
Response: PASV
Response: REST STREAM
Response: SIZE
Response: TVFS
Response: UTF8
Response: 211 End
Command: OPTS UTF8 ON
Response: 200 Always in UTF8 mode.
Status: Connected
Status: Retrieving directory listing...
Command: PWD
Response: 257 "/"
Command: TYPE I
Response: 200 Switching to Binary mode.
Command: PASV
Response: 227 Entering Passive Mode (195,218,93,115,195,211)
Command: LIST
Response: 150 Here comes the directory listing.
Response: 226 Directory send OK.
Status: Directory listing successful
Status: Retrieving directory listing...
Command: CWD private
Response: 250-You may upload to this directory, but you are not allowed to download
Response: 250-from it. Downloadable files are in the public/ directory.
Response: 250-
Response: 250 Directory successfully changed.
Command: PWD
Response: 257 "/private"
Command: PASV
Response: 227 Entering Passive Mode (195,218,93,115,196,17)
Command: LIST
Response: 150 Here comes the directory listing.
Response: 226 Transfer done (but failed to open directory).
Status: Directory listing successful
Status: Resolving address of ftp.askmonty.org
Status: Connecting to 195.218.93.115:21...
Status: Connection established, waiting for welcome message...
Response: 220 Welcome to ftp.askmonty.org. Anonymous logins allowed.
Command: USER anonymous
Response: 331 Please specify the password.
Command: PASS **************
Response: 230-Welcome to ftp.askmonty.org.
Response: 230-
Response: 230-Anonymous logins are allowed.
Response: 230-
Response: 230-There are two main folders here: private and public.
Response: 230-
Response: 230-You may upload to the private directory, but you are not allowed to
Response: 230-download from it.
Response: 230-
Response: 230-You may download from the public directory, but you are not allowed to
Response: 230-upload to it.
Response: 230-
Response: 230-
Response: 230 Login successful.
Command: OPTS UTF8 ON
Response: 200 Always in UTF8 mode.
Status: Connected
Status: Starting upload of mdev_3801.mysqldump.gz
Command: CWD /private
Response: 250-You may upload to this directory, but you are not allowed to download
Response: 250-from it. Downloadable files are in the public/ directory.
Response: 250-
Response: 250 Directory successfully changed.
Command: PWD
Response: 257 "/private"
Command: TYPE I
Response: 200 Switching to Binary mode.
Command: PASV
Response: 227 Entering Passive Mode (195,218,93,115,196,20)
Command: STOR mdev_3801.mysqldump.gz
Response: 553 Could not create file.
Error: Critical file transfer error

Oh, it is probably because the filename already exists. I am sending mdev_3801.mysqldump2.gz now.

• The bug is not reproducible with MariaDB 5.5.28
• On MariaDB 5.3.9, debug build there is no crash, but instead query execution stops with the following error:

ERROR 1030 (HY000): Got error 140 from storage engine

This error means: "MySQL error code 140: Wrong create options".
Most likely it is due to creating a temporary table with incorrect options.

The error is produced by the following stack trace:

#1 0x000000000086c153 in handler::print_error (this=0x46e9f40, error=140, errflag=0) at handler.cc:3011
#2 0x00000000007945c0 in create_internal_tmp_table (table=0x7fffe7444170, keyinfo=0x36ccd28, start_recinfo=0x36cca90, recinfo=0x3046480, options=2147764736) at sql_select.cc:14852
#3 0x0000000000794982 in create_internal_tmp_table_from_heap2 (thd=0x2d68a48, table=0x36cbcc0, start_recinfo=0x36cca90, recinfo=0x3046480, error=135, ignore_last_dupp_key_error=true, hton=0x1b5d988, proc_info=0xde8e83 "converting HEAP to Aria") at sql_select.cc:15091
#4 0x00000000007946e1 in create_internal_tmp_table_from_heap (thd=0x2d68a48, table=0x36cbcc0, start_recinfo=0x36cca90, recinfo=0x3046480, error=135, ignore_last_dupp_key_error=true) at sql_select.cc:14875
#5 0x00000000008f17e2 in select_union::send_data (this=0x30463f8, values=...) at sql_union.cc:87
#6 0x0000000000798a1a in end_send (join=0x30a6ad8, join_tab=0x30a7c70, end_of_records=false) at sql_select.cc:16757
#7 0x0000000000796922 in evaluate_join_record (join=0x30a6ad8, join_tab=0x30a7950, error=0) at sql_select.cc:15915
#8 0x00000000007962ed in sub_select (join=0x30a6ad8, join_tab=0x30a7950, end_of_records=false) at sql_select.cc:15757
#9 0x000000000079592e in do_select (join=0x30a6ad8, fields=0x2de1c40, table=0x0, procedure=0x0) at sql_select.cc:15378
#10 0x0000000000776ba5 in JOIN::exec (this=0x30a6ad8) at sql_select.cc:2761
#11 0x000000000077742f in mysql_select (thd=0x2d68a48, rref_pointer_array=0x36bfaf0, tables=0x2de1818, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x30463f8, unit=0x2de17c8, select_lex=0x36bf898) at sql_select.cc:2982
#12 0x00000000008f3c84 in st_select_lex_unit::exec (this=0x2de17c8) at sql_union.cc:757
#13 0x00000000008f5e6d in mysql_derived_fill (thd=0x2d68a48, lex=0x2d6af38, derived=0x36c5d50) at sql_derived.cc:869
#14 0x00000000008f4bb8 in mysql_handle_single_derived (lex=0x2d6af38, derived=0x36c5d50, phases=96) at sql_derived.cc:176
#15 0x0000000000789017 in st_join_table::preread_init (this=0x30988d0) at sql_select.cc:10171
#16 0x0000000000795f1c in sub_select (join=0x30456f0, join_tab=0x30988d0, end_of_records=false) at sql_select.cc:15672
#17 0x000000000085c0db in join_tab_execution_startup (tab=0x3097910) at opt_subselect.cc:4945
#18 0x0000000000796087 in sub_select (join=0x30456f0, join_tab=0x3097910, end_of_records=false) at sql_select.cc:15705
#19 0x000000000079592e in do_select (join=0x30456f0, fields=0x2d6b5f0, table=0x0, procedure=0x0) at sql_select.cc:15378
#20 0x0000000000776ba5 in JOIN::exec (this=0x30456f0) at sql_select.cc:2761

I managed to reduce the original test case through the following commands:

CREATE TABLE table_c_s (
deal_id int(10) unsigned NOT NULL,
feid int(10) unsigned NOT NULL,
exchange char(2) DEFAULT NULL,
PRIMARY KEY (deal_id,feid),
KEY feid (feid,deal_id),
KEY exchange (exchange),
KEY deal_feid (deal_id,feid),
KEY feid_date (feid)
) ENGINE=MyISAM;

insert into table_c_s select deal_id, feid, exchange from table_c where co_role_id = 2;
delete from table_d where co_role_id <> 2;
delete from table_f where exchange <> 'ND';

The simplified query that reproduces the bug is:

set @@tmp_table_size=16384;

SELECT t1.deal_id
FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id)
WHERE
t1.deal_id IN
(SELECT *
FROM (SELECT DISTINCT t1.deal_id
FROM table_e AS t1
JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id)
JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid)
JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU)
limit 10;

– EXPLAIN in 5.5
----------------------------------------------------------------------------------------------------------------------------------------------------+

----------------------------------------------------------------------------------------------------------------------------------------------------+

----------------------------------------------------------------------------------------------------------------------------------------------------+
– EXPLAIN in 5.3
--------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------+

There is a bug both in the current 5.3 and 5.5 versions. Based on the query plan, and the stack trace below, my hypothesis is that the problem is in the code that creates the temporary table for the derived table. More specifically the problem could be related to the index created on the fly for this temporary table.

== In 5.3 (both optimized and debug build) the query results in a crash with the following stack trace:

#0 0x00007f8f31944319 in __memset_sse2 () from /lib64/libc.so.6
#1 0x0000000000a021ce in sort_insert_key (sort_param=0x7f8f3339c2f0, key_block=0x7f8ec410d358, key=0x7f8ec4153358 "", prev_block=18446744073709551615) at ma_check.c:5755
#2 0x0000000000a016a7 in sort_key_write (sort_param=0x7f8f3339c2f0, a=0x7f8ec4153358 "") at ma_check.c:5526
#3 0x0000000000a80c30 in write_index (info=0x7f8f3339c2f0, sort_keys=0x7f8ec414d650, count=1953) at ma_sort.c:772
#4 0x0000000000a7ec77 in _ma_create_index_by_sort (info=0x7f8f3339c2f0, no_messages=1 '\001', sortbuff_size=134217728) at ma_sort.c:201
#5 0x00000000009fc7ad in maria_repair_by_sort (param=0x7f8ec40ec530, info=0x7f8ec40e7568, name=0x7f8f3339cbb0 "/tmp/#sql_6aca_0", rep_quick=1 '\001') at ma_check.c:3877
#6 0x00000000009eb4c0 in ha_maria::repair (this=0x7f8ec40b8af0, thd=0x2d69cf8, param=0x7f8ec40ec530, do_optimize=false) at ha_maria.cc:1666
#7 0x00000000009ec104 in ha_maria::enable_indexes (this=0x7f8ec40b8af0, mode=2) at ha_maria.cc:1989
#8 0x00000000009ec802 in ha_maria::end_bulk_insert (this=0x7f8ec40b8af0) at ha_maria.cc:2178
#9 0x00000000007a8583 in handler::ha_end_bulk_insert (this=0x7f8ec40b8af0) at handler.h:1784
#10 0x0000000000794b7e in create_internal_tmp_table_from_heap2 (thd=0x2d69cf8, table=0x7f8ec4059370, start_recinfo=0x7f8ec405a140, recinfo=0x7f8ec404fc38, error=135, ignore_last_dupp_key_error=true, hton=0x1b5ec58, proc_info=0xde8e83 "converting HEAP to Aria") at sql_select.cc:15130
#11 0x00000000007946e1 in create_internal_tmp_table_from_heap (thd=0x2d69cf8, table=0x7f8ec4059370, start_recinfo=0x7f8ec405a140, recinfo=0x7f8ec404fc38, error=135, ignore_last_dupp_key_error=true) at sql_select.cc:14875
#12 0x00000000008f17e2 in select_union::send_data (this=0x7f8ec404fbb0, values=...) at sql_union.cc:87
#13 0x0000000000798a1a in end_send (join=0x7f8ec404fca8, join_tab=0x7f8ec4069d48, end_of_records=false) at sql_select.cc:16757
#14 0x0000000000796922 in evaluate_join_record (join=0x7f8ec404fca8, join_tab=0x7f8ec4069a28, error=0) at sql_select.cc:15915
#15 0x00000000007962ed in sub_select (join=0x7f8ec404fca8, join_tab=0x7f8ec4069a28, end_of_records=false) at sql_select.cc:15757
#16 0x000000000079592e in do_select (join=0x7f8ec404fca8, fields=0x7f8ec404fff8, table=0x0, procedure=0x0) at sql_select.cc:15378
#17 0x0000000000776ba5 in JOIN::exec (this=0x7f8ec404fca8) at sql_select.cc:2761
#18 0x000000000077742f in mysql_select (thd=0x2d69cf8, rref_pointer_array=0x7f8ec404ade8, tables=0x7f8ec404b4e8, wild_num=0, fields=..., conds=0x7f8ec405ad08, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200193, result=0x7f8ec404fbb0, unit=0x7f8ec404aed0, select_lex=0x7f8ec404ab90) at sql_select.cc:2982
#19 0x00000000008f5f9d in mysql_derived_fill (thd=0x2d69cf8, lex=0x2d6c1e8, derived=0x7f8ec404ea70) at sql_derived.cc:889
#20 0x00000000008f4bb8 in mysql_handle_single_derived (lex=0x2d6c1e8, derived=0x7f8ec404ea70, phases=96) at sql_derived.cc:176
#21 0x0000000000789017 in st_join_table::preread_init (this=0x7f8ec4065e70) at sql_select.cc:10171
#22 0x0000000000795f1c in sub_select (join=0x7f8ec404f098, join_tab=0x7f8ec4065e70, end_of_records=false) at sql_select.cc:15672
#23 0x000000000085c0db in join_tab_execution_startup (tab=0x7f8ec40654f0) at opt_subselect.cc:4945
#24 0x0000000000796087 in sub_select (join=0x7f8ec404f098, join_tab=0x7f8ec40654f0, end_of_records=false) at sql_select.cc:15705
#25 0x000000000079592e in do_select (join=0x7f8ec404f098, fields=0x2d6c8a0, table=0x0, procedure=0x0) at sql_select.cc:15378
#26 0x0000000000776ba5 in JOIN::exec (this=0x7f8ec404f098) at sql_select.cc:2761

== In 5.5 (both optimized and debug build) the query is interrupted with the following error:

ERROR 1030 (HY000): Got error 140 from storage engine
This error means: "MySQL error code 140: Wrong create options".

Need to try to create a simpler test case in order to include it into the MTR suite.

Patch from Igor:

=== modified file 'sql/sql_select.cc'
— sql/sql_select.cc 2012-10-10 05:21:22 +0000
+++ sql/sql_select.cc 2012-10-20 05:49:13 +0000
@@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys()
JOIN_TAB *tab;
for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES);
tab;

• tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS))
  + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS))
  {

TABLE *table=tab->table;

Simplified test case:

CREATE TABLE t1 (
pk int(10) unsigned NOT NULL AUTO_INCREMENT,
a char(2) DEFAULT NULL,
PRIMARY KEY (pk),
KEY a (a)
) ENGINE=MyISAM;
INSERT INTO t1 (a)
VALUES (NULL),(NULL),(NULL),('AB'),(NULL),('CD'),(NULL),(NULL);
INSERT INTO t1 SELECT NULL, a1.a FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;

CREATE TABLE t2 (
pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
INSERT INTO t2 SELECT NULL FROM t2 a1, t2 a2, t2 a3, t2 a4, t2 a5;

CREATE TABLE t3 (
pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM;
INSERT INTO t3 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
INSERT INTO t3 SELECT NULL FROM t3 a1, t3 a2, t3 a3, t3 a4, t3 a5;

CREATE TABLE t4 (
a char(2) NOT NULL DEFAULT '',
PRIMARY KEY (a)
) ENGINE=MyISAM;
INSERT INTO t4 VALUES ('CD');

set @@tmp_table_size=8192;

SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk)
WHERE
tX.pk IN
(SELECT *
FROM (SELECT DISTINCT tA.pk
FROM t3 AS tA
JOIN t2 AS tB ON (tA.pk = tB.pk)
JOIN t1 AS tC ON (tB.pk = tC.pk)
JOIN t4 AS tD ON tC.a = tD.a) tU)
limit 10;

Pushed to 5.3.11.