[MDEV-31233] Server crashes in multi_update::prepare upon 2nd execution of PS with multi-table update Created: 2023-05-10  Updated: 2024-02-08

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 11.1, 11.2, 11.3
Fix Version/s: 11.1, 11.2, 11.3, 11.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: regression


 Description   

It can be the same problem as MDEV-31150, but I cannot say for sure, and at the time of filing this there is no yet a patch for MDEV-31150 to check if it helps.

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (3),(4);
 
PREPARE stmt FROM 'UPDATE t1 JOIN t2 SET t1.a = NULL ORDER BY t2.b LIMIT 1';
EXECUTE stmt;
EXECUTE stmt;
 
# Cleanup
DROP TABLE t1, t2;

11.1 4e5b771e non-ASAN

#2  <signal handler called>
#3  0x000055c86dea7e4e in multi_update::prepare (this=0x7f6d14010bb0, not_used_values=..., lex_unit=<optimized out>) at /data/src/11.1/sql/sql_update.cc:1859
#4  0x000055c86de3b234 in JOIN::prepare (this=this@entry=0x7f6d14010cd0, tables_init=tables_init@entry=0x7f6d1403a400, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=skip_order_by@entry=false, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /data/src/11.1/sql/sql_select.cc:1775
#5  0x000055c86dead7c2 in Sql_cmd_update::prepare_inner (this=0x7f6d1403bc38, thd=0x7f6d14000c68) at /data/src/11.1/sql/sql_update.cc:2992
#6  0x000055c86de09944 in Sql_cmd_dml::prepare (this=0x7f6d1403bc38, thd=0x7f6d14000c68) at /data/src/11.1/sql/sql_select.cc:32467
#7  0x000055c86de0d651 in Sql_cmd_dml::execute (this=0x7f6d1403bc38, thd=0x7f6d14000c68) at /data/src/11.1/sql/sql_select.cc:32520
#8  0x000055c86ddd73d6 in mysql_execute_command (thd=0x7f6d14000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=true) at /data/src/11.1/sql/sql_parse.cc:4393
#9  0x000055c86ddfbaba in Prepared_statement::execute (this=this@entry=0x7f6d1419d8a8, expanded_query=expanded_query@entry=0x7f6d2495bed0, open_cursor=open_cursor@entry=false) at /data/src/11.1/sql/sql_prepare.cc:4992
#10 0x000055c86ddfbc55 in Prepared_statement::execute_loop (this=this@entry=0x7f6d1419d8a8, expanded_query=expanded_query@entry=0x7f6d2495bed0, open_cursor=open_cursor@entry=false, packet=packet@entry=0x0, packet_end=packet_end@entry=0x0) at /data/src/11.1/sql/sql_prepare.cc:4415
#11 0x000055c86ddfbf96 in mysql_sql_stmt_execute (thd=thd@entry=0x7f6d14000c68) at /data/src/11.1/sql/sql_prepare.cc:3456
#12 0x000055c86ddd870b in mysql_execute_command (thd=thd@entry=0x7f6d14000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/11.1/sql/sql_parse.cc:3960
#13 0x000055c86dddaff5 in mysql_parse (thd=0x7f6d14000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /data/src/11.1/sql/sql_parse.cc:7760
#14 0x000055c86dddd2d5 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f6d14000c68, packet=packet@entry=0x7f6d14008669 "EXECUTE stmt", packet_length=packet_length@entry=12, blocking=blocking@entry=true) at /data/src/11.1/sql/sql_parse.cc:1989
#15 0x000055c86ddde5d7 in do_command (thd=0x7f6d14000c68, blocking=blocking@entry=true) at /data/src/11.1/sql/sql_parse.cc:1405
#16 0x000055c86deee8e7 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55c86fdf8b68, put_in_cache=put_in_cache@entry=true) at /data/src/11.1/sql/sql_connect.cc:1416
#17 0x000055c86deeec7d in handle_one_connection (arg=arg@entry=0x55c86fdf8b68) at /data/src/11.1/sql/sql_connect.cc:1318
#18 0x000055c86e1feb07 in pfs_spawn_thread (arg=0x55c86fdb0218) at /data/src/11.1/storage/perfschema/pfs.cc:2201
#19 0x00007f6d29ea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#20 0x00007f6d29f285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

