[MDEV-31440] choose_best_splitting: crash on update query using correlated subquery after minor update - Jira

VAROQUI Stephane created issue - 2023-06-09 09:50

VAROQUI Stephane added a comment - 2023-06-09 09:56

Tested the crash was introduced in 10.9.6

VAROQUI Stephane added a comment - 2023-06-09 09:56 Tested the crash was introduced in 10.9.6

VAROQUI Stephane made changes - 2023-06-09 10:00

Field	Original Value	New Value
Attachment		mycrashingquery.zip [ 70455 ]

VAROQUI Stephane added a comment - 2023-06-09 10:01

Attaching show variables and query

VAROQUI Stephane added a comment - 2023-06-09 10:01 Attaching show variables and query

VAROQUI Stephane added a comment - 2023-06-09 10:08

The explain of the query make the server crash confirming the issue is in optimizer

VAROQUI Stephane added a comment - 2023-06-09 10:08 The explain of the query make the server crash confirming the issue is in optimizer

VAROQUI Stephane made changes - 2023-06-09 10:17

Affects Version/s

10.9.6 [ 28520 ]

VAROQUI Stephane made changes - 2023-06-09 11:43

Link

This issue is part of ~~MDEV-31403~~ [ ~~MDEV-31403~~ ]

VAROQUI Stephane added a comment - 2023-06-09 11:49

Can confirm SET global optimizer_switch='semijoin=off'; is a valid workaround

VAROQUI Stephane added a comment - 2023-06-09 11:49 Can confirm SET global optimizer_switch='semijoin=off'; is a valid workaround

VAROQUI Stephane made changes - 2023-06-09 11:59

Summary

crash on update query using correlated subquery after miner update

crash on update query using correlated subquery after minor update

Alice Sherepa made changes - 2023-06-12 11:46

Link

This issue relates to ~~MDEV-31403~~ [ ~~MDEV-31403~~ ]

Alice Sherepa made changes - 2023-06-12 11:49

Link

This issue relates to ~~MDEV-31403~~ [ ~~MDEV-31403~~ ]

Daniel Black made changes - 2023-06-13 03:07

Fix Version/s

10.9 [ 26905 ]

Daniel Black made changes - 2023-06-13 03:07

Assignee

Sergei Petrunia [ psergey ]

Daniel Black added a comment - 2023-06-13 03:09

The SQL didn't include the table structure for data_entity.

Daniel Black added a comment - 2023-06-13 03:09 The SQL didn't include the table structure for data_entity .

VAROQUI Stephane made changes - 2023-06-13 08:17

Description

Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

{code}
Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=1
max_threads=65636
thread_count=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f74f0c071d8
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 = 0x7f75782fc4a8 thread_stack 0x40000
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
/usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
/usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
/usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
/usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
/usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
/usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
/usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
/usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
/usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]
{code}

Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

{code}
Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=1
max_threads=65636
thread_count=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f74f0c071d8
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 = 0x7f75782fc4a8 thread_stack 0x40000
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
/usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
/usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
/usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
/usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
/usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
/usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
/usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
/usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
/usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]
{code}

