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

signal 11 error on multi-table update - crash in handler::increment_statistics or in make_select or assertion failure pfs_thread == ((PFS_thread*) pthread_getspecific((THR_PFS)))

Details

    • 10.1.18, 10.1.20

    Description

      I got signal 11 error, when I execute the query through HeidiSQL tool.
      But It didn't happen error executing through mysql client in server and toad tool.
      I attached my.cnf , mysqld.err log and the query for test.
      Why did it happen in HeidiSQL ? This tool version is 9.3.0.4984.
      Thanks.

      Attachments

        1. fortestquery.sql
          1.0 kB
        2. my_cnf.txt
          7 kB
        3. mysqld_err.txt
          5 kB

        Activity

          SELECT statements first open tables and lock them, and then they call fix_fields().
          Multi-table UPDATE seems to be a special kind of statement, it opens tables, then does fix_fields calls, then calls lock_tables(). I suppose, the reason for doing things in this order is that it needs to figure out which of the tables should be locked for writing.

          psergei Sergei Petrunia added a comment - SELECT statements first open tables and lock them, and then they call fix_fields(). Multi-table UPDATE seems to be a special kind of statement, it opens tables, then does fix_fields calls, then calls lock_tables(). I suppose, the reason for doing things in this order is that it needs to figure out which of the tables should be locked for writing.

          Need to discuss with igor and/or sanja.

          psergei Sergei Petrunia added a comment - Need to discuss with igor and/or sanja .

          I've checked that fixing Item_func_conv_charset fixes the bug.

          Real fix is to make Item_func_conv_charset storing result on first execute (do not forget constant reporting earlier, and cleaning on cleanup()).

          sanja Oleksandr Byelkin added a comment - I've checked that fixing Item_func_conv_charset fixes the bug. Real fix is to make Item_func_conv_charset storing result on first execute (do not forget constant reporting earlier, and cleaning on cleanup()).
          sanja Oleksandr Byelkin added a comment - - edited

          CREATE TABLE `t1` (
            `CLOSE_YN` varchar(10) COLLATE utf8_bin DEFAULT NULL
          ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
           
           
          CREATE TABLE `t2` (
            `ap_close_to` varchar(8) COLLATE utf8_bin DEFAULT NULL
          ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
           
           
          --delimiter $$
           
          CREATE  FUNCTION `f1`(`P_DC_CD` VARBINARY(50), `P_SYS_DATE` DATETIME) RETURNS datetime
              DETERMINISTIC
              SQL SECURITY INVOKER
          BEGIN
            DECLARE V_SYS_DATE DATETIME;
            SELECT now() AS LOC_DATE INTO V_SYS_DATE ;
            RETURN v_sys_date ;
          END $$
           
          --delimiter ;
           
          update t1 S 
          JOIN
          (
            SELECT CASE
                     WHEN DATE_FORMAT( f1('F01', NOW()) , '%Y%m%d') <= CLOSE_YMD
                       THEN '99991231'
          	     ELSE '' END ACCOUNT_APPLY_YYYYMMDD
            FROM (
                  select case
                           when 'AP'='AP'
                             then ap_close_to
                             end AS CLOSE_YMD
          	from t2
                 ) A 
          ) X  			
          SET S.CLOSE_YN = ''
          where 1=1;
           
          drop function if exists f1;
          drop table t1,t2;
          

          sanja Oleksandr Byelkin added a comment - - edited CREATE TABLE `t1` ( `CLOSE_YN` varchar(10) COLLATE utf8_bin DEFAULT NULL ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ; CREATE TABLE `t2` ( `ap_close_to` varchar(8) COLLATE utf8_bin DEFAULT NULL ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ; --delimiter $$   CREATE FUNCTION `f1`(`P_DC_CD` VARBINARY(50), `P_SYS_DATE` DATETIME) RETURNS datetime DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE V_SYS_DATE DATETIME; SELECT now() AS LOC_DATE INTO V_SYS_DATE ; RETURN v_sys_date ; END $$ --delimiter ; update t1 S JOIN ( SELECT CASE WHEN DATE_FORMAT( f1('F01', NOW()) , '%Y%m%d') <= CLOSE_YMD THEN '99991231' ELSE '' END ACCOUNT_APPLY_YYYYMMDD FROM ( select case when 'AP'='AP' then ap_close_to end AS CLOSE_YMD from t2 ) A ) X SET S.CLOSE_YN = '' where 1=1;   drop function if exists f1; drop table t1,t2;

          This patch is OK to push:

          Date: Tue, 06 Dec 2016 15:43:37 +0100
          To: commits@mariadb.org
          Subject: [Commits] 0b4f89a: MDEV-10713: signal 11 error on multi-table
          update - crash in handler::increment_statistics or in
          make_select or assertion failure pfs_thread == ((PFS_thread*)
          pthread_getspecific((THR_PFS)))

          Perhaps we should change the non-caching constructor to set is_expensive_cache to -1, so the non-caching function works like a regular string function, for example REVERSE.

          bar Alexander Barkov added a comment - This patch is OK to push: Date: Tue, 06 Dec 2016 15:43:37 +0100 To: commits@mariadb.org Subject: [Commits] 0b4f89a: MDEV-10713 : signal 11 error on multi-table update - crash in handler::increment_statistics or in make_select or assertion failure pfs_thread == ((PFS_thread*) pthread_getspecific((THR_PFS))) Perhaps we should change the non-caching constructor to set is_expensive_cache to -1, so the non-caching function works like a regular string function, for example REVERSE .

          People

            sanja Oleksandr Byelkin
            gks3117 sungwon.han
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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