Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23383

Server crashes in JOIN_CACHE::get_offset with certain join_buffer_space_limit

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
    • 10.4, 10.5, 10.6
    • Optimizer
    • None

    Description

      I am setting it to Minor despite it being a non-debug crash because of the low value of join_buffer_space_limit in the test, which can be considered a corner case. However, strangely it doesn't crash when the value is even lower, so it needs to be made sure that the problem is indeed in the extremely low limit and not in the certain (scalable) combination of the limit and the data volume.
      Still, even if the problem is specific to the low value, it's worth ensuring that it doesn't crash, given that the session values can be changed by anyone.

      The explicit setting of optimize_join_buffer_size switch is for 10.2-10.3, in 10.4+ it's on by default.

      It's possible that the query can be simplified further, I didn't manage to do it in a reasonable time.

      CREATE TABLE t1 (
          a varchar(1024) CHARACTER SET utf8,
          b varchar(1024) CHARACTER SET utf8,
          c int
      ) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('a','b',1),('c','d',2);
       
      CREATE TABLE t2 (
          d varchar(1024) CHARACTER SET utf8,
          e varchar(8),
          f int
      ) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('g', 'h', 5) , ('i', 'j', 6);
       
      CREATE TABLE t3 (g int) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (3),(4);
       
      CREATE TABLE t4 (h varchar(8)) ENGINE=MyISAM;
      INSERT INTO t4 VALUES ('e'),('f');
       
      SET join_buffer_space_limit= 16384, optimizer_switch= 'optimize_join_buffer_size=on';
       
      SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a;
       
      # Cleanup
      DROP TABLE t1, t2, t3, t4;
      

      10.2 6e09e7c1

      #3  <signal handler called>
      #4  0x000055fbad3c9fd3 in JOIN_CACHE::get_offset (this=0x7f478405a0c0, ofs_sz=4, ptr=0x7f47a5a5a5a1 <error: Cannot access memory at address 0x7f47a5a5a5a1>) at /data/src/10.2/sql/sql_join_cache.h:119
      #5  0x000055fbad3ca0b9 in JOIN_CACHE::get_rec_ref (this=0x7f478405a0c0, ptr=0x7f47a5a5a5a5 <error: Cannot access memory at address 0x7f47a5a5a5a5>) at /data/src/10.2/sql/sql_join_cache.h:380
      #6  0x000055fbad3c549b in JOIN_CACHE::get_record_by_pos (this=0x7f478405a1f0, rec_ptr=0x7f47a5a5a5a5 <error: Cannot access memory at address 0x7f47a5a5a5a5>) at /data/src/10.2/sql/sql_join_cache.cc:1645
      #7  0x000055fbad3c5421 in JOIN_CACHE::get_record (this=0x7f478405a320) at /data/src/10.2/sql/sql_join_cache.cc:1613
      #8  0x000055fbad3c833e in JOIN_CACHE_BNL::read_next_candidate_for_match (this=0x7f478405a320, rec_ptr=0x7f4784062f65 "") at /data/src/10.2/sql/sql_join_cache.cc:3585
      #9  0x000055fbad3c6664 in JOIN_CACHE::join_matching_records (this=0x7f478405a320, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2292
      #10 0x000055fbad3c5fd2 in JOIN_CACHE::join_records (this=0x7f478405a320, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2087
      #11 0x000055fbad3c6172 in JOIN_CACHE::join_records (this=0x7f478405a1f0, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2133
      #12 0x000055fbad3c6172 in JOIN_CACHE::join_records (this=0x7f478405a0c0, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2133
      #13 0x000055fbad2a1de2 in sub_select_cache (join=0x7f4784052e00, join_tab=0x7f478405dc60, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18652
      #14 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d8b0, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
      #15 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d500, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
      #16 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d150, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
      #17 0x000055fbad2a1805 in do_select (join=0x7f4784052e00, procedure=0x0) at /data/src/10.2/sql/sql_select.cc:18419
      #18 0x000055fbad27b271 in JOIN::exec_inner (this=0x7f4784052e00) at /data/src/10.2/sql/sql_select.cc:3638
      #19 0x000055fbad27a72c in JOIN::exec (this=0x7f4784052e00) at /data/src/10.2/sql/sql_select.cc:3433
      #20 0x000055fbad27b8e2 in mysql_select (thd=0x7f4784000af0, tables=0x7f4784012780, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f47840183d8, unit=0x7f47840046e8, select_lex=0x7f4784004e28) at /data/src/10.2/sql/sql_select.cc:3833
      #21 0x000055fbad26faa0 in handle_select (thd=0x7f4784000af0, lex=0x7f4784004628, result=0x7f47840183d8, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:361
      #22 0x000055fbad23b4ec in execute_sqlcom_select (thd=0x7f4784000af0, all_tables=0x7f4784012780) at /data/src/10.2/sql/sql_parse.cc:6218
      #23 0x000055fbad231d6d in mysql_execute_command (thd=0x7f4784000af0) at /data/src/10.2/sql/sql_parse.cc:3524
      #24 0x000055fbad23f223 in mysql_parse (thd=0x7f4784000af0, rawbuf=0x7f4784012458 "SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a", length=181, parser_state=0x7f47a0297610, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7733
      #25 0x000055fbad22d54f in dispatch_command (command=COM_QUERY, thd=0x7f4784000af0, packet=0x7f478408d1a1 "", packet_length=181, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1824
      #26 0x000055fbad22bfca in do_command (thd=0x7f4784000af0) at /data/src/10.2/sql/sql_parse.cc:1377
      #27 0x000055fbad382099 in do_handle_one_connection (connect=0x55fbb0c12cf0) at /data/src/10.2/sql/sql_connect.cc:1336
      #28 0x000055fbad381e04 in handle_one_connection (arg=0x55fbb0c12cf0) at /data/src/10.2/sql/sql_connect.cc:1241
      #29 0x000055fbadb98aa8 in pfs_spawn_thread (arg=0x55fbb0c1dae0) at /data/src/10.2/storage/perfschema/pfs.cc:1869
      #30 0x00007f47a76eb4a4 in start_thread (arg=0x7f47a0298700) at pthread_create.c:456
      #31 0x00007f47a581fd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
      

      Reproducible the same way on debug, non-debug and asan builds.
      Couldn't reproduce on 10.1.
      Couldn't reproduce at least with this test case with InnoDB or Aria instead of MyISAM.

      Execution plan:

      explain extended
      SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	SIMPLE	t2a	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	SIMPLE	t3a	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	SIMPLE	t2b	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      1	SIMPLE	t3b	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
      1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2a`.`d` AS `d`,`test`.`t2a`.`e` AS `e`,`test`.`t2a`.`f` AS `f`,`test`.`t3a`.`g` AS `g`,`test`.`t2b`.`d` AS `d`,`test`.`t2b`.`e` AS `e`,`test`.`t2b`.`f` AS `f`,`test`.`t3b`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t1` left join (`test`.`t2` `t2a` left join `test`.`t3` `t3a` on(`test`.`t3a`.`g` = `test`.`t1`.`c`) join `test`.`t2` `t2b` join `test`.`t3` `t3b`) on(`test`.`t2a`.`f` = `test`.`t1`.`c` and `test`.`t3b`.`g` = `test`.`t1`.`c` and `test`.`t2a`.`d` = convert(`test`.`t2b`.`e` using utf8)) left join `test`.`t4` on(convert(`test`.`t4`.`h` using utf8) = `test`.`t1`.`a`) where 1
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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