{code}
Create Table: CREATE TABLE `lastname_page` (
  `obituary_id` int(11) unsigned NOT NULL,
  `lastname_uri` varchar(255) NOT NULL,
  `lastname_page` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`obituary_id`,`lastname_uri`),
  KEY `lastname_uri_page_id` (`lastname_uri`,`lastname_page`,`obituary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

CREATE TABLE `data_entity` (
  `uri` varchar(255) NOT NULL,
  `type_uri` varchar(255) NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `status` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`uri`),
  KEY `data_entity_type_uri_idx` (`type_uri`),
  KEY `data_entity_latitude_longitude_idx` (`latitude`,`longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
{code}

Alice Sherepa made changes - 2023-08-01 11:25

Link

This issue relates to MDEV-31818 [ MDEV-31818 ]

Alice Sherepa added a comment - 2023-08-02 12:38 - edited

stephane@skysql.com Could you please provide the way to repeat the crash (test case or dump of this 2 tables + .cnf - show variables were not attached). I tried to repeat with some mock data, but without luck.

Alice Sherepa added a comment - 2023-08-02 12:38 - edited stephane@skysql.com Could you please provide the way to repeat the crash (test case or dump of this 2 tables + .cnf - show variables were not attached). I tried to repeat with some mock data, but without luck.

Alice Sherepa made changes - 2023-08-08 12:16

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

VAROQUI Stephane added a comment - 2023-08-30 12:23

Alice we may have an other case of siimliar semi join issue but on 10.6.14 the sysadmin is more willing to spend time to reproduce base on a snapshot of a VM will keep you posted when we have reproductible the case

VAROQUI Stephane added a comment - 2023-08-30 12:23 Alice we may have an other case of siimliar semi join issue but on 10.6.14 the sysadmin is more willing to spend time to reproduce base on a snapshot of a VM will keep you posted when we have reproductible the case

Richard DEMONGEOT made changes - 2023-09-01 07:23

Link

This issue relates to ~~MDEV-32064~~ [ ~~MDEV-32064~~ ]

Sergei Golubchik added a comment - 2023-10-02 07:14

stephane@skysql.com, there was no feedback for a month. the issue will auto-close in a month after no activity (this my comment counts as activity too). But don't worry you can leave feedback whenever you have it, we'll reopen the issue if it'll be closed.

Sergei Golubchik added a comment - 2023-10-02 07:14 stephane@skysql.com , there was no feedback for a month. the issue will auto-close in a month after no activity (this my comment counts as activity too). But don't worry you can leave feedback whenever you have it, we'll reopen the issue if it'll be closed.

Alice Sherepa made changes - 2023-10-28 11:06

Link

This issue relates to ~~MDEV-32602~~ [ ~~MDEV-32602~~ ]

Sergei Petrunia made changes - 2023-10-30 08:29

Summary

crash on update query using correlated subquery after minor update

choose_best_splitting: crash on update query using correlated subquery after minor update

Alice Sherepa added a comment - 2023-10-30 15:03 - edited

test case from ~~MDEV-32624~~ - reproducible after 10.5 e87440b (~~MDEV-26301~~)
Crash is repeatable on 10.5-10.11, but not on 11.0+

--source include/have_innodb.inc

CREATE TABLE `table1` (

`id_product`int(11),

`id_stock`varchar(32),

`quantity` mediumint(9),

PRIMARY KEY (`id_product`,`id_stock`)

) engine=innodb;

CREATE TABLE `table2` (

`id_product`int(10) unsigned,

`id_shop`int(10) unsigned,

PRIMARY KEY (`id_product`,`id_shop`)

)  engine=innodb;

SELECT backups.id_product, quantity_backup, quantity_bl

FROM (

SELECT id_product, SUM(quantity) as quantity_backup

FROM`table1`

) backups

INNER JOIN (

SELECT id_product

FROM`table1`

GROUP BY id_product

) normals ON (normals.id_product = backups.id_product)

LEFT JOIN (

SELECT quantity as quantity_bl, id_product FROM table1

) bl ON (backups.id_product = bl.id_product )

WHERE

backups.id_product IN (

SELECT id_product

FROM table2

);

Version: '10.5.20-MariaDB-debug-log'

231030 15:51:48 [ERROR] mysqld got signal 11 ;

Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e

sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea]

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

sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d]

sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c]

sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c]

sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]

sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]

sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7]

sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3]

sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7]

sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66]

sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f]

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

sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35]

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

sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c]

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

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

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

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

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

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

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

Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl

FROM (

SELECT id_product, SUM(quantity) as quantity_backup

FROM`table1`

) backups

INNER JOIN (

SELECT id_product

FROM`table1`

GROUP BY id_product

) normals ON (normals.id_product = backups.id_product)

LEFT JOIN (

SELECT quantity as quantity_bl, id_product FROM table1

) bl ON (backups.id_product = bl.id_product )

WHERE

