[MDEV-30391] Server crash in spider_set_direct_limit_offset upon 2nd execution of PS Created: 2023-01-12  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Prepared Statements, Storage Engine - Spider
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-25116 Spider: IF(COUNT( trigger SQL Error ... Closed

 Description    

#This may not work, e.g. for in-source builds, fix the path
 
--source plugin/spider/spider/include/init_spider.inc
 
 
 
SET spider_same_server_link= on;
 
--eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
 
 
 
CREATE TABLE t (a INT);
 
INSERT INTO t VALUES (1),(2);
 
CREATE TABLE t_spider (a INT) ENGINE=SPIDER COMMENT = "wrapper 'mysql', srv 's', table 't'";
 
CREATE VIEW v_spider AS SELECT * FROM t_spider;
 
 
 
PREPARE stmt FROM 'SELECT a FROM v_spider ORDER BY a LIMIT 1 OFFSET 1';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
 
 
DROP VIEW v_spider;
 
DROP TABLE t_spider, t;
 
DROP SERVER s;
 
 
 
This may not work, e.g. for in-source builds, fix the path
 
--source plugin/spider/spider/include/deinit_spider.inc

10.4 f97f6955

 
#3  <signal handler called>
 
#4  0x00007feec18a7ed6 in spider_set_direct_limit_offset (spider=0x61f0000196a8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/spider/spd_table.cc:9111
 
#5  0x00007feec1995f8c in ha_spider::check_direct_order_limit (this=0x61f0000196a8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/spider/ha_spider.cc:13163
 
#6  0x00007feec1962a3b in ha_spider::rnd_next_internal (this=0x61f0000196a8, buf=0x61900008e8c0 "\377") at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/spider/ha_spider.cc:7727
 
#7  0x00007feec1967a30 in ha_spider::rnd_next (this=0x61f0000196a8, buf=0x61900008e8c0 "\377") at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/spider/ha_spider.cc:8093
 
#8  0x000055f126456c55 in handler::ha_rnd_next (this=0x61f0000196a8, buf=0x61900008e8c0 "\377") at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/handler.cc:2891
 
#9  0x000055f12643230a in find_all_keys (thd=0x62b00005b208, param=0x7feec1dd6e70, select=0x62b0000643c0, fs_info=0x615000015c00, buffpek_pointers=0x7feec1dd70f0, tempfile=0x7feec1dd6f40, pq=0x7feec1dd6e00, found_rows=0x615000015de0) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/filesort.cc:783
 
#10 0x000055f12642e3cc in filesort (thd=0x62b00005b208, table=0x620000062088, filesort=0x62b000064598, tracker=0x62b000064c88, join=0x62b0000623a8, first_table_bit=1) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/filesort.cc:263
 
#11 0x000055f125e1f084 in create_sort_index (thd=0x62b00005b208, join=0x62b0000623a8, tab=0x62b0000636b8, fsort=0x62b000064598) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:24070
 
#12 0x000055f125e0dd1b in st_join_table::sort_table (this=0x62b0000636b8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:21790
 
#13 0x000055f125e0d225 in join_init_read_record (tab=0x62b0000636b8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:21729
 
#14 0x000055f125e06a3f in sub_select (join=0x62b0000623a8, join_tab=0x62b0000636b8, end_of_records=false) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:20800
 
#15 0x000055f125e04bc8 in do_select (join=0x62b0000623a8, procedure=0x0) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:20337
 
#16 0x000055f125d94306 in JOIN::exec_inner (this=0x62b0000623a8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:4574
 
#17 0x000055f125d91949 in JOIN::exec (this=0x62b0000623a8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:4356
 
#18 0x000055f125d9599e in mysql_select (thd=0x62b00005b208, tables=0x62b0000954f0, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x62b0000965a8, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x62b0000967a8, unit=0x62b0000932f8, select_lex=0x62b000094eb8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:4795
 
#19 0x000055f125d66dd4 in handle_select (thd=0x62b00005b208, lex=0x62b000093238, result=0x62b0000967a8, setup_tables_done_option=0) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_select.cc:437
 
#20 0x000055f125cd9053 in execute_sqlcom_select (thd=0x62b00005b208, all_tables=0x62b0000954f0) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:6452
 
#21 0x000055f125cc6a23 in mysql_execute_command (thd=0x62b00005b208) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:3966
 
#22 0x000055f125d340a8 in Prepared_statement::execute (this=0x619000256288, expanded_query=0x7feec1dd9b00, open_cursor=false) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_prepare.cc:5026
 
#23 0x000055f125d2f711 in Prepared_statement::execute_loop (this=0x619000256288, expanded_query=0x7feec1dd9b00, open_cursor=false, packet=0x0, packet_end=0x0) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_prepare.cc:4495
 
#24 0x000055f125d29589 in mysql_sql_stmt_execute (thd=0x62b00005b208) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_prepare.cc:3577
 
#25 0x000055f125cc6a68 in mysql_execute_command (thd=0x62b00005b208) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:3982
 
#26 0x000055f125ce205c in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "EXECUTE stmt", length=12, parser_state=0x7feec1ddb800, is_com_multi=false, is_next_command=false) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:7984
 
#27 0x000055f125cb8fc5 in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x62900023f209 "", packet_length=12, is_com_multi=false, is_next_command=false) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:1857
 
#28 0x000055f125cb5b64 in do_command (thd=0x62b00005b208) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_parse.cc:1378
 
#29 0x000055f1260a4520 in do_handle_one_connection (connect=0x608000000da8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_connect.cc:1420
 
#30 0x000055f1260a3e06 in handle_one_connection (arg=0x608000000da8) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/sql/sql_connect.cc:1324
 
#31 0x000055f126ce048b in pfs_spawn_thread (arg=0x615000004e08) at /home/jenkins/workspace/sandbox-elenst/Nightly-Build-CS/src/storage/perfschema/pfs.cc:1869
 
#32 0x00007feecbcafea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#33 0x00007feecb89caef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95



 Comments   
Comment by Yuchen Pei [ 2023-02-06 ]

mtr case:

storage/spider/mysql-test/spider/bugfix/t/mdev_30391.test

 
--echo #
 
--echo # MDEV-30391 Server crash in spider_set_direct_limit_offset upon 2nd execution of PS
 
--echo #
 
--source ../../t/test_init.inc
 
 
 
--connection child2_1
 
CREATE DATABASE auto_test_remote;
 
USE auto_test_remote;
 
CREATE TABLE tbl_a (a INT);
 
INSERT INTO tbl_a VALUES (1),(2);
 
 
 
--connection master_1
 
CREATE DATABASE auto_test_local;
 
USE auto_test_local;
 
eval CREATE TABLE tbl_a (
 
    a INT
 
) $MASTER_1_ENGINE COMMENT='table "tbl_a", srv "s_2_1"';
 
CREATE VIEW vs AS SELECT * FROM tbl_a;
 
 
 
PREPARE stmt FROM 'SELECT a FROM vs ORDER BY a LIMIT 1 OFFSET 1';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
 
 
--connection master_1
 
DROP DATABASE IF EXISTS auto_test_local;
 
 
 
--connection child2_1
 
DROP DATABASE IF EXISTS auto_test_remote;
 
 
 
--source ../../t/test_deinit.inc

storage/spider/mysql-test/spider/bugfix/t/mdev_30391.cnf

 
!include include/default_mysqld.cnf
 
!include ../my_1_1.cnf
 
!include ../my_2_1.cnf

Changing vs to tbl_a in the prepared statement causes the test to pass.

Comment by Yuchen Pei [ 2023-02-06 ]

Initial debugging seems to indicate that the first execute freed table_list->table, causing a null reference in the second execute.

First execute:

[[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:20103][free_tmp_table]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_base.cc:949][close_thread_tables]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:6262][mysql_execute_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:5026][Prepared_statement::execute]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:4495][Prepared_statement::execute_loop]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:3577][mysql_sql_stmt_execute]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:3982][mysql_execute_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:7986][mysql_parse]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:1857][dispatch_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:1378][do_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_connect.cc:1420][do_handle_one_connection]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_connect.cc:1324][handle_one_connection]] > [[/lib/x86_64-linux-gnu/libpthread.so.0][start_thread]]

