I've just upgraded from 10.4 to 10.5 and now some queries results in a server crash.
The issue can be reproduced by creating this simple table and make a query on it. It doesn't matter if the table is empty or not.
CREATE TABLE `test` (
`a` BIGINT(20) NOT NULL,
`b` BIGINT(20) NOT NULL,
`c` BIGINT(20) NOT NULL,
PRIMARY KEY (`a`, `b`, `c`) USING BTREE,
INDEX `KEY_2` (`b`) USING BTREE
) ENGINE=InnoDB;
Then running this query produce the crash:
SELECT count(*) FROM test
WHERE c = 1
AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
currently it is fixed in 10.5 (by patch for MDEV-24953),
workaround, that is mentioned there (to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000)) helps also in this case:
MariaDB [test]> set optimizer_max_sel_arg_weight=100;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.001 sec)
MariaDB [test]> set optimizer_max_sel_arg_weight=16000;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
ERROR 2013 (HY000): Lost connection to MySQL server during query
Alice Sherepa
added a comment - - edited Thanks a lot! I repeated as described on 10.5
10.5 16388f393c63b3e2158db1e
Version: '10.5.9-MariaDB-debug-log'
210226 10:40:13 [ERROR] mysqld got signal 11 ;
Server version: 10.5.9-MariaDB-debug-log
sql/signal_handler.cc:211(handle_fatal_signal)[0x560bdad5c5d8]
sigaction.c:0(__restore_rt)[0x7f0ceb7a13c0]
sql/opt_range.cc:9816(and_all_keys(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18a75d]
sql/opt_range.cc:9875(key_and(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18aca0]
sql/opt_range.cc:10065(key_and_with_limit(RANGE_OPT_PARAM*, unsigned int, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18bf4c]
sql/opt_range.cc:9151(and_range_trees(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, SEL_TREE*))[0x560bdb186cb1]
sql/opt_range.cc:9263(tree_and(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*))[0x560bdb187611]
sql/opt_range.cc:8315(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x560bdb17e57b]
sql/opt_range.cc:2880(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x560bdb15a7ff]
sql/sql_select.cc:4763(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x560bda639220]
sql/sql_select.cc:5494(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560bda640177]
sql/sql_select.cc:2255(JOIN::optimize_inner())[0x560bda61ec4e]
sql/sql_select.cc:1627(JOIN::optimize())[0x560bda61812e]
sql/sql_select.cc:4705(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560bda638925]
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560bda60a358]
sql/sql_parse.cc:6282(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560bda573ac6]
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x560bda5629c1]
sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560bda57edc0]
sql/sql_parse.cc:1892(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560bda554fb9]
sql/sql_parse.cc:1370(do_command(THD*))[0x560bda5518e2]
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x560bda994778]
sql/sql_connect.cc:1314(handle_one_connection)[0x560bda9940dc]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560bdb69fdf9]
nptl/pthread_create.c:478(start_thread)[0x7f0ceb795609]
x86_64/clone.S:97(__GI___clone)[0x7f0ceb369293]
Query (0x62b0000a12a8): SELECT count(*) FROM test
WHERE c = 1
AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
currently it is fixed in 10.5 (by patch for MDEV-24953 ),
workaround, that is mentioned there (to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000)) helps also in this case:
MariaDB [test]> set optimizer_max_sel_arg_weight=100;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.001 sec)
MariaDB [test]> set optimizer_max_sel_arg_weight=16000;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
ERROR 2013 (HY000): Lost connection to MySQL server during query
Thanks a lot! I repeated as described on 10.5
10.5 16388f393c63b3e2158db1e
Version: '10.5.9-MariaDB-debug-log'
210226 10:40:13 [ERROR] mysqld got signal 11 ;
Server version: 10.5.9-MariaDB-debug-log
sql/signal_handler.cc:211(handle_fatal_signal)[0x560bdad5c5d8]
sigaction.c:0(__restore_rt)[0x7f0ceb7a13c0]
sql/opt_range.cc:9816(and_all_keys(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18a75d]
sql/opt_range.cc:9875(key_and(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18aca0]
sql/opt_range.cc:10065(key_and_with_limit(RANGE_OPT_PARAM*, unsigned int, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18bf4c]
sql/opt_range.cc:9151(and_range_trees(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, SEL_TREE*))[0x560bdb186cb1]
sql/opt_range.cc:9263(tree_and(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*))[0x560bdb187611]
sql/opt_range.cc:8315(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x560bdb17e57b]
sql/opt_range.cc:2880(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x560bdb15a7ff]
sql/sql_select.cc:4763(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x560bda639220]
sql/sql_select.cc:5494(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560bda640177]
sql/sql_select.cc:2255(JOIN::optimize_inner())[0x560bda61ec4e]
sql/sql_select.cc:1627(JOIN::optimize())[0x560bda61812e]
sql/sql_select.cc:4705(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560bda638925]
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560bda60a358]
sql/sql_parse.cc:6282(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560bda573ac6]
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x560bda5629c1]
sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560bda57edc0]
sql/sql_parse.cc:1892(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560bda554fb9]
sql/sql_parse.cc:1370(do_command(THD*))[0x560bda5518e2]
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x560bda994778]
sql/sql_connect.cc:1314(handle_one_connection)[0x560bda9940dc]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560bdb69fdf9]
nptl/pthread_create.c:478(start_thread)[0x7f0ceb795609]
x86_64/clone.S:97(__GI___clone)[0x7f0ceb369293]
Query (0x62b0000a12a8): SELECT count(*) FROM test
WHERE c = 1
AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
currently it is fixed in 10.5 (by patch for
MDEV-24953),workaround, that is mentioned there (to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000)) helps also in this case:
MariaDB [test]> set optimizer_max_sel_arg_weight=100;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.001 sec)
MariaDB [test]> set optimizer_max_sel_arg_weight=16000;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
ERROR 2013 (HY000): Lost connection to MySQL server during query