backups.id_product IN (

SELECT id_product

FROM table2

Alice Sherepa added a comment - 2023-10-30 15:03 - edited test case from MDEV-32624 - reproducible after 10.5 e87440b ( MDEV-26301 ) Crash is repeatable on 10.5-10.11, but not on 11.0+ --source include/have_innodb.inc CREATE TABLE `table1` ( `id_product` int (11), `id_stock` varchar (32), `quantity` mediumint(9), PRIMARY KEY (`id_product`,`id_stock`) ) engine=innodb; CREATE TABLE `table2` ( `id_product` int (10) unsigned, `id_shop` int (10) unsigned, PRIMARY KEY (`id_product`,`id_shop`) ) engine=innodb; SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM (quantity) as quantity_backup FROM `table1` ) backups INNER JOIN ( SELECT id_product FROM `table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 ); Version: '10.5.20-MariaDB-debug-log' 231030 15:51:48 [ERROR] mysqld got signal 11 ; Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea] sigaction.c:0(__restore_rt)[0x7feeb8d12420] sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d] sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c] sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c] sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f] sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f] sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7] sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3] sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7] sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66] sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f] sql/sql_select.cc:4830(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*))[0x55a108cb2ea6] sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35] sql/sql_parse.cc:6331(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a108be98c9] sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c] sql/sql_parse.cc:8106(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a108bf4bc3] sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a108bca819] sql/sql_parse.cc:1375(do_command(THD*))[0x55a108bc718b] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55a109023ea7] sql/sql_connect.cc:1320(handle_one_connection)[0x55a10902380b] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a109cad45a] nptl/pthread_create.c:478(start_thread)[0x7feeb8d06609] Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 )

Alice Sherepa made changes - 2023-10-30 15:03

Link

This issue is duplicated by ~~MDEV-32624~~ [ ~~MDEV-32624~~ ]

Alice Sherepa added a comment - 2023-10-30 15:05 - edited

Test case from ~~MDEV-32602~~ , crashes on 10.5-11.2, after e87440b commit

CREATE TABLE t1 ( id int NOT NULL, PRIMARY KEY (id));

INSERT INTO t1 VALUES

(-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534);

INSERT INTO t1 SELECT id

FROM t1

NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt

WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ;

DROP TABLE t1;

Version: '10.5.23-MariaDB-debug-log'

231028 13:29:08 [ERROR] mysqld got signal 11 ;

Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59

sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90]

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

sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e]

sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce]

sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a]

sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]

sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]

sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5]

sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1]

sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f]

sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c]

sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505]

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

sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073]

sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885]

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

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

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

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

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

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

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

Query (0x62b0000852a8): INSERT INTO t1 SELECT id

FROM t1

NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt

WHERE id IN ( SELECT id LIKE 'x' FROM t1 )

Alice Sherepa added a comment - 2023-10-30 15:05 - edited Test case from MDEV-32602 , crashes on 10.5-11.2, after e87440b commit CREATE TABLE t1 ( id int NOT NULL , PRIMARY KEY (id)); INSERT INTO t1 VALUES (-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534); INSERT INTO t1 SELECT id FROM t1 NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ; DROP TABLE t1; Version: '10.5.23-MariaDB-debug-log' 231028 13:29:08 [ERROR] mysqld got signal 11 ; Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59 sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90] sigaction.c:0(__restore_rt)[0x7fc23cb66420] sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e] sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce] sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a] sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d] sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d] sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5] sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1] sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f] sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c] sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505] sql/sql_select.cc:4845(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*))[0x5615c7c351cc] sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073] sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885] sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5615c7b76977] sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5615c7b4c3a5] sql/sql_parse.cc:1375(do_command(THD*))[0x5615c7b48d0d] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5615c7fa795b] sql/sql_connect.cc:1320(handle_one_connection)[0x5615c7fa72bf] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5615c8c33aba] nptl/pthread_create.c:478(start_thread)[0x7fc23cb5a609] Query (0x62b0000852a8): INSERT INTO t1 SELECT id FROM t1 NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt WHERE id IN ( SELECT id LIKE 'x' FROM t1 )

Alice Sherepa made changes - 2023-10-30 15:06

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Alice Sherepa made changes - 2023-10-30 15:13

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.2 [ 28603 ]

Alice Sherepa made changes - 2023-10-30 15:13

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]

Alice Sherepa made changes - 2023-10-30 15:13

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2023-10-30 15:16

Link

This issue is caused by ~~MDEV-26301~~ [ ~~MDEV-26301~~ ]

Alice Sherepa made changes - 2023-10-30 15:16

Link

This issue is part of ~~MDEV-31403~~ [ ~~MDEV-31403~~ ]

Alice Sherepa made changes - 2023-10-30 15:24

Link

This issue is duplicated by ~~MDEV-32571~~ [ ~~MDEV-32571~~ ]

Alice Sherepa made changes - 2023-11-13 12:11

Labels

regression

Julien Fritsch made changes - 2023-11-28 10:23

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:28

Fix Version/s

10.10 [ 27530 ]

Alice Sherepa made changes - 2023-12-15 16:09

Link

This issue is duplicated by ~~MDEV-32643~~ [ ~~MDEV-32643~~ ]

Alice Sherepa added a comment - 2023-12-15 16:19 - edited

not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5
but maybe not fixed - possible that query plan changed. Currently:

MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 );

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

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

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

|    1 | PRIMARY      | <subquery5> | ALL   | distinct_key  | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |

|    1 | PRIMARY      | <derived2>  | ref   | key1          | key1    | 5       | test.table2.id_product | 2    |   100.00 | Using where                                               |

|    1 | PRIMARY      | table1      | ALL   | PRIMARY       | NULL    | NULL    | NULL                   | 1    |   100.00 | Using where; Using join buffer (flat, BNL join)           |

|    1 | PRIMARY      | <derived3>  | ref   | key0          | key0    | 4       | backups.id_product     | 2    |   100.00 |                                                           |

|    5 | MATERIALIZED | table2      | index | PRIMARY       | PRIMARY | 8       | NULL                   | 1    |   100.00 | Using index                                               |

|    3 | DERIVED      | table1      | range | PRIMARY       | PRIMARY | 4       | NULL                   | 2    |   100.00 | Using index for group-by; Using temporary; Using filesort |

|    2 | DERIVED      | table1      | ALL   | NULL          | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |

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

7 rows in set, 1 warning (0,001 sec)

I could not compare with the previous one - EXPLAIN crashes also.

Alice Sherepa added a comment - 2023-12-15 16:19 - edited not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5 but maybe not fixed - possible that query plan changed. Currently: MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 ); +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ | 1 | PRIMARY | <subquery5> | ALL | distinct_key | NULL | NULL | NULL | 1 | 100.00 | | | 1 | PRIMARY | <derived2> | ref | key1 | key1 | 5 | test.table2.id_product | 2 | 100.00 | Using where | | 1 | PRIMARY | table1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (flat, BNL join) | | 1 | PRIMARY | <derived3> | ref | key0 | key0 | 4 | backups.id_product | 2 | 100.00 | | | 5 | MATERIALIZED | table2 | index | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using index | | 3 | DERIVED | table1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using index for group-by; Using temporary; Using filesort | | 2 | DERIVED | table1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ 7 rows in set, 1 warning (0,001 sec) I could not compare with the previous one - EXPLAIN crashes also.

Sergei Petrunia made changes - 2024-04-02 08:28

Description

Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

{code}
Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=1
max_threads=65636
thread_count=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f74f0c071d8
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 = 0x7f75782fc4a8 thread_stack 0x40000
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
/usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
/usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
/usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
/usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
/usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
/usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
/usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
/usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
/usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]
{code}

{code}
Create Table: CREATE TABLE `lastname_page` (
  `obituary_id` int(11) unsigned NOT NULL,
  `lastname_uri` varchar(255) NOT NULL,
  `lastname_page` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`obituary_id`,`lastname_uri`),
  KEY `lastname_uri_page_id` (`lastname_uri`,`lastname_page`,`obituary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

CREATE TABLE `data_entity` (
  `uri` varchar(255) NOT NULL,
  `type_uri` varchar(255) NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `status` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`uri`),
  KEY `data_entity_type_uri_idx` (`type_uri`),
  KEY `data_entity_latitude_longitude_idx` (`latitude`,`longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
{code}

Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

{code}
Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=1
max_threads=65636
thread_count=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f74f0c071d8
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 = 0x7f75782fc4a8 thread_stack 0x40000
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
/usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
/usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
/usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
/usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
/usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
/usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
/usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
/usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
/usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
/usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]
{code}

{code}
Create Table: CREATE TABLE `lastname_page` (
  `obituary_id` int(11) unsigned NOT NULL,
  `lastname_uri` varchar(255) NOT NULL,
  `lastname_page` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`obituary_id`,`lastname_uri`),
  KEY `lastname_uri_page_id` (`lastname_uri`,`lastname_page`,`obituary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

CREATE TABLE `data_entity` (
  `uri` varchar(255) NOT NULL,
  `type_uri` varchar(255) NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `status` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`uri`),
  KEY `data_entity_type_uri_idx` (`type_uri`),
  KEY `data_entity_latitude_longitude_idx` (`latitude`,`longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
{code}

query in the attached file:
{code:sql}
UPDATE data_entity
         INNER JOIN (
            SELECT lastname_uri
            FROM lastname_page
            INNER JOIN data_entity on uri = lastname_uri
            WHERE lastname_uri IN (<list of 31124 constants>)
            GROUP BY lastname_uri
            HAVING count(*) >= 10
          ) lp ON lp.lastname_uri = uri
        SET status = 1
        WHERE uri IN (<list of 31124 constants, seems same as above>)
        AND status = 0
{code}

Julien Fritsch made changes - 2024-05-03 14:40

Fix Version/s

11.0 [ 28320 ]

Jira Automation (IT) made changes - 2024-07-04 01:00

Zendesk Related Tickets

165880 114010

Julien Fritsch made changes - 2024-09-10 15:27

Fix Version/s

11.1 [ 28549 ]

Roel Van de Paar made changes - 2024-09-14 00:55

Labels

regression

regression stack-smashing

Roel Van de Paar made changes - 2024-09-14 01:01

Affects Version/s

11.2 [ 28603 ]

Roel Van de Paar made changes - 2024-09-14 01:01

Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.7 [ 29815 ]

Roel Van de Paar added a comment - 2024-09-14 01:03

--source include/have_innodb.inc

SET @@max_statement_time=0.0001;

--error 0, 1969

CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;

--error 0, 1969

UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;

--error 0, 1969

UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;

--error 0, 1969

DROP TABLE t;

Sporadid. Run MTR with --repeat 10000. Example occurence:

CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)
$ ./mtr --repeat 10000 test
....
main.test [ 390 pass ] 7
SET @@max_statement_time=0.0001;
CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
main.test [ 391 fail ]
Test ended at 2024-09-14 10:41:15

CURRENT_TEST: main.test
mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...


Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run
Server log from this test:
----------SERVER LOG START-----------
----------SERVER LOG END-------------


- found 'core' (0/5)

Leads to:

CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)
Core was generated by `/test/MD090924-mariadb-10.5.27-linux-x86_64-dbg/bin/mariadbd --defaults-group-s'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090

[Current thread is 1 (LWP 2104708)]
(gdb) bt
#0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090
#1 0x0000557ff3b896d1 in best_access_path (join=join@entry=0x14d558018d18, s=s@entry=0x14d558b5c800, remaining_tables=remaining_tables@entry=2, join_positions=0x14d558b5cdf0, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14d558b5cf30, loose_scan_pos=0x14d5eabfd070)at /test/10.5_dbg/sql/sql_select.cc:7627
#2 0x0000557ff3b89b71 in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=1.2, search_depth=search_depth@entry=61, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:9943
#3 0x0000557ff3b8a4ab in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:10032
#4 0x0000557ff3b8b396 in greedy_search (use_cond_selectivity=4, prune_level=<optimized out>, search_depth=62, remaining_tables=3, join=0x14d558018d18) at /test/10.5_dbg/sql/sql_select.cc:9111
#5 choose_plan (join=join@entry=0x14d558018d18, join_tables=<optimized out>)at /test/10.5_dbg/sql/sql_select.cc:8673
#6 0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810
#7 0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416
#8 0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765
#9 0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902
#10 0x0000557ff3c3075d in mysql_multi_update (thd=thd@entry=0x14d558000d58, table_list=0x14d558014320, fields=fields@entry=0x14d5580058a8, values=values@entry=0x14d558005e68, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x14d558004f08, select_lex=0x14d558005740, result=0x14d5eabfded0)at /test/10.5_dbg/sql/sql_update.cc:2003
#11 0x0000557ff3b4466c in mysql_execute_command (thd=thd@entry=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:4575
#12 0x0000557ff3b4acf1 in mysql_parse (thd=thd@entry=0x14d558000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14d5eabfe2b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_parse.cc:8236
#13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238
#14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376
#15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417
#16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319
#17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
#18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78

And

CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized)
Core was generated by `/test/MD090924-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd --defaults-group-su'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105

[Current thread is 1 (LWP 3149311)]
(gdb) bt
#0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105
#1 0x000055b96ccd14a8 in best_access_path (join=join@entry=0x146e94b311a0, s=s@entry=0x146e941e10c0, remaining_tables=remaining_tables@entry=2, join_positions=0x146e941e1750, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x146e941e2080, loose_scan_pos=0x146e941e21d0)at /test/server_opt/sql/sql_select.cc:8582
#2 0x000055b96ccd5477 in get_costs_for_tables (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, pos=pos@entry=0x146e941e1720, store_position=0x146ed4467308, allowed_tables=0x146ed4467318, stop_on_eq_ref=false, trace_one_table=0x146ed4467320) at /test/server_opt/sql/sql_select.cc:11329
#3 0x000055b96ccdef1e in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44674b0)at /test/server_opt/sql/sql_select.cc:11586
#4 0x000055b96ccdfa0b in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=<optimized out>, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44675c0)at /test/server_opt/sql/sql_select.cc:11812
#5 0x000055b96cce0799 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=3, join=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:10557
#6 choose_plan (join=join@entry=0x146e94b311a0, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0)at /test/server_opt/sql/sql_select.cc:10076
#7 0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312
#8 0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691
#9 0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989
#10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844
#11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109
#12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793
#13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403
#14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876
#15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991
#16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405
#17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448
#18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350
#19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198
#20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
#21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78

Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types:

SIGSEGV|st_join_table::choose_best_splitting|best_access_path|best_extension_by_limited_search|best_extension_by_limited_search

SIGSEGV|st_join_table::choose_best_splitting|best_access_path|get_costs_for_tables|best_extension_by_limited_search

Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.

Roel Van de Paar added a comment - 2024-09-14 01:03 --source include/have_innodb.inc SET @@max_statement_time=0.0001; --error 0, 1969 CREATE TABLE t (c1 DATETIME PRIMARY KEY ,c2 VARCHAR (40)) ENGINE=InnoDB; --error 0, 1969 UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; --error 0, 1969 UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; --error 0, 1969 DROP TABLE t; Sporadid. Run MTR with --repeat 10000. Example occurence: CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug) $ ./mtr --repeat 10000 test .... main.test [ 390 pass ] 7 SET @@max_statement_time=0.0001; CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB; UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; main.test [ 391 fail ] Test ended at 2024-09-14 10:41:15 CURRENT_TEST: main.test mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0... Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run Server log from this test: ----------SERVER LOG START----------- ----------SERVER LOG END------------- - found 'core' (0/5) Leads to: CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug) Core was generated by `/test/MD090924-mariadb-10.5.27-linux-x86_64-dbg/bin/mariadbd --defaults-group-s'. Program terminated with signal SIGSEGV, Segmentation fault. #0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090 [Current thread is 1 (LWP 2104708)] (gdb) bt #0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090 #1 0x0000557ff3b896d1 in best_access_path (join=join@entry=0x14d558018d18, s=s@entry=0x14d558b5c800, remaining_tables=remaining_tables@entry=2, join_positions=0x14d558b5cdf0, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14d558b5cf30, loose_scan_pos=0x14d5eabfd070)at /test/10.5_dbg/sql/sql_select.cc:7627 #2 0x0000557ff3b89b71 in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=1.2, search_depth=search_depth@entry=61, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:9943 #3 0x0000557ff3b8a4ab in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:10032 #4 0x0000557ff3b8b396 in greedy_search (use_cond_selectivity=4, prune_level=<optimized out>, search_depth=62, remaining_tables=3, join=0x14d558018d18) at /test/10.5_dbg/sql/sql_select.cc:9111 #5 choose_plan (join=join@entry=0x14d558018d18, join_tables=<optimized out>)at /test/10.5_dbg/sql/sql_select.cc:8673 #6 0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810 #7 0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416 #8 0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765 #9 0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902 #10 0x0000557ff3c3075d in mysql_multi_update (thd=thd@entry=0x14d558000d58, table_list=0x14d558014320, fields=fields@entry=0x14d5580058a8, values=values@entry=0x14d558005e68, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x14d558004f08, select_lex=0x14d558005740, result=0x14d5eabfded0)at /test/10.5_dbg/sql/sql_update.cc:2003 #11 0x0000557ff3b4466c in mysql_execute_command (thd=thd@entry=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:4575 #12 0x0000557ff3b4acf1 in mysql_parse (thd=thd@entry=0x14d558000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14d5eabfe2b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_parse.cc:8236 #13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238 #14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376 #15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417 #16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319 #17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201 #18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447 #19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78 And CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized) Core was generated by `/test/MD090924-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd --defaults-group-su'. Program terminated with signal SIGSEGV, Segmentation fault. #0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105 [Current thread is 1 (LWP 3149311)] (gdb) bt #0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105 #1 0x000055b96ccd14a8 in best_access_path (join=join@entry=0x146e94b311a0, s=s@entry=0x146e941e10c0, remaining_tables=remaining_tables@entry=2, join_positions=0x146e941e1750, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x146e941e2080, loose_scan_pos=0x146e941e21d0)at /test/server_opt/sql/sql_select.cc:8582 #2 0x000055b96ccd5477 in get_costs_for_tables (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, pos=pos@entry=0x146e941e1720, store_position=0x146ed4467308, allowed_tables=0x146ed4467318, stop_on_eq_ref=false, trace_one_table=0x146ed4467320) at /test/server_opt/sql/sql_select.cc:11329 #3 0x000055b96ccdef1e in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44674b0)at /test/server_opt/sql/sql_select.cc:11586 #4 0x000055b96ccdfa0b in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=<optimized out>, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44675c0)at /test/server_opt/sql/sql_select.cc:11812 #5 0x000055b96cce0799 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=3, join=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:10557 #6 choose_plan (join=join@entry=0x146e94b311a0, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0)at /test/server_opt/sql/sql_select.cc:10076 #7 0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312 #8 0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691 #9 0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989 #10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844 #11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109 #12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793 #13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403 #14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876 #15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991 #16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405 #17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448 #18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350 #19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198 #20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447 #21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78 Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types: SIGSEGV|st_join_table::choose_best_splitting|best_access_path|best_extension_by_limited_search|best_extension_by_limited_search SIGSEGV|st_join_table::choose_best_splitting|best_access_path|get_costs_for_tables|best_extension_by_limited_search Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.

Roel Van de Paar made changes - 2024-09-14 01:04

Labels

regression stack-smashing

regression sporadic stack-smashing

Roel Van de Paar added a comment - 2024-09-14 01:44

No UB/ASAN issues observed on 11.2 dbg, even with long runs.

Roel Van de Paar added a comment - 2024-09-14 01:44 No UB/ASAN issues observed on 11.2 dbg, even with long runs.

Roel Van de Paar made changes - 2024-09-14 05:06

Affects Version/s

11.7 [ 29815 ]

Roel Van de Paar made changes - 2024-09-14 05:06

Fix Version/s

11.7 [ 29815 ]

Julien Fritsch made changes - 2024-09-17 10:31

Fix Version/s

11.1(EOL) [ 28549 ]

Julien Fritsch made changes - 2024-11-22 17:05

Fix Version/s

11.2(EOL) [ 28603 ]

MariaDB Server

choose_best_splitting: crash on update query using correlated subquery after minor update

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration