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

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

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.9.6, 10.9.7, 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.7(EOL)
    • 10.5, 10.6, 10.11
    • Optimizer
    • Linux Debian jemalloc ldd (Debian GLIBC 2.31-13+deb11u6) 2.31

    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 .

      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]


      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

      query in the attached file:

      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

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26301 Split optimization refills temporary table too many times

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32571 A specific query crashes MariaDB in choose_best_splitting

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32624 Mariadb crashes with query

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32643 "[ERROR] mysqld got signal 11" with one SQL

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31818 Server crashes in choose_best_splitting

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32064 Crash when searching for the best split of derived table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32602 Server crash after query

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            stephane@skysql.com VAROQUI Stephane created issue -
            stephane@skysql.com VAROQUI Stephane made changes -
            Field Original Value New Value
            Attachment mycrashingquery.zip [ 70455 ]
            stephane@skysql.com VAROQUI Stephane made changes -
            Affects Version/s 10.9.6 [ 28520 ]
            stephane@skysql.com VAROQUI Stephane made changes -
            stephane@skysql.com VAROQUI Stephane made changes -
            Summary  crash on update query using correlated subquery after miner update  crash on update query using correlated subquery after minor update
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            danblack Daniel Black made changes -
            Fix Version/s 10.9 [ 26905 ]
            danblack Daniel Black made changes -
            Assignee Sergei Petrunia [ psergey ]
            stephane@skysql.com VAROQUI Stephane made changes -
            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 Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]
            rdem Richard DEMONGEOT made changes -
            alice Alice Sherepa made changes -
            psergei Sergei Petrunia made changes -
            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 Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Status Needs Feedback [ 10501 ] Open [ 1 ]
            alice Alice Sherepa made changes -
            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 Alice Sherepa made changes -
            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 Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Labels regression
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.10 [ 27530 ]
            alice Alice Sherepa made changes -
            psergei Sergei Petrunia made changes -
            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 Julien Fritsch made changes -
            Fix Version/s 11.0 [ 28320 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 165880 114010
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.1 [ 28549 ]
            Roel Roel Van de Paar made changes -
            Labels regression regression stack-smashing
            Roel Roel Van de Paar made changes -
            Affects Version/s 11.2 [ 28603 ]
            Roel Roel Van de Paar made changes -
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.7 [ 29815 ]
            Roel Roel Van de Paar made changes -
            Labels regression stack-smashing regression sporadic stack-smashing
            Roel Roel Van de Paar made changes -
            Affects Version/s 11.7 [ 29815 ]
            Roel Roel Van de Paar made changes -
            Fix Version/s 11.7 [ 29815 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.1(EOL) [ 28549 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.2(EOL) [ 28603 ]

            People

              psergei Sergei Petrunia
              stephane@skysql.com VAROQUI Stephane
              Votes:
              3 Vote for this issue
              Watchers:
              12 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.