10.4 bef20b5f36d21a2e7a03d283e158e66a64a16754

 
  if (entry->pos_in_table_list && entry->pos_in_table_list->table)
 
  {
 
    DBUG_ASSERT(entry->pos_in_table_list->table == entry);
 
    entry->pos_in_table_list->table= NULL; // <-
 
  }

Second execute:

[[/home/ycp/source/mariadb-server/mdev-30391/src/storage/spider/spd_table.cc:9111][spider_set_direct_limit_offset]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/storage/spider/ha_spider.cc:13173][ha_spider::check_direct_order_limit]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/storage/spider/ha_spider.cc:7727][ha_spider::rnd_next_internal]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/storage/spider/ha_spider.cc:8093][ha_spider::rnd_next]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/handler.cc:2891][handler::ha_rnd_next]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/filesort.cc:783][find_all_keys]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/filesort.cc:263][filesort]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:24073][create_sort_index]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:21793][st_join_table::sort_table]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:21732][join_init_read_record]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:20803][sub_select]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:20340][do_select]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:4574][JOIN::exec_inner]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:4356][JOIN::exec]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:4795][mysql_select]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_select.cc:437][handle_select]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:6454][execute_sqlcom_select]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:3966][mysql_execute_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:5026][Prepared_statement::execute]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:4495][Prepared_statement::execute_loop]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_prepare.cc:3577][mysql_sql_stmt_execute]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:3982][mysql_execute_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:7986][mysql_parse]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:1857][dispatch_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_parse.cc:1378][do_command]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_connect.cc:1420][do_handle_one_connection]] > [[/home/ycp/source/mariadb-server/mdev-30391/src/sql/sql_connect.cc:1324][handle_one_connection]] > [[/lib/x86_64-linux-gnu/libpthread.so.0][start_thread]]

