[MDEV-26446] Crash on st_join_table::save_explain_data - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Incomplete
Affects Version/s: 10.4.21, 10.5.12, 10.6.4, 10.4(EOL), 10.5, 10.6
Fix Version/s: N/A
Component/s: Server
Labels:
None

Description

Simple SELECT and also just EXPLAIN crash MariaDB server in certain data condition.
Example crash trace:

stack_bottom = 0x7f982069adb0 thread_stack 0x49000

mysys/stacktrace.c:213(my_print_stacktrace)[0x55972f6abc1e]

sql/signal_handler.cc:224(handle_fatal_signal)[0x55972f088aa7]

sigaction.c:0(__restore_rt)[0x7f98364b73c0]

sql/sql_select.cc:26822(st_join_table::save_explain_data(Explain_table_access*, unsigned long long, bool, st_join_table*))[0x55972eecb64f]

sql/sql_select.cc:27378(JOIN::save_explain_data_intern(Explain_query*, bool, bool, bool, char const*))[0x55972eecd18b]

sql/sql_select.cc:4256(JOIN::save_explain_data(Explain_query*, bool, bool, bool, bool))[0x55972eecd2f0]

sql/sql_select.cc:1617(JOIN::build_explain())[0x55972eecd54c]

sql/sql_select.cc:1674(JOIN::optimize())[0x55972eee2091]

sql/sql_select.cc:4758(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*))[0x55972eee2198]

sql/sql_select.cc:27511(mysql_explain_union(THD*, st_select_lex_unit*, select_result*))[0x55972eee2ff2]

sql/sql_parse.cc:6256(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55972ed45807]

sql/sql_parse.cc:4005(mysql_execute_command(THD*))[0x55972ee87fce]

sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55972ee8a35c]

sql/sql_parse.cc:1951(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55972ee8cd5a]

sql/sql_parse.cc:1375(do_command(THD*))[0x55972ee8e07f]

sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55972ef797e2]

sql/sql_connect.cc:1318(handle_one_connection)[0x55972ef79ab4]

perfschema/pfs.cc:2204(pfs_spawn_thread)[0x55972f30087d]

nptl/pthread_create.c:478(start_thread)[0x7f98364ab609]

??:0(clone)[0x7f9836099293]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7f97a4012940): EXPLAIN SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1

Consistently reproducible with the example SQL dump attached. Sometimes the EXPLAIN needs to be run 3-4 times before it starts crashing.
Reproduced on latest 10.4, 10.5 and 10.6 versions with:

$ dbdeployer deploy single 10.6.4

$ msb_10_6_4/use test < mycrash_1.sql

$ msb_10_6_4/use test

mysql [localhost:10604] {msandbox} (test) > SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1;

ERROR 2013 (HY000): Lost connection to server during query

Problem similar I guess to ~~MDEV-19720~~ and ~~MDEV-22160~~.
I am very interested if there is any workaround to avoid these crashes.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

msandbox.err
2021-08-20 08:52
16 kB
Przemek
mycrash_1.sql
2021-08-20 08:50
284 kB
Przemek

Issue Links

is caused by

MDEV-27262 Unexpected index intersection with full index scan for an index

Closed

relates to

MDEV-19720 Server crash in st_join_table::save_explain_data or Assertion `sel->quick' failed in JOIN::make_range_rowid_filters with rowid_filter=on

Closed

MDEV-30651 Crash after killing query while it is processed by test_quick_select

Closed

MDEV-33510 Server crash : mysqld got signal 11 in st_join_table::save_explain_data

Closed

Activity

Ascending order - Click to sort in descending order

Sami Ahlroos added a comment - 2021-08-20 14:05

set optimizer_switch="index_merge_sort_intersection=on"; seems to avoid this crash:

mysql> set optimizer_switch="index_merge_sort_intersection=on";

Query OK, 0 rows affected (0.000 sec)

mysql> SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1;

+----------+

| COUNT(*) |

+----------+

|     2456 |

+----------+

1 row in set (0.027 sec)

Sami Ahlroos added a comment - 2021-08-20 14:05 set optimizer_switch="index_merge_sort_intersection=on"; seems to avoid this crash: mysql> set optimizer_switch= "index_merge_sort_intersection=on" ; Query OK, 0 rows affected ( 0.000 sec) mysql> SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4 ) OR er = 4 )) AND ((es >= 4 )) limit 1 ; +----------+ | COUNT(*) | +----------+ | 2456 | +----------+ 1 row in set ( 0.027 sec)

Alice Sherepa added a comment - 2021-08-24 09:50

Thanks!
I repeated as described on 10.4-10.6:

10.4 2b66cd249384221e2a892f

mysqld: /10.4/src/sql/sql_select.cc:1744: bool JOIN::make_range_rowid_filters(): Assertion `sel->quick' failed.
210824 11:46:37 [ERROR] mysqld got signal 6 ;