11.1 4e5b771e ASAN

==1867808==ERROR: AddressSanitizer: use-after-poison on address 0x62900010d1e0 at pc 0x55b7f521ed76 bp 0x7f462d5ce7d0 sp 0x7f462d5ce7c8
READ of size 8 at 0x62900010d1e0 thread T14
    #0 0x55b7f521ed75 in base_list_iterator::next() /data/src/11.1/sql/sql_list.h:431
    #1 0x55b7f523765e in List_iterator<Item>::operator++(int) /data/src/11.1/sql/sql_list.h:596
    #2 0x55b7f53a3b53 in setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool) /data/src/11.1/sql/sql_base.cc:8029
    #3 0x55b7f58c39ec in setup_fields_with_no_wrap(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, bool) /data/src/11.1/sql/sql_base.h:387
    #4 0x55b7f58b35f1 in Multiupdate_prelocking_strategy::handle_end(THD*) /data/src/11.1/sql/sql_update.cc:1567
    #5 0x55b7f538ff0c in open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) /data/src/11.1/sql/sql_base.cc:4741
    #6 0x55b7f537682f in open_tables /data/src/11.1/sql/sql_base.h:267
    #7 0x55b7f539564f in open_tables_for_query(THD*, TABLE_LIST*, unsigned int*, unsigned int, DML_prelocking_strategy*) /data/src/11.1/sql/sql_base.cc:5740
    #8 0x55b7f5712e46 in Sql_cmd_dml::prepare(THD*) /data/src/11.1/sql/sql_select.cc:32458
    #9 0x55b7f57131a3 in Sql_cmd_dml::execute(THD*) /data/src/11.1/sql/sql_select.cc:32520
    #10 0x55b7f553beac in mysql_execute_command(THD*, bool) /data/src/11.1/sql/sql_parse.cc:4393
    #11 0x55b7f55e3a51 in Prepared_statement::execute(String*, bool) /data/src/11.1/sql/sql_prepare.cc:4992
    #12 0x55b7f55dece8 in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /data/src/11.1/sql/sql_prepare.cc:4415
    #13 0x55b7f55d84b3 in mysql_sql_stmt_execute(THD*) /data/src/11.1/sql/sql_prepare.cc:3456
    #14 0x55b7f5539507 in mysql_execute_command(THD*, bool) /data/src/11.1/sql/sql_parse.cc:3960
    #15 0x55b7f5553339 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /data/src/11.1/sql/sql_parse.cc:7760
    #16 0x55b7f552bab0 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /data/src/11.1/sql/sql_parse.cc:1892
    #17 0x55b7f55287ee in do_command(THD*, bool) /data/src/11.1/sql/sql_parse.cc:1405
    #18 0x55b7f59e06e1 in do_handle_one_connection(CONNECT*, bool) /data/src/11.1/sql/sql_connect.cc:1416
    #19 0x55b7f59e00a2 in handle_one_connection /data/src/11.1/sql/sql_connect.cc:1318
    #20 0x55b7f65d9a3b in pfs_spawn_thread /data/src/11.1/storage/perfschema/pfs.cc:2201
    #21 0x7f463c0a7fd3 in start_thread nptl/pthread_create.c:442
    #22 0x7f463c1285bb in clone3 ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
 
