[MCOL-5000] ALTER TABLE ADD COLUMN breaks on Columnstore Replica Created: 2022-02-24  Updated: 2023-11-17  Resolved: 2022-10-24

Status: Closed
Project: MariaDB ColumnStore
Component/s: DDLProc, MDB Plugin
Affects Version/s: 6.2.3, 6.3.1
Fix Version/s: 22.08.8

Type: Bug Priority: Blocker
Reporter: Edward Stoever Assignee: Gagan Goel (Inactive)
Resolution: Fixed Votes: 2
Labels: None

Sprint: 2021-17
Assigned for Review: Roman Roman
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

Tested on Columnstore cluster version 6.2.3, the following command:

alter table test add column (col2 varchar(100));

breaks replication on replica nodes:

2022-02-23 20:25:42 7 [ERROR] Slave SQL: Error 'Got error 1815 "Unknown error 1815" from storage engine ColumnStore' on query. Default database: 'yy'. Query: 'alter table test add column (col2 varchar(100))', Gtid 0-10-39, Internal MariaDB error code: 1030
 
2022-02-23 20:25:42 7 [Warning] Slave: Got error 1815 "Unknown error 1815" from storage engine ColumnStore Error_code: 1030
 
2022-02-23 20:25:42 7 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000002' position 806132326; GTID position '0-10-38'

Problem discovered in Columnstore training.

 Comments   
Comment by Kyle Hutchinson [ 2022-05-03 ]

I ran into this error as well, but figured out how to recover from it. On the replica node, I attempted to manually run the alter table statement. This gave me the following error:

ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed:  AlterTableProcessor::addColumn Internal add column error for [schema].[table].[column]. Column exists already. Your table is probably out-of-sync

I realized that when I queried information_schema.columnstore_columns on the broken replica for the table name, I got all of the columns I expected. However, when I queried information_schema.columns, it was missing the newly added column.
This lead me to this comment on MCOL-1733 that lead me to discover that 

 alter table test add column (col2 varchar(100)) comment 'schema sync only';

could fix my issue. After running it on the broken replica, I now saw the "missing" column in the information_schema.columns table and when I ran a 

 show create table [table_name]

command.
After that I skipped the problematic replication statement and started replication back up.

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

Build verified: 22.11.01 (Drone #5781)

PM1:

--------------
 
drop database if exists mcol5000_db
 
--------------
 
 
 
Query OK, 0 rows affected, 1 warning
 
 
 
--------------
 
create database mcol5000_db
 
--------------
 
 
 
Query OK, 1 row affected
 
 
 
--------------
 
create table test (c1 int, c2 char(15)) engine=columnstore
 
--------------
 
 
 
Query OK, 0 rows affected
 
 
 
--------------
 
insert into test values (1,'hello')
 
--------------
 
 
 
Query OK, 1 row affected
 
 
 
--------------
 
insert into test values (2,'world')
 
--------------
 
 
 
Query OK, 1 row affected
 
 
 
--------------
 
alter table test add column (col3 varchar(100))
 
--------------
 
 
 
Query OK, 0 rows affected
 
Records: 0  Duplicates: 0  Warnings: 0

PM2

--------------
 
show create table test
 
--------------
 
 
 
*************************** 1. row ***************************
 
       Table: test
 
Create Table: CREATE TABLE `test` (
 
  `c1` int(11) DEFAULT NULL,
 
  `c2` char(15) DEFAULT NULL,
 
  `col3` varchar(100) DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3
 
1 row in set
 
 
 
--------------
 
select * from test
 
--------------
 
 
 
c1	c2	col3
 
1	hello	NULL
 
2	world	NULL
 
2 rows in set

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