[MCOL-2055] insert..select inserts shorter strings when batch insert is disabled, for data typeS BLOCK LONGBLOB TEXT LONGTEXT Created: 2018-12-31  Updated: 2021-04-29  Resolved: 2021-04-29

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.2.2
Fix Version/s: 5.6.1

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

Attachments: File text.n.import.dat    
Issue Links:
Relates
relates to MCOL-4698 Optimize long string handling during DML Open
relates to MCOL-491 Change the data range of Varchar and ... Closed
Sprint: 2021-7

 Description   

Build tested: 1.2.2-1

When executing an insert..select statement from a table that has 5 rows, with batch insert off, all 5 rows were inserted correctly. But when I repeat the same insert..select statement, the first inserted row is shorter than the source row.

I simplified the test case by insert..select only one row, the execute is still correct, but from the 2nd execution and onward, the insert is not correct.

When batch insert is enabled (using cpimort), it does not have this issue.

The same issue occurs to blob, longblob, text, and longtext data types.

MariaDB [mytest]> set infinidb_use_import_for_batchinsert=0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mytest]> desc text1;
---------------------------------+

Field Type Null Key Default Extra

---------------------------------+

c1 int(11) YES   NULL  
c2 text YES   NULL  

---------------------------------+
2 rows in set (0.002 sec)

MariaDB [mytest]> desc text2;
---------------------------------+

Field Type Null Key Default Extra

---------------------------------+

c1 int(11) YES   NULL  
c2 text YES   NULL  

---------------------------------+
2 rows in set (0.002 sec)

MariaDB [mytest]> select * from text1;
Empty set (0.011 sec)

MariaDB [mytest]> select c1, length(c2) from text2;
----------------+

c1 length(c2)

----------------+

1 60000
2 60000
3 60000
4 60000
5 60000

----------------+
5 rows in set (0.096 sec)

MariaDB [mytest]> insert into text1 select * from text2;
Query OK, 5 rows affected (0.383 sec)
Records: 5 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000
2 60000
3 60000
4 60000
5 60000
1 57950
2 60000
3 60000
4 60000
5 60000

----------------+
10 rows in set (0.018 sec)

MariaDB [mytest]> truncate table text1;
Query OK, 0 rows affected (0.499 sec)

MariaDB [mytest]> insert into text1 select * from text2 where text2.c1 = 1;
Query OK, 1 row affected (0.227 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000

----------------+
1 row in set (0.016 sec)

MariaDB [mytest]> insert into text1 select * from text2 where text2.c1 = 1;
Query OK, 1 row affected (0.182 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000
1 54698

----------------+
2 rows in set (0.041 sec)

MariaDB [mytest]> insert into text1 select * from text2 where text2.c1 = 1;
Query OK, 1 row affected (0.270 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000
1 54698
1 57552

----------------+
3 rows in set (0.013 sec)

MariaDB [mytest]> insert into text1 select * from text2 where text2.c1 = 1;
Query OK, 1 row affected (0.246 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000
1 54698
1 57552
1 52242

----------------+
4 rows in set (0.016 sec)

When enabled batch insert (using cpimport), the same insertselect statement worked

MariaDB [mytest]> insert into text1 select * from text2 where text2.c1 = 1;
Query OK, 1 row affected (1.213 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [mytest]> select c1, length(c2) from text1;
----------------+

c1 length(c2)

----------------+

1 60000
1 54698
1 57552
1 52242
1 60000

----------------+
5 rows in set (0.024 sec)

 Comments   
Comment by Daniel Lee (Inactive) [ 2019-04-24 ]

Build tested: 1.2.4-1 (nightly)

ColumnStore:

DML update also has the same issue for LONGBLOB and LONGTEXT
[root@localhost datatypesTextBlob]# cat longtext.o.update.column.sql.tst.log
--------------
update longtext1 t1, longtext2 t2 set t1.c2=t2.c2 where t1.c1=t2.c1 and t2.c1=3
--------------

--------------
select 'longtext1' as tableName, length(c2) from longtext1
--------------

tableName length(c2)
longtext1 10
longtext1 10
longtext1 8928
longtext1 10
longtext1 10
[root@localhost datatypesTextBlob]# cat longtext.n.update.column.sql.tst.log
--------------
update longtext1 t1, longtext2 t2 set t1.c2=t2.c2 where t1.c1=t2.c1 and t2.c1=3
--------------

--------------
select 'longtext1' as tableName, length(c2) from longtext1
--------------

tableName length(c2)
longtext1 10
longtext1 10
longtext1 54464
longtext1 10
longtext1 10

MariaDB Server reference

[root@localhost datatypesTextBlob]# cat longtext.n.update.column.sql.ref.log
--------------
update longtext1 t1, longtext2 t2 set t1.c2=t2.c2 where t1.c1=t2.c1 and t2.c1=3
--------------

--------------
select 'longtext1' as tableName, length(c2) from longtext1
--------------

tableName length(c2)
longtext1 10
longtext1 10
longtext1 120000
longtext1 10
longtext1 10
[root@localhost datatypesTextBlob]# cat longtext.o.update.column.sql.ref.log
--------------
update longtext1 t1, longtext2 t2 set t1.c2=t2.c2 where t1.c1=t2.c1 and t2.c1=3
--------------

--------------
select 'longtext1' as tableName, length(c2) from longtext1
--------------

tableName length(c2)
longtext1 10
longtext1 10
longtext1 140000
longtext1 10
longtext1 10
[root@localhost datatypesTextBlob]#

Comment by David Hall (Inactive) [ 2020-10-29 ]

CREATE TABLE `text1` (`c1` int(11) DEFAULT NULL, `c2` text(65535) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=latin1;
CREATE TABLE `text2` (`c1` int(11) DEFAULT NULL, `c2` text(65535) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=latin1;

Comment by David Hall (Inactive) [ 2020-10-29 ]

The problem seems to only occur if a query is made before the insert and never occurs on the first insert into an empty table.
Example

insert into text1 select * from text2 where text2.c1 = 1;
 
insert into text1 select * from text2 where text2.c1 = 1;
 
insert into text1 select * from text2 where text2.c1 = 1;

Will work, but

insert into text1 select * from text2 where text2.c1 = 1;
 
select c1, length(c2) from text1;
 
insert into text1 select * from text2 where text2.c1 = 1;
 
select c1, length(c2) from text1;
 
insert into text1 select * from text2 where text2.c1 = 1;
 
select c1, length(c2) from text1;

will break

Comment by Jose Rojas (Inactive) [ 2020-12-02 ]

After investigating, I discovered the first block of the dictionary writeengineserver writes to during the insert/select is not found when trying to retrieve the newly inserted value(s). Restarting DMLProc after the insert/select would fix this issue due to a primproc cache flush.

This fix flushes the dict oids from the primproc cache after the batch insert, which allows retrieval of the entire text/blob without having to restart DMLProc.

Comment by Jose Rojas (Inactive) [ 2020-12-02 ]

For QA: To test the fix, you can simply take the attached file and rerun the queries from the decription and observer there is no longer issues retrieving columns with missing data.

The fix only includes a call to flush the dict oids from the cache so running queries from description is a sufficient test.

Comment by Roman [ 2020-12-03 ]

I'm very sorry but we need to investigate case a bit more b/c it doesn't look right if we just flush the cache for all dict oids involved.
Imagine a table with some dict columns that are used by the queries now. Here comes an insert. The patch suggests to just wipe ALL dict blocks from the cache that is the main source of data blocks for primitives code. And that ALL might be a big number. So after we wipe ALL dict blocks from the cache primitive job will spawn a number of threads that will saturate IO once again. It is easy to imagine a scenario that ruins the performance.
insertColumnRecs() is used to insert multiple values into the column so it is used by both INSERT..SELECT with cpimport disabled and INSERT VALUES(),(),() so the number of values inserted should be small enough to fit into a number of blocks.
So the solution might be attacked from two sides:

  • we find the root cause of why the records are smaller then expected
  • we reduce a number of blocks to flush from the cache to those we realy update(writeColumnRec() should have enough info to get them)
Comment by Roman [ 2020-12-03 ]

There is another way. Plz somehow proove the used approach doesn't increase IO and affect the performance of the reading queries running at the same time with the ingestion.

Comment by David Hall (Inactive) [ 2020-12-09 ]

columnstore-5.5.1-1 has been built with the modified cache flush algorithm. I'm moving this to 5.6 for further optimizations.

Comment by David Hall (Inactive) [ 2021-04-27 ]

MCOL-491 has a similar issue. When the maximum of 8000 bytes is relaxed, varchar starts showing a similar behavior. I tried adding the oid flush, but in this case, it didn't help.

Comment by Daniel Lee (Inactive) [ 2021-04-29 ]

Build 5.6.1 ( Drone #2270)

The mentioned test case no longer show the report issue.

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