Details
Major
Description
The attached sql file causes MariaDB to crash, leaving the following in syslog:
mysqld: Server version: 5.5.29-MariaDB-mariadb1~precise-log mysqld: key_buffer_size=134217728 mysqld: read_buffer_size=2097152 mysqld: max_used_connections=1 mysqld: max_threads=102 mysqld: thread_count=1 mysqld: It is possible that mysqld could use up to mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 759225 K bytes of memory mysqld: Hope that's ok; if not, decrease some variables in the equation. mysqld: mysqld: Thread pointer: 0x0x7ff296d2bfd0 mysqld: Attempting backtrace. You can use the following information to find out mysqld: where mysqld died. If you see no messages after this, something went mysqld: terribly wrong... mysqld: stack_bottom = 0x7ff2920bee40 thread_stack 0x48000 mysqld: ??:0(??)[0x7ff292a542fb] mysqld: ??:0(??)[0x7ff292679aa1] mysqld: ??:0(??)[0x7ff290eedcb0] mysqld: ??:0(??)[0x7ff292794ccc] mysqld: ??:0(??)[0x7ff2925716ed] mysqld: ??:0(??)[0x7ff29257617b] mysqld: ??:0(??)[0x7ff29252fa9b] mysqld: ??:0(??)[0x7ff29252fcf7] mysqld: ??:0(??)[0x7ff29252ffdf] mysqld: ??:0(??)[0x7ff29253204f] mysqld: ??:0(??)[0x7ff2925326c3] mysqld: ??:0(??)[0x7ff2925eee63] mysqld: ??:0(??)[0x7ff2925eef61] mysqld: ??:0(??)[0x7ff290ee5e9a] mysqld: ??:0(??)[0x7ff290616ccd] mysqld: mysqld: Trying to get some variables. mysqld: Some pointers may be invalid and cause the dump to abort. mysqld: Query (0x7ff2500699e8): is an invalid pointer mysqld: Connection ID (thread ID): 34 mysqld: Status: NOT_KILLED mysqld: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
A slightly modified test case is below. The attached one is also fine, and it's more realistic, in the one below I just removed indexes and fields not needed to reproduce the crash. You can use whichever you prefer.
Reproducible both with InnoDB (as in the original test case) and with MyISAM.
Minimal optimizer_switch: materialization=on,semijoin=on
Multi-table delete syntax is important here. Even although I removed from DELETE one of the two tables present in the original test case, still, the crash only happens when we do
delete b from b where ...
but not if we convert it into
delete from b where ...
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on';
CREATE TABLE `a` (
`id` int(11) NOT NULL
);
CREATE TABLE `b` (
`id` int(11) NOT NULL,
`a_id` int(11) DEFAULT NULL
);
insert into a values (1), (2), (3);
insert into b values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 3), (7, 3);
delete b from b where a_id in (select * from (select a.id from a limit 2) as x);
- End of test case
In the above testcase, the query can be simplified:
delete b from b where a_id in (select a.id from a);
It is essential that SJ-Materialization is enabled, and the query is a multi-table DELETE.
Fixed in 5.5. Thanks for bug report.
Hi,
Thanks for the report, hope you don't mind that I modified the summary just a little bit, to make it more easily searchable in future.