[MCOL-895] INSERT after ALTER TABLE can corrupt HWM Created: 2017-08-29  Updated: 2020-08-25  Resolved: 2017-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.10, 1.1.0
Fix Version/s: 1.0.12, 1.1.1

Type: Bug Priority: Critical
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: relnote

Sprint: 2017-18, 2017-19, 2017-20, 2017-21

 Description   

This is the test case:

drop table if exists c1;
 
create table c1 (a int, b bigint, c tinyint, d int) engine=columnstore;
 
insert into c1 values (12345,54321,21,98765);
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
insert into c1 select * from c1;
 
 
 
alter table c1 drop column c;
 
alter table c1 add column c bigint;
 
start transaction;
 
insert into c1 select * from c1;
 
commit;
 
insert into c1 select * from c1;

The last insert will error because the previous insert (which is in a transaction to use DMLProc instead of cpimport) has corrupted the HWMs. This is the resulting HWMs (the 4 byte HWMs should match):

*************************** 1. row ***************************
 
          OBJECT_ID: 16306
 
        OBJECT_TYPE: Column
 
LOGICAL_BLOCK_START: 834560
 
  LOGICAL_BLOCK_END: 838655
 
          MIN_VALUE: 12345
 
          MAX_VALUE: 12345
 
              WIDTH: 4
 
             DBROOT: 1
 
       PARTITION_ID: 0
 
         SEGMENT_ID: 0
 
       BLOCK_OFFSET: 0
 
         MAX_BLOCKS: 4096
 
    HIGH_WATER_MARK: 67
 
              STATE: Valid
 
             STATUS: Available
 
          DATA_SIZE: 557056
 
*************************** 2. row ***************************
 
          OBJECT_ID: 16307
 
        OBJECT_TYPE: Column
 
LOGICAL_BLOCK_START: 838656
 
  LOGICAL_BLOCK_END: 846847
 
          MIN_VALUE: NULL
 
          MAX_VALUE: NULL
 
              WIDTH: 8
 
             DBROOT: 1
 
       PARTITION_ID: 0
 
         SEGMENT_ID: 0
 
       BLOCK_OFFSET: 0
 
         MAX_BLOCKS: 8192
 
    HIGH_WATER_MARK: 135
 
              STATE: Invalid
 
             STATUS: Available
 
          DATA_SIZE: 1114112
 
*************************** 3. row ***************************
 
          OBJECT_ID: 16309
 
        OBJECT_TYPE: Column
 
LOGICAL_BLOCK_START: 847872
 
  LOGICAL_BLOCK_END: 851967
 
          MIN_VALUE: NULL
 
          MAX_VALUE: NULL
 
              WIDTH: 4
 
             DBROOT: 1
 
       PARTITION_ID: 0
 
         SEGMENT_ID: 0
 
       BLOCK_OFFSET: 0
 
         MAX_BLOCKS: 4096
 
    HIGH_WATER_MARK: 119
 
              STATE: Invalid
 
             STATUS: Available
 
          DATA_SIZE: 983040
 
*************************** 4. row ***************************
 
          OBJECT_ID: 16310
 
        OBJECT_TYPE: Column
 
LOGICAL_BLOCK_START: 851968
 
  LOGICAL_BLOCK_END: 860159
 
          MIN_VALUE: NULL
 
          MAX_VALUE: NULL
 
              WIDTH: 8
 
             DBROOT: 1
 
       PARTITION_ID: 0
 
         SEGMENT_ID: 0
 
       BLOCK_OFFSET: 0
 
         MAX_BLOCKS: 8192
 
    HIGH_WATER_MARK: 135
 
              STATE: Invalid
 
             STATUS: Available
 
          DATA_SIZE: 1114112
 
4 rows in set (0.19 sec)



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-09-19 ]

Problem is the columnRIDs call for the system catalog is returning columns in the wrong order. Whereas WriteEngine assumes it has got them in the position order which means it adds the HWM additions to the wrong columns.

Comment by Andrew Hutchings (Inactive) [ 2017-09-20 ]

Pull requests for 1.0, 1.1 and 1.2 develop trees

For QA: see the description for a test case. Without the bug fix after the "commit" you will see scrambled HWMs in information_schema.columnstore_extents and information_schema.columnstore_columns has the columns out of position sequence. All will work as normal after the bug fix.

Comment by Daniel Lee (Inactive) [ 2017-10-24 ]

Build verified: Github source for 1.1.1-1

/root/columnstore/mariadb-columnstore-server
commit f6cd94ea167789970db7b5b501569a6549495d10
Merge: 3d846d3 91b2553
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 09:15:58 2017 -0500

Merge pull request #72 from mariadb-corporation/MCOL-982

MCOL-982 Merge MariaDB 10.2.9

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 751f9fbd2f26026983915a0677d6d600be273073
Author: david hill <david.hill@mariadb.com>
Date: Tue Oct 24 14:05:48 2017 -0500

removed duplicaue entries

Verified the test case in the ticket description.

Additional notes for the ticket:

original table schema is:

create table c1 (a int, b bigint, c tinyint, d int) engine=columnstore;

After the alter table, dropping c and adding c again with bigint data type, the table schema is as the following:

a int, b bigint, d int, c bigint

HWM wise, a and d should be the same, b and c should also be the same.

In the ticket description, HWM in row 3 should be the same as in row 1, which is 67. Instead, it has the value of 119.

After the fix, HWMs match now.

Comment by Daniel Lee (Inactive) [ 2017-10-25 ]

Build verified: Github source for 1.0.12-1

/root/columnstore/mariadb-columnstore-server
commit a42eb6d1e74e44c9e8fd9bb8290e6ce7dbf909f5
Merge: 2965fc8 6a14ced
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 3 10:12:33 2017 -0500

Merge pull request #69 from mariadb-corporation/MCOL-940

MCOL-940

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 22f5c04f854d8571fa81ac33a26edc256d3acd48
Merge: 2f19dc2 c132f1b
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 17:39:45 2017 -0500

Merge pull request #300 from mariadb-corporation/MCOL-973-1.0

MCOL-973 remove reference to TEXT in the 1.0 code

Verified with same tests

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