Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5051

ifnull(column1, '') mediumtext comparison results in Sig 11 kill Exemgr - `ERROR 1815 (HY000): Internal error: InetStreamSocket::readToMagic: Remote is closed'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.6.5
    • 22.08.1
    • PrimProc
    • None
    • 2021-17

    Description

      Here is table schema and query that causes the Seg 11 Kills Exemgr - restarting at least 3 subprocesses

      CREATE TABLE `tpp_white_list` (
        `TABLE_NAME` mediumtext DEFAULT NULL,
        `COLUMN_NAME` mediumtext DEFAULT NULL,
        `CODE` mediumtext DEFAULT NULL,
        `NAME` mediumtext DEFAULT NULL,
        `CS_CODE` mediumtext DEFAULT NULL,
        `LIST_KBN` mediumtext DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
       
      # You need the data to reproduce - see comments for data
      cpimport client tpp_white_list -E '"' -s "," list.csv 
       
      WITH
      temp1 AS (select 1 as num,'aa1' as name, 111 as VALUE union all
      select 2 as num,'aa2' as name, 112 as VALUE union all
      select 3 as num,'aa3' as name, 113 as VALUE union all
      select 4 as num, NULL as name, 'MDCDX2' as VALUE union all
      select 5 as num, null as name, 115 as VALUE union all
      select 4 as num, "" as name, null as VALUE union all
      select 5 as num, null as name, null as VALUE union all
      select 4 as num,'*1068' as name, '99D01' as VALUE union all
      select 5 as num,'*1554' as NAME, '99D01' as VALUE union all
      select 4 as num,'0063001' as NAME, '99D01' as VALUE
      )
      SELECT COUNT(1)
      from temp1 a
      where exists (select 1 from tpp_white_list b
      WHERE b.TABLE_NAME = 'IF001'
      AND (b.COLUMN_NAME = 'プロブレム1セット' OR b.COLUMN_NAME ='プロブレム2セット')
      and ifnull(a.name,'') = ifnull(b.code,'') 
      AND ifnull(a.value,'') = ifnull(b.cs_code,'') 
      );
      

      What actually happens
      Debug.log

      Apr 19 15:10:09 ip-172-31-30-7 systemd: mcs-exemgr.service: main process exited, code=killed, status=11/SEGV
      

      Client:

      ERROR 1815 (HY000): Internal error: InetStreamSocket::readToMagic: Remote is closed
      

      What clients expect to happen
      No error or restart of exemgr & ddlproc & WriteEngineServer - just the response similar to the work arounds

      +----------+
      | COUNT(1) |
      +----------+
      |        3 |
      +----------+
      1 row in set (6.962 sec)
      

      Work arounds

      • Removing the ifnull functions around the medium text
      • using varchar(1000) instead of mediumtext
      • write query with a subselect

        # Subselect re-write
        WITH
        temp1 AS (select 1 as num,'aa1'     as name, 111 as VALUE union all
                  select 2 as num,'aa2'     as name, 112 as VALUE union all
                  select 3 as num,'aa3'     as name, 113 as VALUE union all
                  select 4 as num, NULL     as name, 'MDCDX2' as VALUE union all
                  select 5 as num, null     as name, 115 as VALUE union all
                  select 4 as num, ""       as name, null as VALUE union all
                  select 5 as num, null     as name, null as VALUE union all
                  select 4 as num,'*1068'   as name, '99D01' as VALUE union all
                  select 5 as num,'*1554'   as NAME, '99D01' as VALUE union all
                  select 4 as num,'0063001' as NAME, '99D01' as VALUE 
                 )
        ,temp2 as (select * from tpp_white_list)
        SELECT COUNT(1)
        from temp1 a
        where exists (select 1 from temp2 b
                      WHERE b.TABLE_NAME = 'IF001' 
                        AND (b.COLUMN_NAME = 'プロブレム1セット' OR b.COLUMN_NAME ='プロブレム2セット')
                        and ifnull(a.name,'') = ifnull(b.code,'')
                        AND ifnull(a.value,'') = ifnull(b.cs_code,'')
                     );
        

      Attachments

        Activity

          Build tested: ColumnStore branch develop-6 (#4287)

          The reported issue exists in this latest build.

          dleeyh Daniel Lee (Inactive) added a comment - Build tested: ColumnStore branch develop-6 (#4287) The reported issue exists in this latest build.

          Build tested: 5.6.1-1

          The issue also exists in 5.6.1-1

          dleeyh Daniel Lee (Inactive) added a comment - Build tested: 5.6.1-1 The issue also exists in 5.6.1-1

          Build verified: 22.08-1 (#5243)

          It is no longer an issue in this build.

          [root@s0 /]# cpimport mytest tpp_white_list -E '"' -s "," /data/list.csv
          Locale = en_US.UTF-8Column delimiter : ,
          Enclosed by Character : "
           
          Using table OID 3002 as the default JOB ID
          Input file(s) will be read from : /
          Job description file : /var/log/mariadb/columnstore/data/bulk/tmpjob/3002_D20220810_T175103_S221218_Job_3002.xml
          Log file for this job: /var/log/mariadb/columnstore/cpimport/Job_3002.log
          2022-08-10 17:51:03 (1286) INFO : successfully loaded job file /var/log/mariadb/columnstore/data/bulk/tmpjob/3002_D20220810_T175103_S221218_Job_3002.xml
          2022-08-10 17:51:03 (1286) INFO : Job file loaded, run time for this step : 0.0176101 seconds
          2022-08-10 17:51:03 (1286) INFO : PreProcessing check starts
          2022-08-10 17:51:03 (1286) INFO : input data file /data/list.csv
          2022-08-10 17:51:03 (1286) INFO : PreProcessing check completed
          2022-08-10 17:51:03 (1286) INFO : preProcess completed, run time for this step : 0.0876989 seconds
          2022-08-10 17:51:03 (1286) INFO : No of Read Threads Spawned = 1
          2022-08-10 17:51:03 (1286) INFO : No of Parse Threads Spawned = 3
          2022-08-10 17:51:03 (1286) INFO : For table mytest.tpp_white_list: 3094247 rows processed and 3094247 rows inserted.
          2022-08-10 17:51:04 (1286) INFO : Bulk load completed, total run time : 1.10579 seconds
           
          [root@s0 /]# mariadb mytest
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A
           
          Welcome to the MariaDB monitor.  Commands end with ; or \g.
          Your MariaDB connection id is 15
          Server version: 10.9.2-MariaDB-log MariaDB Server
           
          Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          MariaDB [mytest]> WITH
              -> temp1 AS (select 1 as num,'aa1' as name, 111 as VALUE union all
              -> select 2 as num,'aa2' as name, 112 as VALUE union all
              -> select 3 as num,'aa3' as name, 113 as VALUE union all
              -> select 4 as num, NULL as name, 'MDCDX2' as VALUE union all
              -> select 5 as num, null as name, 115 as VALUE union all
              -> select 4 as num, "" as name, null as VALUE union all
              -> select 5 as num, null as name, null as VALUE union all
              -> select 4 as num,'*1068' as name, '99D01' as VALUE union all
              -> select 5 as num,'*1554' as NAME, '99D01' as VALUE union all
              -> select 4 as num,'0063001' as NAME, '99D01' as VALUE
              -> )
              -> SELECT COUNT(1)
              -> from temp1 a
              -> where exists (select 1 from tpp_white_list b
              -> WHERE b.TABLE_NAME = 'IF001'
              -> AND (b.COLUMN_NAME = 'プロブレム1セット' OR b.COLUMN_NAME ='プロブレム2セット')
              -> and ifnull(a.name,'') = ifnull(b.code,'') 
              -> AND ifnull(a.value,'') = ifnull(b.cs_code,'') 
              -> );
          +----------+
          | COUNT(1) |
          +----------+
          |        3 |
          +----------+
          1 row in set (0.562 sec)
          
          

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: 22.08-1 (#5243) It is no longer an issue in this build. [root@s0 /]# cpimport mytest tpp_white_list -E '"' -s "," /data/list.csv Locale = en_US.UTF-8Column delimiter : , Enclosed by Character : "   Using table OID 3002 as the default JOB ID Input file(s) will be read from : / Job description file : /var/log/mariadb/columnstore/data/bulk/tmpjob/3002_D20220810_T175103_S221218_Job_3002.xml Log file for this job: /var/log/mariadb/columnstore/cpimport/Job_3002.log 2022-08-10 17:51:03 (1286) INFO : successfully loaded job file /var/log/mariadb/columnstore/data/bulk/tmpjob/3002_D20220810_T175103_S221218_Job_3002.xml 2022-08-10 17:51:03 (1286) INFO : Job file loaded, run time for this step : 0.0176101 seconds 2022-08-10 17:51:03 (1286) INFO : PreProcessing check starts 2022-08-10 17:51:03 (1286) INFO : input data file /data/list.csv 2022-08-10 17:51:03 (1286) INFO : PreProcessing check completed 2022-08-10 17:51:03 (1286) INFO : preProcess completed, run time for this step : 0.0876989 seconds 2022-08-10 17:51:03 (1286) INFO : No of Read Threads Spawned = 1 2022-08-10 17:51:03 (1286) INFO : No of Parse Threads Spawned = 3 2022-08-10 17:51:03 (1286) INFO : For table mytest.tpp_white_list: 3094247 rows processed and 3094247 rows inserted. 2022-08-10 17:51:04 (1286) INFO : Bulk load completed, total run time : 1.10579 seconds   [root@s0 /]# mariadb mytest Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 10.9.2-MariaDB-log MariaDB Server   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [mytest]> WITH -> temp1 AS (select 1 as num,'aa1' as name, 111 as VALUE union all -> select 2 as num,'aa2' as name, 112 as VALUE union all -> select 3 as num,'aa3' as name, 113 as VALUE union all -> select 4 as num, NULL as name, 'MDCDX2' as VALUE union all -> select 5 as num, null as name, 115 as VALUE union all -> select 4 as num, "" as name, null as VALUE union all -> select 5 as num, null as name, null as VALUE union all -> select 4 as num,'*1068' as name, '99D01' as VALUE union all -> select 5 as num,'*1554' as NAME, '99D01' as VALUE union all -> select 4 as num,'0063001' as NAME, '99D01' as VALUE -> ) -> SELECT COUNT(1) -> from temp1 a -> where exists (select 1 from tpp_white_list b -> WHERE b.TABLE_NAME = 'IF001' -> AND (b.COLUMN_NAME = 'プロブレム1セット' OR b.COLUMN_NAME ='プロブレム2セット') -> and ifnull(a.name,'') = ifnull(b.code,'') -> AND ifnull(a.value,'') = ifnull(b.cs_code,'') -> ); +----------+ | COUNT(1) | +----------+ | 3 | +----------+ 1 row in set (0.562 sec)

          People

            Andrey Andrey Piskunov (Inactive)
            allen.lee@mariadb.com Allen Lee (Inactive)
            Votes:
            0 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.