[MCOL-1709] DDL for creation of a new table in columnstore produces syntax error when it runs on columnstore Created: 2018-09-10  Updated: 2023-10-26  Resolved: 2019-01-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.1.6
Fix Version/s: 1.1.7

Type: Bug Priority: Major
Reporter: Elena Kotsinova (Inactive) Assignee: Zdravelina Sokolovska (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2018-17, 2018-18, 2018-19, 2018-20, 2018-21

 Description   

1. Create a new table in TX:

CREATE TABLE `test` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `col` varchar(100) DEFAULT NULL,
 
  UNIQUE KEY `id` (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

2. Run
mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h localhost -P 4001 cdc_eli test

Result:
Table was not found in Columnstore. A DDL statement is suggested to create a respective table in Columnstore. The DDL contains syntax error:

CREATE TABLE cdc_eli.test (col varchar(100), domain int, event_number int, event_type varchar(50), id serial, sequence int, server_id int, timestamp int) ENGINE=ColumnStore;

Error is:
Error Code: 1069. Too many keys specified; max 0 keys allowed

Problem is in id serial. Columnstore doesn't support this.

 Comments   
Comment by markus makela [ 2018-09-10 ]

Pushed to MCOL-1709 branch.

Comment by Zdravelina Sokolovska (Inactive) [ 2019-01-11 ]

the returned error for non existing table is now suggestion feasable table creation on ]# mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h 127.0.0.1 -P 4001 testA test
2019-01-11 16:02:56 [testA.test] Table not found, create it manually on ColumnStore with:

CREATE TABLE testA.test (col varchar(100), domain int, event_number int, event_type varchar(50), id bigint(20), sequence int, server_id int, timestamp int) ENGINE=ColumnStore;

before: the suggested table cannot be created
MariaDB [testA]> CREATE TABLE cdc_eli.test (col varchar(100), domain int, event_number int, event_type varchar(50), id serial, sequence int, server_id int, timestamp int) ENGINE=ColumnStore;
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

on 1.1.7 suggested table can be created
MariaDB [testA]> CREATE TABLE testA.test (col varchar(100), domain int, event_number int, event_type varchar(50), id bigint(20), sequence int, server_id int, timestamp int) ENGINE=ColumnStore;
Query OK, 0 rows affected (0.614 sec)

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