[MDEV-4465] Reproducible crash (mysqld got signal 11) in multi_delete::initialize_tables with semijoin+materialization Created: 2013-05-01  Updated: 2013-05-02  Resolved: 2013-05-02

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2, 5.5.30, 5.5.29-galera, 5.3.12
Fix Version/s: 10.0.3, 5.5.31, 5.3.13

Type: Bug Priority: Major
Reporter: Israel Tsadok Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: crash
Environment:

ubuntu 12.04 percise

Attachments: File crash.sql    

 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



 Comments   
Comment by Elena Stepanova [ 2013-05-01 ]

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.

Comment by Elena Stepanova [ 2013-05-01 ]

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

  1. End of test case
Comment by Sergei Petrunia [ 2013-05-02 ]

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.

Comment by Sergei Petrunia [ 2013-05-02 ]

Fixed in 5.5. Thanks for bug report.

Generated at Thu Feb 08 06:56:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.