Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
--source include/have_innodb.inc
|
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB; |
INSERT INTO t1 VALUES |
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
|
|
|
CREATE TABLE t2 (b INT) ENGINE=InnoDB; |
INSERT INTO t2 VALUES (100),(200); |
|
|
CREATE TABLE t3 (c INT, d INT, KEY(c)) ENGINE=InnoDB; |
INSERT INTO t3 VALUES (1,1),(2,2); |
|
|
CREATE VIEW v AS SELECT c, d FROM t3 GROUP BY c, d; |
|
|
SELECT * FROM t1 JOIN t2 WHERE (t1.a, t2.b) IN (SELECT * FROM v); |
|
|
# Cleanup
|
DROP VIEW v; |
DROP TABLE t1, t2, t3; |
|
bb-10.6-release fbe68a6a745 |
#3 <signal handler called>
|
#4 st_join_table::choose_best_splitting (this=0x7f3c2407c160, idx=2, remaining_tables=4, spl_pd_boundary=0x7f3c7c2f5d40) at /data/src/bb-10.6-release/sql/opt_split.cc:1048
|
#5 0x000055b653619ba2 in best_access_path (join=0x7f3c24075e88, s=0x7f3c2407c160, remaining_tables=4, join_positions=0x7f3c2407cca8, idx=2, disable_jbuf=true, record_count=30, pos=0x7f3c2407cf38, loose_scan_pos=0x7f3c7c2f6180) at /data/src/bb-10.6-release/sql/sql_select.cc:7898
|
#6 0x000055b6537f56f8 in fix_semijoin_strategies_for_picked_join_order (join=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/opt_subselect.cc:3994
|
#7 0x000055b653622f3d in JOIN::get_best_combination (this=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/sql_select.cc:11183
|
#8 0x000055b653607cc8 in JOIN::optimize_stage2 (this=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/sql_select.cc:2556
|
#9 0x000055b653607b24 in JOIN::optimize_inner (this=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/sql_select.cc:2535
|
#10 0x000055b653605244 in JOIN::optimize (this=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/sql_select.cc:1848
|
#11 0x000055b653611145 in mysql_select (thd=0x7f3c24000dc8, tables=0x7f3c24015d58, fields=..., conds=0x7f3c24018c10, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f3c24075e60, unit=0x7f3c24005138, select_lex=0x7f3c24015740) at /data/src/bb-10.6-release/sql/sql_select.cc:5055
|
#12 0x000055b6535ff9fd in handle_select (thd=0x7f3c24000dc8, lex=0x7f3c24005070, result=0x7f3c24075e60, setup_tables_done_option=0) at /data/src/bb-10.6-release/sql/sql_select.cc:559
|
#13 0x000055b6535bdab1 in execute_sqlcom_select (thd=0x7f3c24000dc8, all_tables=0x7f3c24015d58) at /data/src/bb-10.6-release/sql/sql_parse.cc:6273
|
#14 0x000055b6535b49e5 in mysql_execute_command (thd=0x7f3c24000dc8, is_called_from_prepared_stmt=false) at /data/src/bb-10.6-release/sql/sql_parse.cc:3949
|
#15 0x000055b6535c2a8a in mysql_parse (thd=0x7f3c24000dc8, rawbuf=0x7f3c24015660 "SELECT * FROM t1 JOIN t2 WHERE (t1.a, t2.b) IN (SELECT * FROM v)", length=64, parser_state=0x7f3c7c2f7380) at /data/src/bb-10.6-release/sql/sql_parse.cc:8036
|
#16 0x000055b6535ae56b in dispatch_command (command=COM_QUERY, thd=0x7f3c24000dc8, packet=0x7f3c2400b8f9 "SELECT * FROM t1 JOIN t2 WHERE (t1.a, t2.b) IN (SELECT * FROM v)", packet_length=64, blocking=true) at /data/src/bb-10.6-release/sql/sql_parse.cc:1896
|
#17 0x000055b6535acec4 in do_command (thd=0x7f3c24000dc8, blocking=true) at /data/src/bb-10.6-release/sql/sql_parse.cc:1409
|
#18 0x000055b65377eeba in do_handle_one_connection (connect=0x55b6567779d8, put_in_cache=true) at /data/src/bb-10.6-release/sql/sql_connect.cc:1416
|
#19 0x000055b65377ec2f in handle_one_connection (arg=0x55b656777618) at /data/src/bb-10.6-release/sql/sql_connect.cc:1318
|
#20 0x000055b653cd7572 in pfs_spawn_thread (arg=0x55b6566c7d58) at /data/src/bb-10.6-release/storage/perfschema/pfs.cc:2201
|
#21 0x00007f3c82ea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#22 0x00007f3c82f285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|
The fix for MDEV-31240 is in the branches.
The failure started happening after this commit in 10.4.29:
commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb
|
Author: Igor Babaev
|
Date: Tue May 2 23:17:07 2023 -0700
|
|
|
MDEV-26301 Split optimization refills temporary table too many times
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
- links to
(1 links to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | Server crashes in st_join_table::choose_best_splitting | Server crashes in st_join_table::choose_best_splitting (still) |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.4.30 [ 28912 ] | |
| Fix Version/s | 10.5.21 [ 28913 ] | |
| Fix Version/s | 10.6.14 [ 28914 ] | |
| Fix Version/s | 10.9.7 [ 28916 ] | |
| Fix Version/s | 10.10.5 [ 28917 ] | |
| Fix Version/s | 10.11.4 [ 28918 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue is duplicated by |
| Link | This issue includes MDEV-31440 [ MDEV-31440 ] |
| Link | This issue relates to MDEV-31440 [ MDEV-31440 ] |
| Link | This issue relates to MDEV-31440 [ MDEV-31440 ] |
| Link |
This issue is duplicated by |
| Link | This issue relates to MDEV-31818 [ MDEV-31818 ] |
Hello, I just had an issue very similar - with a query on MariaDB 10.6.14
Environement : Debian 11 + jemalloc + Mariadb10.6.16
Thread pointer: 0x7fd601c97818
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 = 0x7fd6a2f7d4d8 thread_stack 0x30000
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x55888e0e75ae]
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x55888dbb03d5]
sigaction.c:0(__restore_rt)[0x7fdb2333c140]
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x55888db2fcf4]
/usr/sbin/mariadbd(Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6+0x14f)[0x55888d9b187f]
/usr/sbin/mariadbd(+0x7f561f)[0x55888d9b561f]
/usr/sbin/mariadbd(+0x7f58de)[0x55888d9b58de]
/usr/sbin/mariadbd(+0x7f58de)[0x55888d9b58de]
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x55888d9b6434]
/usr/sbin/mariadbd(+0x8206c1)[0x55888d9e06c1]
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12ec)[0x55888d9e53ac]
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x55888d9e59c0]
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xb3)[0x55888d9e5a93]
/usr/sbin/mariadbd(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14f)[0x55888d9e629f]
/usr/sbin/mariadbd(+0x7abfe1)[0x55888d96bfe1]
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x4607)[0x55888d97b157]
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x55888d97c747]
/usr/sbin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x156d)[0x55888d97ef4d]
/usr/sbin/mariadbd(_Z10do_commandP3THDb+0x138)[0x55888d980ae8]
/usr/sbin/mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3af)[0x55888da8f61f]
/usr/sbin/mariadbd(handle_one_connection+0x5d)[0x55888da8f96d]
/usr/sbin/mariadbd(+0xc3c322)[0x55888ddfc322]
nptl/pthread_create.c:478(start_thread)[0x7fdb23330ea7]
x86_64/clone.S:97(_GI__clone)[0x7fdb22f37a2f]
| Link |
This issue relates to |
| Link | This issue includes MDEV-31440 [ MDEV-31440 ] |
| Link |
This issue relates to |
| Remote Link | This issue links to "Commit that seems to have caused this (ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb) (Web Link)" [ 36335 ] |
See https://jira.mariadb.org/browse/MDEV-31240?focusedCommentId=278585#comment-278585 for a viable mitigation for this crash and related ones in st_join_table::choose_best_splitting.
The mitigation is:
SET SESSION optimizer_switch='split_materialized=off';
|
The fix for this seems to be incomplete. I have a recurring crash with near identical stacktrace on 10.4.34:
241122 15:11:49 [ERROR] mysqld got signal 11 ;
|
Sorry, we probably made a mistake, and this is a bug.
|
Your assistance in bug reporting will enable us to fix this for the next release.
|
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: 10.4.34-MariaDB-log source revision: 16394f1aa1b4097f897b8ab01ea2064726cca059
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=105
|
max_threads=10002
|
thread_count=119
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1312642168 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
Thread pointer: 0x7f8dd800c168
|
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 = 0x7f968408ecb0 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x557c0145fc4e]
|
/usr/sbin/mysqld(handle_fatal_signal+0x2ff)[0x557c00eb2d2f]
|
sigaction.c:0(__restore_rt)[0x7f974fbc75d0]
|
/usr/sbin/mysqld(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0xb92)[0x557c00e288f2]
|
/usr/sbin/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x136)[0x557c00d08ea6]
|
/usr/sbin/mysqld(+0x7475fe)[0x557c00d0c5fe]
|
/usr/sbin/mysqld(_Z11choose_planP4JOINy+0x25c)[0x557c00d0d26c]
|
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x2f5f)[0x557c00d149ef]
|
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x9d)[0x557c00d1526d]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf1)[0x557c00d15381]
|
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1e7)[0x557c00d15fa7]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5c13)[0x557c00cb8cb3]
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x557c00c14386]
|
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x1a5)[0x557c00c1cbc5]
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x64c)[0x557c00c1d6dc]
|
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0xa04)[0x557c00c180f4]
|
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x789)[0x557c00c19aa9]
|
/usr/sbin/mysqld(+0x6e577a)[0x557c00caa77a]
|
/usr/sbin/mysqld(+0x6e7b46)[0x557c00cacb46]
|
/usr/sbin/mysqld(_ZN12Sql_cmd_call7executeEP3THD+0x90)[0x557c00cad380]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1a2c)[0x557c00cb4acc]
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x557c00c14386]
|
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x1a5)[0x557c00c1cbc5]
|
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x64c)[0x557c00c1d6dc]
|
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0xa04)[0x557c00c180f4]
|
/usr/sbin/mysqld(_ZN7sp_head15execute_triggerEP3THDPK25st_mysql_const_lex_stringS4_P13st_grant_info+0x1c8)[0x557c00c190f8]
|
/usr/sbin/mysqld(_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0x105)[0x557c00d58125]
|
/usr/sbin/mysqld(_ZN21Update_rows_log_event11do_exec_rowEP14rpl_group_info+0x38e)[0x557c00fbffce]
|
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEP14rpl_group_info+0x25c)[0x557c00fb33cc]
|
/usr/sbin/mysqld(+0x640b08)[0x557c00c05b08]
|
/usr/sbin/mysqld(+0x82b68e)[0x557c00df068e]
|
/usr/sbin/mysqld(handle_rpl_parallel_thread+0x721)[0x557c00df2fe1]
|
/usr/sbin/mysqld(+0xb6d30d)[0x557c0113230d]
|
pthread_create.c:0(start_thread)[0x7f974fbbfdd5]
|
/lib64/libc.so.6(clone+0x6d)[0x7f974f0dbead]
|
I have also experienced this crash in versions at least as far back as 10.4.18, so this was NOT introduced in 10.4.29 as suggested above.
The key part is here:
#4 st_join_table::choose_best_splitting (this=0x7f3c2407c160, idx=2, remaining_tables=4, spl_pd_boundary=0x7f3c7c2f5d40) at /data/src/bb-10.6-release/sql/opt_split.cc:1048#5 0x000055b653619ba2 in best_access_path (join=0x7f3c24075e88, s=0x7f3c2407c160, remaining_tables=4, join_positions=0x7f3c2407cca8, idx=2, disable_jbuf=true, record_count=30, pos=0x7f3c2407cf38, loose_scan_pos=0x7f3c7c2f6180) at /data/src/bb-10.6-release/sql/sql_select.cc:7898#6 0x000055b6537f56f8 in fix_semijoin_strategies_for_picked_join_order (join=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/opt_subselect.cc:3994#7 0x000055b653622f3d in JOIN::get_best_combination (this=0x7f3c24075e88) at /data/src/bb-10.6-release/sql/sql_select.cc:11183choose_best_splitting() is called from fix_semijoin_strategies_for_picked_join_order().
The problem is that choose_best_splitting() assumes that the current join prefix is in join->positions, while at JOIN::get_best_combination()/fix_semijoin_strategies_for_picked_join_order() stage it is in join->best_positions.