Server version: 10.4.22-MariaDB-debug-log

:0(__GI___assert_fail)[0x7ffb50852f36]
sql/sql_select.cc:1746(JOIN::make_range_rowid_filters())[0x55ec5c78bbbd]
sql/sql_select.cc:2366(JOIN::optimize_stage2())[0x55ec5c792818]
sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55ec5c792245]
sql/sql_select.cc:1659(JOIN::optimize())[0x55ec5c78ae38]
sql/sql_select.cc:4740(mysql_select(THD, TABLE_LIST, unsigned int, List<Item>&, Item, unsigned int, st_order, st_order, Item, st_order, unsigned long long, select_result, st_select_lex_unit, st_select_lex))[0x55ec5c7abb5f]
sql/sql_select.cc:436(handle_select(THD, LEX, select_result*, unsigned long))[0x55ec5c77ce9a]
sql/sql_parse.cc:6449(execute_sqlcom_select(THD, TABLE_LIST))[0x55ec5c6ebdea]
sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x55ec5c6d96b7]
sql/sql_parse.cc:7995(mysql_parse(THD, char, unsigned int, Parser_state*, bool, bool))[0x55ec5c6f52d7]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD, char, unsigned int, bool, bool))[0x55ec5c6cba53]
sql/sql_parse.cc:1373(do_command(THD*))[0x55ec5c6c84d0]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55ec5cac1b39]
sql/sql_connect.cc:1317(handle_one_connection)[0x55ec5cac1292]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55ec5e192df9]
nptl/pthread_create.c:478(start_thread)[0x7ffb50d69609]
x86_64/clone.S:97(__GI___clone)[0x7ffb5093e293]

Query (0x62b0000a1290): SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1

