[MDEV-8938] Server Crash on Update with joins Created: 2015-10-13  Updated: 2015-11-06  Resolved: 2015-11-06

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 5.5.45, 10.0.21, 10.1.7, 5.5, 10.0, 10.1
Fix Version/s: 5.5.47

Type: Bug Priority: Major
Reporter: Richard Lawley Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 1
Labels: crash
Environment:

CentOS 7.1 64-bit, running inside VirtualBox

Sprint: 10.1.9-2

 Description   

I've seen there are other bugs which have similar titles, but every one has been marked as closed, and since I've verified this with the 3 latest versions available for my distribution, I'm reporting it as a new bug. This was found trying to run CloudStack.

Whenever the following query is executed, the server crashes:

UPDATE `cloud`.`configuration` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `cloud`.`configuration` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";

I've cut the database in question down to the absolute minimum:

CREATE TABLE `configuration` (
 
  `name` varchar(255) NOT NULL,
 
  `value` varchar(4095) DEFAULT NULL,
 
  PRIMARY KEY (`name`)
 
);

Tested on a clean CentOS7.1 64bit install (running under VirtualBox, provisioned with vagrant using bento/centos-7.1)

The contents of the error log are as follows:

151013 11:00:25 [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.5.45-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466712 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7fe313239000
 
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 = 0x7fe32f557d80 thread_stack 0x48000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0xac241e]
 
/usr/sbin/mysqld(handle_fatal_signal+0x390)[0x6e20f0]
 
/lib64/libpthread.so.0(+0xf130)[0x7fe32f1a2130]
 
/usr/sbin/mysqld[0x5ceb80]
 
/usr/sbin/mysqld[0x5ced80]
 
/usr/sbin/mysqld[0x5d137a]
 
/usr/sbin/mysqld[0x5d3a6b]
 
/usr/sbin/mysqld[0x5ec6d7]
 
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x6ed)[0x5f0d8d]
 
/usr/sbin/mysqld(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x90)[0x59ada0]
 
/usr/sbin/mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_+0x37c)[0x631e9c]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3c0d)[0x5aa2fd]
 
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x11e)[0x5ad00e]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1729)[0x5aef69]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x20b)[0x6655bb]
 
/usr/sbin/mysqld(handle_one_connection+0x43)[0x665683]
 
/lib64/libpthread.so.0(+0x7df5)[0x7fe32f19adf5]
 
/lib64/libc.so.6(clone+0x6d)[0x7fe32da181ad]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fe305420018): is an invalid pointer
 
Connection ID (thread ID): 3
 
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,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
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.



 Comments   
Comment by Elena Stepanova [ 2015-10-17 ]

Thanks for the report and the test case.

I think it might be the same issue as MDEV-8701, but it definitely needs checking after MDEV-8701 is fixed.

Stack trace from 5.5 commit f804b74fd498bce2d527008146b5a0288580d75c

 
#3  <signal handler called>
 
#4  0x00000000005c3ebc in Bitmap<64u>::merge (this=0x188, map2=...) at 5.5/sql/sql_bitmap.h:157
 
#5  0x0000000000651179 in add_key_field (join=0x7fac350b4628, key_fields=0x7fac5f0c5348, and_level=0, cond=0x7fac35137510, field=0x7fac35073590, eq_func=true, value=0x7fac5f0c5208, num_values=1, usable_tables=18446744073709551615, sargables=0x7fac5f0c54b0) at 5.5/sql/sql_select.cc:4159
 
#6  0x000000000065246d in add_key_fields (join=0x7fac350b4628, key_fields=0x7fac5f0c5348, and_level=0x7fac5f0c5338, cond=0x7fac35137510, usable_tables=18446744073709551615, sargables=0x7fac5f0c54b0) at 5.5/sql/sql_select.cc:4545
 
#7  0x000000000065379c in update_ref_and_keys (thd=0x7fac3b794060, keyuse=0x7fac350b4940, join_tab=0x7fac351377b0, tables=1, cond=0x7fac35137510, normal_tables=18446744073709551615, select_lex=0x7fac3504ab10, sargables=0x7fac5f0c54b0) at 5.5/sql/sql_select.cc:4970
 
#8  0x000000000064e90f in make_join_statistics (join=0x7fac350b4628, tables_list=..., conds=0x7fac35137510, keyuse_array=0x7fac350b4940) at 5.5/sql/sql_select.cc:3372
 
#9  0x0000000000646cd3 in JOIN::optimize (this=0x7fac350b4628) at 5.5/sql/sql_select.cc:1227
 
#10 0x000000000060a615 in st_select_lex::optimize_unflattened_subqueries (this=0x7fac3b797a70, const_only=false) at 5.5/sql/sql_lex.cc:3507
 
#11 0x00000000006cecf6 in mysql_update (thd=0x7fac3b794060, table_list=0x7fac3504a338, fields=..., values=..., conds=0x7fac350b3710, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fac5f0c5e10, updated_return=0x7fac5f0c5eb0) at 5.5/sql/sql_update.cc:371
 
#12 0x000000000061631e in mysql_execute_command (thd=0x7fac3b794060) at 5.5/sql/sql_parse.cc:2849
 
#13 0x000000000061eb36 in mysql_parse (thd=0x7fac3b794060, rawbuf=0x7fac3504a078 "UPDATE `configuration` SET value = CONCAT(\"*.\",(SELECT `temptable`.`value` FROM (SELECT * FROM `configuration` WHERE `name`=\"consoleproxy.url.domain\") AS `temptable` WHERE `temptable`.`name`=\"consolep"..., length=257, parser_state=0x7fac5f0c6610) at 5.5/sql/sql_parse.cc:5911
 
#14 0x0000000000611f1f in dispatch_command (command=COM_QUERY, thd=0x7fac3b794060, packet=0x7fac3b486061 "UPDATE `configuration` SET value = CONCAT(\"*.\",(SELECT `temptable`.`value` FROM (SELECT * FROM `configuration` WHERE `name`=\"consoleproxy.url.domain\") AS `temptable` WHERE `temptable`.`name`=\"consolep"..., packet_length=257) at 5.5/sql/sql_parse.cc:1079
 
#15 0x0000000000611070 in do_command (thd=0x7fac3b794060) at 5.5/sql/sql_parse.cc:793
 
#16 0x000000000071e8b5 in do_handle_one_connection (thd_arg=0x7fac3b794060) at 5.5/sql/sql_connect.cc:1269
 
#17 0x000000000071e642 in handle_one_connection (arg=0x7fac3b794060) at 5.5/sql/sql_connect.cc:1185
 
#18 0x00007fac5e076e9a in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#19 0x00007fac5d7a3cbd in clone () from /lib/x86_64-linux-gnu/libc.so.6
 
#20 0x0000000000000000 in ?? ()

Comment by Marc Langevin [ 2015-10-26 ]

Hi,
We are also facing a similar situation with an update using a subquery causing a server crash. I am providing the test case below. The problem did not exist in 5.5.44 but is present in both 5.5.45 and 5.5.46.
Test case:

CREATE TABLE `configuration` (
 
  `name` varchar(255) NOT NULL,
 
  `value` varchar(4095) DEFAULT NULL,
 
  PRIMARY KEY (`name`)
 
);
 
 
 
create table hostconfig (
 
  `name` varchar(255) NOT NULL,
 
  `value` varchar(4095) DEFAULT NULL,
 
  PRIMARY KEY (`name`)
 
);
 
 
 
UPDATE configuration
 
SET value = (SELECT value FROM hostconfig WHERE `name`= configuration.name) 
WHERE value is null ;

Stack Trace:

151022 10:38:45 [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.5.46-MariaDB-log
 
key_buffer_size=134217728
 
read_buffer_size=4194304
 
max_used_connections=2
 
max_threads=52
 
thread_count=2
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 770958 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7fb2becfc000
 
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 = 0x7fb2b0337e30 thread_stack 0x48000
 
mysys/stacktrace.c:247(my_print_stacktrace)[0xaf83be]
 
sql/signal_handler.cc:153(handle_fatal_signal)[0x6dd3ec]
 
/lib64/libpthread.so.0[0x3935c0f790]
 
sql/sql_list.h:206(base_list::push_back(void*))[0x702b08]
 
sql/item.cc:5213(Item_field::fix_fields(THD*, Item**))[0x704b09]
 
sql/item_func.cc:204(Item_func::fix_fields(THD*, Item**))[0x73d1d4]
 
sql/sql_base.cc:8953(setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**))[0x5364d4]
 
sql/sql_lex.h:1051(st_select_lex::set_non_agg_field_used(bool))[0x5ade31]
 
sql/item_subselect.cc:3020(subselect_single_select_engine::prepare())[0x77236b]
 
sql/item_subselect.cc:245(Item_subselect::fix_fields(THD*, Item**))[0x770d3d]
 
sql/sql_base.cc:8227(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool))[0x534039]
 
sql/sql_update.cc:364(mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, enum_duplicates, bool, unsigne
 
d long long*, unsigned long long*))[0x60df91]
 
sql/sql_parse.cc:2852(mysql_execute_command(THD*))[0x586057]
 
sql/sql_parse.cc:5914(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x587e11]
 
sql/sql_parse.cc:1081(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x589db8]
 
sql/sql_parse.cc:793(do_command(THD*))[0x58a373]
 
sql/sql_connect.cc:1269(do_handle_one_connection(THD*))[0x645d13]
 
sql/sql_connect.cc:1187(handle_one_connection)[0x645e5c]
 
/lib64/libpthread.so.0[0x3935c07a51]
 
/lib64/libc.so.6(clone+0x6d)[0x39358e893d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fb2b1a08018): UPDATE configuration SET value = (SELECT value FROM hostconfig WHERE `name`= configuration.name)  WHERE value is null
 
Connection ID (thread ID): 3
 
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,engine_condition_pus
 
hdown=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_matc
 
h_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_c
 
ache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Comment by Oleksandr Byelkin [ 2015-11-06 ]

the same as MDEV-8701

Comment by Oleksandr Byelkin [ 2015-11-06 ]

I downported fix from 10.0

Generated at Thu Feb 08 07:30:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.