0x62900010d1e0 is located 16352 bytes inside of 16400-byte region [0x629000109200,0x62900010d210)
allocated by thread T14 here:
    #0 0x7f463cab89cf in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cpp:69
    #1 0x55b7f71a78af in my_malloc /data/src/11.1/mysys/my_malloc.c:91
    #2 0x55b7f7182ec3 in root_alloc /data/src/11.1/mysys/my_alloc.c:71
    #3 0x55b7f7183db8 in reset_root_defaults /data/src/11.1/mysys/my_alloc.c:248
    #4 0x55b7f53eb94a in THD::init_for_queries() /data/src/11.1/sql/sql_class.cc:1386
    #5 0x55b7f59df986 in prepare_new_connection_state(THD*) /data/src/11.1/sql/sql_connect.cc:1245
    #6 0x55b7f59e0123 in thd_prepare_connection(THD*) /data/src/11.1/sql/sql_connect.cc:1339
    #7 0x55b7f59e063b in do_handle_one_connection(CONNECT*, bool) /data/src/11.1/sql/sql_connect.cc:1406
    #8 0x55b7f59e00a2 in handle_one_connection /data/src/11.1/sql/sql_connect.cc:1318
    #9 0x55b7f65d9a3b in pfs_spawn_thread /data/src/11.1/storage/perfschema/pfs.cc:2201
    #10 0x7f463c0a7fd3 in start_thread nptl/pthread_create.c:442
 
Thread T14 created by T0 here:
    #0 0x7f463ca49726 in __interceptor_pthread_create ../../../../src/libsanitizer/asan/asan_interceptors.cpp:207
    #1 0x55b7f65d5776 in my_thread_create /data/src/11.1/storage/perfschema/my_thread.h:52
    #2 0x55b7f65d9e2a in pfs_spawn_thread_v1 /data/src/11.1/storage/perfschema/pfs.cc:2252
    #3 0x55b7f516d77a in inline_mysql_thread_create /data/src/11.1/include/mysql/psi/mysql_thread.h:1139
    #4 0x55b7f51856c0 in create_thread_to_handle_connection(CONNECT*) /data/src/11.1/sql/mysqld.cc:6134
    #5 0x55b7f5185cd1 in create_new_thread(CONNECT*) /data/src/11.1/sql/mysqld.cc:6193
    #6 0x55b7f5185fbc in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /data/src/11.1/sql/mysqld.cc:6255
    #7 0x55b7f5186940 in handle_connections_sockets() /data/src/11.1/sql/mysqld.cc:6379
    #8 0x55b7f5184f3d in mysqld_main(int, char**) /data/src/11.1/sql/mysqld.cc:6029
    #9 0x55b7f516c8e8 in main /data/src/11.1/sql/main.cc:34
    #10 0x7f463c046189 in __libc_start_call_main ../sysdeps/nptl/libc_start_call_main.h:58
 
SUMMARY: AddressSanitizer: use-after-poison /data/src/11.1/sql/sql_list.h:431 in base_list_iterator::next()
Shadow bytes around the buggy address:
  0x0c52800199e0: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
  0x0c52800199f0: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
  0x0c5280019a00: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
  0x0c5280019a10: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
  0x0c5280019a20: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7
=>0x0c5280019a30: f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7 f7[f7]f7 f7 f7
  0x0c5280019a40: f7 f7 fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c5280019a50: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c5280019a60: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c5280019a70: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
  0x0c5280019a80: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
Shadow byte legend (one shadow byte represents 8 application bytes):
  Addressable:           00
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
  Freed heap region:       fd
  Stack left redzone:      f1
  Stack mid redzone:       f2
  Stack right redzone:     f3
  Stack after return:      f5
  Stack use after scope:   f8
  Global redzone:          f9
  Global init order:       f6
  Poisoned by user:        f7
  Container overflow:      fc
  Array cookie:            ac
  Intra object redzone:    bb
  ASan internal:           fe
  Left alloca redzone:     ca
  Right alloca redzone:    cb