Alice Sherepa added a comment - 2021-08-24 09:50 Thanks! I repeated as described on 10.4-10.6: 10.4 2b66cd249384221e2a892f mysqld: /10.4/src/sql/sql_select.cc:1744: bool JOIN::make_range_rowid_filters(): Assertion `sel->quick' failed. 210824 11:46:37 [ERROR] mysqld got signal 6 ; Server version: 10.4.22-MariaDB-debug-log :0(__GI___assert_fail)[0x7ffb50852f36] sql/sql_select.cc:1746(JOIN::make_range_rowid_filters())[0x55ec5c78bbbd] sql/sql_select.cc:2366(JOIN::optimize_stage2())[0x55ec5c792818] sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55ec5c792245] sql/sql_select.cc:1659(JOIN::optimize())[0x55ec5c78ae38] sql/sql_select.cc:4740(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55ec5c7abb5f] sql/sql_select.cc:436(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55ec5c77ce9a] sql/sql_parse.cc:6449(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55ec5c6ebdea] sql/sql_parse.cc:3963(mysql_execute_command(THD*))[0x55ec5c6d96b7] sql/sql_parse.cc:7995(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55ec5c6f52d7] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55ec5c6cba53] sql/sql_parse.cc:1373(do_command(THD*))[0x55ec5c6c84d0] sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55ec5cac1b39] sql/sql_connect.cc:1317(handle_one_connection)[0x55ec5cac1292] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55ec5e192df9] nptl/pthread_create.c:478(start_thread)[0x7ffb50d69609] x86_64/clone.S:97(__GI___clone)[0x7ffb5093e293] Query (0x62b0000a1290): SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1

Igor Babaev (Inactive) added a comment - 2021-12-14 07:07

With the setting

set optimizer_switch="index_merge_sort_intersection=on";

the optimizer chooses for the query

SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1;

the following execution plan

MariaDB [test]> EXPLAIN EXTENDED SELECT COUNT(*) FROM mycrash WHERE ((p = 'foo' AND er != 4) OR er = 4) AND (es >= 4) limit 1;

+------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+

| id   | select_type | table   | type        | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                    |

+------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+

|    1 | SIMPLE      | mycrash | index_merge | es,er,p       | er,es | 0,1     | NULL | 1831 |   100.00 | Using sort_intersect(er,es); Using where |

+------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+

This already looks strange because the condition

 ((p = 'foo' AND er != 4) OR er = 4)

is equivalent to the condition

 ((p = 'foo' OR er=4) AND (er != 4 OR er = 4))

The latter is equivalent to

(p = 'foo' OR er=4) AND TRUE

Thus using index er does not make sense for index intersection.
Such plan we have for 10.2 and and higher versions.

Igor Babaev (Inactive) added a comment - 2021-12-14 07:07 With the setting set optimizer_switch="index_merge_sort_intersection=on"; the optimizer chooses for the query SELECT COUNT (*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1; the following execution plan MariaDB [test]> EXPLAIN EXTENDED SELECT COUNT(*) FROM mycrash WHERE ((p = 'foo' AND er != 4) OR er = 4) AND (es >= 4) limit 1; +------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+ | 1 | SIMPLE | mycrash | index_merge | es,er,p | er,es | 0,1 | NULL | 1831 | 100.00 | Using sort_intersect(er,es); Using where | +------+-------------+---------+-------------+---------------+-------+---------+------+------+----------+------------------------------------------+ This already looks strange because the condition ((p = 'foo' AND er != 4) OR er = 4) is equivalent to the condition ((p = 'foo' OR er=4) AND (er != 4 OR er = 4)) The latter is equivalent to (p = 'foo' OR er=4) AND TRUE Thus using index er does not make sense for index intersection. Such plan we have for 10.2 and and higher versions.

Igor Babaev (Inactive) added a comment - 2021-12-20 21:06

Here's the explanation how we come to an assertion failure when executing

SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1;

with the optimizer switch 'rowids_filter' set to 'on' .

First the optimizer tries to estimate the cardinality of ranges that could be used in ranges scans for all indexes. Due to the bug ~~MDEV-27262~~ the full index scan for the index er is considered as a possible range scan. Due to a defect of the implementation of records_in_range() for InnoDB the cardinality of this range is estimated as 50% of the number records in the table (it is always so for big ranges). As a result the optimizer chooses to use a range filter over er. When trying to build this filter it discovers that there could be no range scan over er: with only one index er enabled the code of Range Optimizer does not consider the full index scan as range scan.

Igor Babaev (Inactive) added a comment - 2021-12-20 21:06 Here's the explanation how we come to an assertion failure when executing SELECT COUNT (*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1; with the optimizer switch 'rowids_filter' set to 'on' . First the optimizer tries to estimate the cardinality of ranges that could be used in ranges scans for all indexes. Due to the bug MDEV-27262 the full index scan for the index er is considered as a possible range scan. Due to a defect of the implementation of records_in_range() for InnoDB the cardinality of this range is estimated as 50% of the number records in the table (it is always so for big ranges). As a result the optimizer chooses to use a range filter over er. When trying to build this filter it discovers that there could be no range scan over er: with only one index er enabled the code of Range Optimizer does not consider the full index scan as range scan.

Igor Babaev (Inactive) added a comment - 2021-12-25 19:09

I will not close this bug until ~~MDEV-27262~~ is merged into 10.4. After this I plan to add a test case showing that with rowid_filter='on' the optimizer te successfully produces a plan using a rowid filter for the above query.

Igor Babaev (Inactive) added a comment - 2021-12-25 19:09 I will not close this bug until MDEV-27262 is merged into 10.4. After this I plan to add a test case showing that with rowid_filter='on' the optimizer te successfully produces a plan using a rowid filter for the above query.

Alice Sherepa added a comment - 2023-01-13 12:46

Curently no rowid filter is used for this query:

10.4 71e8e4934db06c02db1b51716
MariaDB [test]> explain extended SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1;
+------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+
\| 1 \| SIMPLE \| mycrash \| range \| es,er,p \| es \| 1 \| NULL \| 2947 \| 100.00 \| Using index condition; Using where \|
+------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0,001 sec)

Note (Code 1003): select count(0) AS `COUNT(*)` from `test`.`mycrash` where (`test`.`mycrash`.`p` = 'foo' and `test`.`mycrash`.`er` <> 4 or `test`.`mycrash`.`er` = 4) and `test`.`mycrash`.`es` >= 4 limit 1

Alice Sherepa added a comment - 2023-01-13 12:46 Curently no rowid filter is used for this query: 10.4 71e8e4934db06c02db1b51716 MariaDB [test]> explain extended SELECT COUNT(*) FROM mycrash WHERE (((p = 'foo' AND er != 4) OR er = 4 )) AND ((es >= 4)) limit 1; +------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | mycrash | range | es,er,p | es | 1 | NULL | 2947 | 100.00 | Using index condition; Using where | +------+-------------+---------+-------+---------------+------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0,001 sec) Note (Code 1003): select count(0) AS `COUNT(*)` from `test`.`mycrash` where (`test`.`mycrash`.`p` = 'foo' and `test`.`mycrash`.`er` <> 4 or `test`.`mycrash`.`er` = 4) and `test`.`mycrash`.`es` >= 4 limit 1

Roel Van de Paar added a comment - 2023-02-14 09:18

See newly linked related bugs

Roel Van de Paar added a comment - 2023-02-14 09:18 See newly linked related bugs

People

Assignee:: Valerii Kravchuk

Reporter:: Przemek

Votes:: 1 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2021-08-20 08:54

Updated:: 2024-07-07 21:12

Resolved:: 2023-07-31 07:43

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration