[MDEV-30659] Server crash on EXPLAIN SELECT/SELECT on table with engine Aria for LooseScan Strategy - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 11.0(EOL)
Fix Version/s: 11.0.2
Component/s: Optimizer
Labels:
None

Description

Case to reproduce:

set default_storage_engine=Aria;

create table t1 (old_c1 integer,

                 old_c2 integer,

                 c1 integer,

                 c2 integer,

                 c3 integer);

insert into t1(c1,c2,c3)

        values (1,1,1), (1,2,2), (1,3,3),

               (2,1,4), (2,2,5), (2,3,6),

               (2,4,7), (2,5,8);

create index t1_c2 on t1 (c2,c1);

explain

select * from t1

         where t1.c2 in (select a.c2 from t1 a)

            and c2 >= 3 order by c2;

drop table t1;

Stacktrace:

Thread pointer: 0x7f3690000dc8

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 = 0x7f36a0bf6c38 thread_stack 0x49000

mysys/stacktrace.c:212(my_print_stacktrace)[0x55a5f3e9c026]

sql/signal_handler.cc:238(handle_fatal_signal)[0x55a5f3573c7f]

libc_sigaction.c:0(__restore_rt)[0x7f36a625e520]

nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7f36a62b2a7c]

posix/raise.c:27(__GI_raise)[0x7f36a625e476]

stdlib/abort.c:81(__GI_abort)[0x7f36a62447f3]

intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f36a624471b]

:0(__GI___assert_fail)[0x7f36a6255e96]

sql/sql_select.cc:30497(get_range_limit_read_cost(POSITION const*, TABLE const*, unsigned int, unsigned long long, unsigned long long, double*, double*))[0x55a5f324696d]

sql/sql_select.cc:30856(test_if_cheaper_ordering(st_join_table const*, st_order*, TABLE*, Bitmap<64u>, int, unsigned long long, int*, int*, unsigned long long*, unsigned int*, unsigned int*))[0x55a5f3247b86]

sql/sql_select.cc:25766(test_if_skip_sort_order(st_join_table*, st_order*, unsigned long long, bool, Bitmap<64u> const*))[0x55a5f3238c7b]

sql/sql_select.cc:3306(JOIN::optimize_stage2())[0x55a5f31f7926]

sql/sql_select.cc:2595(JOIN::optimize_inner())[0x55a5f31f5070]

sql/sql_select.cc:1897(JOIN::optimize())[0x55a5f31f2743]

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

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

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

sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x55a5f31885c8]

sql/sql_parse.cc:8000(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55a5f319643e]

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

sql/sql_parse.cc:1407(do_command(THD*, bool))[0x55a5f3180c40]

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

sql/sql_connect.cc:1320(handle_one_connection)[0x55a5f337005f]

perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a5f38e388d]

nptl/pthread_create.c:442(start_thread)[0x7f36a62b0b43]

x86_64/clone3.S:83(__clone3)[0x7f36a6342a00]

Trying to get some variables.

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

Query (0x7f36900154c0): explain

select * from t1

where t1.c2 in (select a.c2 from t1 a)

and c2 >= 3 order by c2

If we add

analyze table t1 persistent for all;

after index creation problem is gone.

There is a second case with truncate table causing the server crash (trigger is important in this case):

set default_storage_engine=Aria;

create table t1 (old_c1 integer,

                 old_c2 integer,

                 c1 integer,

                 c2 integer,

                 c3 integer);

delimiter /;

create trigger trg_t1 before update on t1 for each row

begin

  set new.old_c1=old.c1;

  set new.old_c2=old.c2;

end;

delimiter ;/

insert into t1(c1,c2,c3)

        values (1,1,1), (1,2,2), (1,3,3),

               (2,1,4), (2,2,5), (2,3,6),

               (2,4,7), (2,5,8);

create index t1_c2 on t1 (c2,c1);

analyze table t1 persistent for all;

create table tmp as select * from t1;

truncate table t1;

insert into t1 select * from tmp;

explain

select * from t1

         where t1.c2 in (select a.c2 from t1 a)

            and c2 >= 3 order by c2;

drop table tmp;

drop table t1;

Attachments

Activity

Ascending order - Click to sort in descending order

Lena Startseva created issue - 2023-02-15 20:29

Lena Startseva made changes - 2023-02-15 20:31

Field	Original Value	New Value
Description	Case to reproduce: {code:sql} set default_storage_engine=Aria; create table t1 (old_c1 integer, old_c2 integer, c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); create index t1_c2 on t1 (c2,c1); explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2; drop table t1; {code} Stacktrace: {code:C++} Thread pointer: 0x7f3690000dc8 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 = 0x7f36a0bf6c38 thread_stack 0x49000 mysys/stacktrace.c:212(my_print_stacktrace)[0x55a5f3e9c026] sql/signal_handler.cc:238(handle_fatal_signal)[0x55a5f3573c7f] libc_sigaction.c:0(__restore_rt)[0x7f36a625e520] nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7f36a62b2a7c] posix/raise.c:27(__GI_raise)[0x7f36a625e476] stdlib/abort.c:81(__GI_abort)[0x7f36a62447f3] intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f36a624471b] :0(__GI___assert_fail)[0x7f36a6255e96] sql/sql_select.cc:30497(get_range_limit_read_cost(POSITION const, TABLE const, unsigned int, unsigned long long, unsigned long long, double, double))[0x55a5f324696d] sql/sql_select.cc:30856(test_if_cheaper_ordering(st_join_table const, st_order, TABLE, Bitmap<64u>, int, unsigned long long, int, int, unsigned long long, unsigned int, unsigned int))[0x55a5f3247b86] sql/sql_select.cc:25766(test_if_skip_sort_order(st_join_table, st_order, unsigned long long, bool, Bitmap<64u> const))[0x55a5f3238c7b] sql/sql_select.cc:3306(JOIN::optimize_stage2())[0x55a5f31f7926] sql/sql_select.cc:2595(JOIN::optimize_inner())[0x55a5f31f5070] sql/sql_select.cc:1897(JOIN::optimize())[0x55a5f31f2743] sql/sql_select.cc:5121(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))[0x55a5f31fe61c] sql/sql_select.cc:29564(mysql_explain_union(THD, st_select_lex_unit, select_result))[0x55a5f3243d82] sql/sql_parse.cc:6203(execute_sqlcom_select(THD, TABLE_LIST))[0x55a5f3191148] sql/sql_parse.cc:3949(mysql_execute_command(THD, bool))[0x55a5f31885c8] sql/sql_parse.cc:8000(mysql_parse(THD, char, unsigned int, Parser_state))[0x55a5f319643e] sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD, char, unsigned int, bool))[0x55a5f31822ec] sql/sql_parse.cc:1407(do_command(THD, bool))[0x55a5f3180c40] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT, bool))[0x55a5f33702f6] sql/sql_connect.cc:1320(handle_one_connection)[0x55a5f337005f] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a5f38e388d] nptl/pthread_create.c:442(start_thread)[0x7f36a62b0b43] x86_64/clone3.S:83(__clone3)[0x7f36a6342a00] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f36900154c0): explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2 {code} If we add {code:sql} analyze table t1 persistent for all; {code} after index creation problem is gone. There is a second case with truncate table causing the server crash: {code:sql} set default_storage_engine=Aria; create table t1 (old_c1 integer, old_c2 integer, c1 integer, c2 integer, c3 integer); delimiter /; create trigger trg_t1 before update on t1 for each row begin set new.old_c1=old.c1; set new.old_c2=old.c2; end; / delimiter ;/ insert into t1(c1,c2,c3) values (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); create index t1_c2 on t1 (c2,c1); analyze table t1 persistent for all; create table tmp as select * from t1; truncate table t1; insert into t1 select * from tmp; explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2; drop table tmp; drop table t1; {code}	Case to reproduce: {code:sql} set default_storage_engine=Aria; create table t1 (old_c1 integer, old_c2 integer, c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); create index t1_c2 on t1 (c2,c1); explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2; drop table t1; {code} Stacktrace: {code:C++} Thread pointer: 0x7f3690000dc8 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 = 0x7f36a0bf6c38 thread_stack 0x49000 mysys/stacktrace.c:212(my_print_stacktrace)[0x55a5f3e9c026] sql/signal_handler.cc:238(handle_fatal_signal)[0x55a5f3573c7f] libc_sigaction.c:0(__restore_rt)[0x7f36a625e520] nptl/pthread_kill.c:44(__pthread_kill_implementation)[0x7f36a62b2a7c] posix/raise.c:27(__GI_raise)[0x7f36a625e476] stdlib/abort.c:81(__GI_abort)[0x7f36a62447f3] intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f36a624471b] :0(__GI___assert_fail)[0x7f36a6255e96] sql/sql_select.cc:30497(get_range_limit_read_cost(POSITION const, TABLE const, unsigned int, unsigned long long, unsigned long long, double, double))[0x55a5f324696d] sql/sql_select.cc:30856(test_if_cheaper_ordering(st_join_table const, st_order, TABLE, Bitmap<64u>, int, unsigned long long, int, int, unsigned long long, unsigned int, unsigned int))[0x55a5f3247b86] sql/sql_select.cc:25766(test_if_skip_sort_order(st_join_table, st_order, unsigned long long, bool, Bitmap<64u> const))[0x55a5f3238c7b] sql/sql_select.cc:3306(JOIN::optimize_stage2())[0x55a5f31f7926] sql/sql_select.cc:2595(JOIN::optimize_inner())[0x55a5f31f5070] sql/sql_select.cc:1897(JOIN::optimize())[0x55a5f31f2743] sql/sql_select.cc:5121(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))[0x55a5f31fe61c] sql/sql_select.cc:29564(mysql_explain_union(THD, st_select_lex_unit, select_result))[0x55a5f3243d82] sql/sql_parse.cc:6203(execute_sqlcom_select(THD, TABLE_LIST))[0x55a5f3191148] sql/sql_parse.cc:3949(mysql_execute_command(THD, bool))[0x55a5f31885c8] sql/sql_parse.cc:8000(mysql_parse(THD, char, unsigned int, Parser_state))[0x55a5f319643e] sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD, char, unsigned int, bool))[0x55a5f31822ec] sql/sql_parse.cc:1407(do_command(THD, bool))[0x55a5f3180c40] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT, bool))[0x55a5f33702f6] sql/sql_connect.cc:1320(handle_one_connection)[0x55a5f337005f] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a5f38e388d] nptl/pthread_create.c:442(start_thread)[0x7f36a62b0b43] x86_64/clone3.S:83(__clone3)[0x7f36a6342a00] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f36900154c0): explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2 {code} If we add {code:sql} analyze table t1 persistent for all; {code} after index creation problem is gone. There is a second case with truncate table causing the server crash (trigger is important in this case): {code:sql} set default_storage_engine=Aria; create table t1 (old_c1 integer, old_c2 integer, c1 integer, c2 integer, c3 integer); delimiter /; create trigger trg_t1 before update on t1 for each row begin set new.old_c1=old.c1; set new.old_c2=old.c2; end; / delimiter ;/ insert into t1(c1,c2,c3) values (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8); create index t1_c2 on t1 (c2,c1); analyze table t1 persistent for all; create table tmp as select * from t1; truncate table t1; insert into t1 select * from tmp; explain select * from t1 where t1.c2 in (select a.c2 from t1 a) and c2 >= 3 order by c2; drop table tmp; drop table t1; {code}