==1867808==ABORTING
230510 14:48:37 [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 https://mariadb.com/kb/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: 11.1.0-MariaDB-debug-log source revision: 4e5b771e980edfdad5c5414aa62c81d409d585a4
key_buffer_size=1048576
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 = 63925 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
Thread pointer: 0x62b00017a218
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 = 0x7f462d5d1c10 thread_stack 0x100000
sanitizer_common/sanitizer_common_interceptors.inc:4277(__interceptor_backtrace.part.0)[0x7f463ca51f31]
mysys/stacktrace.c:215(my_print_stacktrace)[0x55b7f71b89cc]
sql/signal_handler.cc:238(handle_fatal_signal)[0x55b7f5e28d5c]
libc_sigaction.c:0(__restore_rt)[0x7f463c05af90]
nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7f463c0a9ccc]
posix/raise.c:27(__GI_raise)[0x7f463c05aef2]
stdlib/abort.c:81(__GI_abort)[0x7f463c045472]
sanitizer_common/sanitizer_posix_libcdep.cpp:137(__sanitizer::Abort())[0x7f463cad650f]
sanitizer_common/sanitizer_termination.cpp:59(__sanitizer::Die())[0x7f463cae2ba1]
asan/asan_report.cpp:190(__asan::ScopedInErrorReport::~ScopedInErrorReport())[0x7f463cac1f5e]
asan/asan_report.cpp:479(__asan::ReportGenericError(unsigned long, unsigned long, unsigned long, unsigned long, bool, unsigned long, unsigned int, bool))[0x7f463cac14c6]
asan/asan_rtl.cpp:123(__asan_report_load8)[0x7f463cac25ac]
sql/sql_list.h:431(base_list_iterator::next())[0x55b7f521ed76]
sql/sql_list.h:596(List_iterator<Item>::operator++(int))[0x55b7f523765f]
sql/sql_base.cc:8029(setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, List<Item>*, bool))[0x55b7f53a3b54]
sql/sql_base.h:387(setup_fields_with_no_wrap(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_column_usage, List<Item>*, bool))[0x55b7f58c39ed]
sql/sql_update.cc:1567(Multiupdate_prelocking_strategy::handle_end(THD*))[0x55b7f58b35f2]
sql/sql_base.cc:4741(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x55b7f538ff0d]
sql/sql_base.h:269(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x55b7f5376830]
sql/sql_base.cc:5740(open_tables_for_query(THD*, TABLE_LIST*, unsigned int*, unsigned int, DML_prelocking_strategy*))[0x55b7f5395650]
sql/sql_select.cc:32458(Sql_cmd_dml::prepare(THD*))[0x55b7f5712e47]
sql/sql_select.cc:32520(Sql_cmd_dml::execute(THD*))[0x55b7f57131a4]
sql/sql_parse.cc:4393(mysql_execute_command(THD*, bool))[0x55b7f553bead]
sql/sql_prepare.cc:4992(Prepared_statement::execute(String*, bool))[0x55b7f55e3a52]
sql/sql_prepare.cc:4415(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55b7f55dece9]
sql/sql_prepare.cc:3457(mysql_sql_stmt_execute(THD*))[0x55b7f55d84b4]
sql/sql_parse.cc:3961(mysql_execute_command(THD*, bool))[0x55b7f5539508]
sql/sql_parse.cc:7760(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55b7f555333a]
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55b7f552bab1]
sql/sql_parse.cc:1405(do_command(THD*, bool))[0x55b7f55287ef]
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55b7f59e06e2]
sql/sql_connect.cc:1320(handle_one_connection)[0x55b7f59e00a3]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55b7f65d9a3c]
nptl/pthread_create.c:442(start_thread)[0x7f463c0a7fd4]
x86_64/clone3.S:83(clone3)[0x7f463c1285bc]
 
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x6290001092d0): UPDATE t1 JOIN t2 SET t1.a = NULL ORDER BY t2.b LIMIT 1
 
Connection ID (thread ID): 4
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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
 
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /dev/shm/var_auto_maU9/mysqld.1/data
Resource Limits:
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             385793               385793               processes 
Max open files            1024                 1024                 files     
Max locked memory         12649951232          12649951232          bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       385793               385793               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        

Reproducible with at least MyISAM, InnoDB, Aria. Not reproducible on 11.0. I didn't bisect this one, as I assume it would anyway point at the group of commits related to MDEV-28883 / MDEV-7487.


Generated at Thu Feb 08 10:22:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.