10.4 bef20b5f36d21a2e7a03d283e158e66a64a16754

 
  table_list = (TABLE_LIST *) select_lex->table_list.first;
 
  if (table_list->table->file->partition_ht() != spider_hton_ptr) // BOOM!
 
  {
 
    DBUG_PRINT("info",("spider ht1=%u ht2=%u",

Comment by Yuchen Pei [ 2023-02-06 ]

The first bad commit is

4194f7b6054d9cf888d657f08c88dc27031def7b is the first bad commit
 
commit 4194f7b6054d9cf888d657f08c88dc27031def7b
 
Author: Nayuta Yanagisawa <nayuta.yanagisawa@hey.com>
 
Date:   Wed Feb 16 17:56:49 2022 +0900
 
 
 
    MDEV-25116 Spider: IF(COUNT( trigger SQL Error (1054)_ Unknown column '' in field list
 
    
    The original query "SELECT IF(COUNT(a.`id`)>=0,'Y','N') FROM t" is
 
    transformed to "SELECT COUNT(a.`id`), IF(ref >= 0, 'Y', 'N') FROM t",
 
    where ref is Item_ref to "COUNT(a.`id`)", by split_sum_func().
 
    
    Spider walks the item list twice, invoking spider_db_print_item_type().
 
    The first invocation is in spider_create_group_by_handler() with
 
    str == NULL. The second one is in spider_group_by_handler::init_scan()
 
    with str != NULL.
 
    
    spider_db_print_item_type() prints nothing at the first invocation,
 
    and it prints item at the second invocation. However, at the second
 
    invocation, the above mentioned ref to "COUNT(a.`id`)" points to
 
    a field in a temporary table where the result will be stored. Thus,
 
    to look behind the item_ref, Spider need to generate the query earlier.
 
    
    A possible fix would be to generate a query to send in
 
    spider_create_group_by_handler(). However, the fix requires a
 
    considerable amount of changes of the Spider's GROUP BY handler.
 
    I'd like to avoid that.
 
    
    So, I fix the problem by not to use the GROUP BY handler when a
 
    query contains Item_ref whose table_name, name, and alias_name_used
 
    are not set.
 
 
 
 .../mysql-test/spider/bugfix/r/mdev_25116.result   | 33 +++++++++++++++++++
 
 .../mysql-test/spider/bugfix/t/mdev_25116.cnf      |  3 ++
 
 .../mysql-test/spider/bugfix/t/mdev_25116.test     | 37 ++++++++++++++++++++++
 
 storage/spider/spd_db_conn.cc                      |  3 +-
 
 4 files changed, 74 insertions(+), 2 deletions(-)
 
 create mode 100644 storage/spider/mysql-test/spider/bugfix/r/mdev_25116.result
 
 create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_25116.cnf
 
 create mode 100644 storage/spider/mysql-test/spider/bugfix/t/mdev_25116.test

Generated at Thu Feb 08 10:15:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.