Lena Startseva made changes - 2023-02-15 20:32

Assignee

Lena Startseva [ JIRAUSER50478 ]

Michael Widenius [ monty ]

Elena Stepanova made changes - 2023-02-15 20:37

Fix Version/s

11.0 [ 28320 ]

Michael Widenius made changes - 2023-02-16 12:29

Status

Open [ 1 ]

In Progress [ 3 ]

Michael Widenius added a comment - 2023-02-16 15:18

First fix done
Waiting for review by Sergei Petrunia

Michael Widenius added a comment - 2023-02-16 15:18 First fix done Waiting for review by Sergei Petrunia

Sergei Petrunia added a comment - 2023-02-20 13:36

One interesting thing that the testcase shows:

The cost of full index scan when it is considered by LooseScan strategy is lower than the cost of the range access.

This is because the quick select adds

records * WHERE_COST_THD(thd)

while LooseScan's full-index-scan cost doesn't add it...

Sergei Petrunia added a comment - 2023-02-20 13:36 One interesting thing that the testcase shows: The cost of full index scan when it is considered by LooseScan strategy is lower than the cost of the range access. This is because the quick select adds records * WHERE_COST_THD(thd) while LooseScan's full-index-scan cost doesn't add it...

Sergei Petrunia added a comment - 2023-02-20 13:51

Another thing to check is that Loose_scan_opt::check_ref_access_part1 does this:

/*

            Now find out how many different keys we will get (for now we

            ignore the fact that we have "keypart_i=const" restriction for

            some key components, that may make us think think that loose

            scan will produce more distinct records than it actually will)

*/

          ulong rpc;

          if ((rpc= s->table->key_info[key].rec_per_key[max_loose_keypart]))

            records= records / rpc;

That is, it computes how many #records will be left semi-join duplicates are removed.

At the same time, Monty's patch does this in Loose_scan_opt::save_to_position():

      set_if_smaller(*records_out, best_loose_scan_records);

which means best_access_path will get the estimated #records after the duplicates are removed...
This seems incorrect.
It's fine to adjust loose_scan_pos->records_out based on computations in best_access_path(). But not the other way around...

Sergei Petrunia added a comment - 2023-02-20 13:51 Another thing to check is that Loose_scan_opt::check_ref_access_part1 does this: /* Now find out how many different keys we will get (for now we ignore the fact that we have "keypart_i=const" restriction for some key components, that may make us think think that loose scan will produce more distinct records than it actually will) */ ulong rpc; if ((rpc= s->table->key_info[key].rec_per_key[max_loose_keypart])) records= records / rpc; That is, it computes how many #records will be left semi-join duplicates are removed. At the same time, Monty's patch does this in Loose_scan_opt::save_to_position() : set_if_smaller(*records_out, best_loose_scan_records); which means best_access_path will get the estimated #records after the duplicates are removed... This seems incorrect. It's fine to adjust loose_scan_pos->records_out based on computations in best_access_path(). But not the other way around...

Sergei Petrunia added a comment - 2023-02-21 09:53

Pushed my variant of the patch into bb-11.0-mdev30659-v2. It's a "subset" of Monty's patch.

Sergei Petrunia added a comment - 2023-02-21 09:53 Pushed my variant of the patch into bb-11.0-mdev30659-v2. It's a "subset" of Monty's patch.

Michael Widenius made changes - 2023-05-27 13:47

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Michael Widenius added a comment - 2023-05-27 13:48

Fixed by commit by Sergei Petrunia, using some code from me.

Michael Widenius added a comment - 2023-05-27 13:48 Fixed by commit by Sergei Petrunia, using some code from me.

Michael Widenius made changes - 2023-05-27 13:48

issue.field.resolutiondate

2023-05-27 13:48:44.0

2023-05-27 13:48:44.654

Michael Widenius made changes - 2023-05-27 13:48

Fix Version/s		11.0.2 [ 28706 ]
Fix Version/s	11.0 [ 28320 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Michael Widenius

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-02-15 20:29

Updated:: 2023-05-27 13:48

Resolved:: 2023-05-27 13:48

Git Integration

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

MariaDB Server