[MCOL-5051] ifnull(column1, '') mediumtext comparison results in Sig 11 kill Exemgr - `ERROR 1815 (HY000): Internal error: InetStreamSocket::readToMagic: Remote is closed' Created: 2022-04-13  Updated: 2022-09-21  Resolved: 2022-08-10

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 5.6.5
Fix Version/s: 22.08.1

Type: Bug Priority: Major
Reporter: Allen Lee (Inactive) Assignee: Andrey Piskunov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 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 = 'プロブレム１セット' OR b.COLUMN_NAME ='プロブレム２セット')
 
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 = 'プロブレム１セット' OR b.COLUMN_NAME ='プロブレム２セット')
    		 
                    and ifnull(a.name,'') = ifnull(b.code,'')
    		 
                    AND ifnull(a.value,'') = ifnull(b.cs_code,'')
    		 
                 );



 Comments   
Comment by Daniel Lee (Inactive) [ 2022-04-19 ]

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

The reported issue exists in this latest build.

Comment by Daniel Lee (Inactive) [ 2022-04-19 ]

Build tested: 5.6.1-1

The issue also exists in 5.6.1-1

Comment by Daniel Lee (Inactive) [ 2022-08-10 ]

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 = 'プロブレム１セット' OR b.COLUMN_NAME ='プロブレム２セット')
 
    -> 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)

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