Some limitations of MariaDB ColumnStore and BUG summary 01/27 Update SLTechnology News&Howtos

Some limitations of MariaDB ColumnStore and BUG summary

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Field property limit

1. CHARACTER SET syntax is not supported

MariaDB [test] > create table T1 (id int,name varchar (10) CHARACTER SET utf8)

-> engine=Columnstore

ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type (s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

2. COLLATE syntax is not supported

MariaDB [test] > create table T1 (id int)

-> engine=Columnstore COLLATE=utf8_bin

MariaDB [test] > create table T1 (id int,name varchar (10) COLLATE utf8_bin)

-> engine=Columnstore

3. TEXT/BLOB is not supported

MariaDB [test] > create table T1 (id int,info text)

-> engine=Columnstore

MariaDB [test] >

MariaDB [test] > create table T1 (id int,info blob)

-> engine=Columnstore

4. Timestamp is not supported. All subsequent DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is deleted.

MariaDB [test] > create table T1 (update_time timestamp) engine=Columnstore

MariaDB [test] > create table T1 (update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

-> engine=Columnstore

5. Decimal cannot be greater than 18

MariaDB [test] > create table T1 (money decimal (1913)) engine=Columnstore

ERROR 1815 (HY000): Internal error: CAL0009: (3) Create table failed due to Syntax error: The maximum precision (total number of digits) that can be specified is 18

6. ROW_FORMAT=COMPACT is not supported

MariaDB [test] > create table T1 (id int) engine=Columnstore ROW_FORMAT=COMPACT

7. Varchar up to 8000

MariaDB [test] > create table T1 (name varchar (8001)) engine=Columnstore

ERROR 1815 (HY000): Internal error: CAL0009: (3) Create table failed due to char, varchar and varbinary length may not exceed 8000

8. Bit type is not supported

MariaDB [test] > create table T1 (status bit) engine=Columnstore

9. Comment cannot carry''quotation marks

MariaDB [test] > create table T3 (id int comment 'primary key' 'ID') engine=Columnstore

10. Row overflow, varchar of one line cannot be greater than 65535 (UTF8 is divided by 3)

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

11. Enum enumerated types are not supported

MariaDB [test] > create table T1 (`type` enum ('HOLIDAY','WORKDAY') DEFAULT NULL) engine=Columnstore

12. Zerofill is not supported

MariaDB [test] > create table T2 (id int (6) zerofill) engine=columnstore

BUG

Reserved keywords reserved keywords user, comment, match, key, update, status are not supported

Https://jira.mariadb.org/browse/MCOL-1022

MariaDB [test] > create table user (id int) engine=Columnstore

MariaDB [test] > create table T1 (comment varchar) engine=Columnstore

SQL statement limit

1. If the field of distinct is not in order by, it cannot be sorted.

MariaDB [test] > select distinct apply_time from test ORDER BY id limit 1

ERROR 1178 (42000): The storage engine for the table doesn't support IDB-2022: ORDER BY column not in DISTINCT list.

Https://jira.mariadb.org/browse/MCOL-1036

2. If the query field is not in the group by, it cannot be counted in groups.

MariaDB [test] > select id from test group by qq

ERROR 1815 (HY000): Internal error: IDB-2021: 'test.id' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.

3. Alter does not support change/modify to change field properties.

MariaDB [test] > alter table T1 change id id bigint

ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed: Changing the datatype of a column is not supported

4. Alter does not support multi-column operation and does not support after

MariaDB [test] > alter table T1 add age tinyint,add address varchar

ERROR 1178 (42000): The storage engine for the table doesn't support Multiple actions in alter table statement is currently not supported by Columnstore.

MariaDB [test] > alter table T1 add age tinyint after id

5. Join associated query with different field types reported an error

MariaDB [test] > select t1.id from T1 join T2 on t1.id=t2.cid

ERROR 1815 (HY000): Internal error: IDB-1002:'T1 'and' T2 'have incompatible column type specified for join condition.

6. DML statements will be very slow

DML, i.e. INSERT, UPDATE, and DELETE, provide row level changes. ColumnStore is optimized towards bulk modifications and so these operations are slower than they would be in say InnoDB.

Currently ColumnStore does not support operating as a replication slave target.

Https://mariadb.com/kb/en/library/columnstore-data-ingestion/

7. Importing large quantities of data into text fields reported insufficient memory

Internal error: CAL0001: Insert Failed: IDB-2008: The version buffer overflowed.

Https://jira.mariadb.org/browse/MCOL-1056

8. Replace into and load data infile replace into are not supported

Https://jira.mariadb.org/browse/MCOL-1080

MariaDB official reply: overwrite is currently a low priority because it is a very large project and the implementation cost is expected to be high.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.