Mariadb columnstore autoincrement not working

I'm trying to use Mariadb columnstore, but I'm having some errors when inserting data in a table with a autoincrement column defined.

The issue happens when using the JDBC driver.

CREATE TABLE schema.mytable
(
  deaf_id bigint NOT NULL COMMENT 'autoincrement=1',
  name varchar(80) NOT NULL,
  country varchar(14) NOT NULL
) ENGINE=ColumnStore;

Using DBeaver, I try to insert a new data on the table:

INSERT INTO schema.mytable
(name, country)
VALUES('ny', 'usa');

But I get the following error:

SQL Error [1364] [HY000]: (conn:4) Field 'deaf_id' doesn't have a default value

But using the mcsmysql, I can add the data successfully:

MariaDB [schema]> INSERT INTO schema.mytable (name, country) VALUES('ny', 'usa');
Query OK, 1 row affected, 1 warning (0.28 sec)

MariaDB [schema]> select * from schema.mytable;
+---------+------+---------+
| deaf_id | name | country |
+---------+------+---------+
|       1 | ny   | usa     |
+---------+------+---------+
1 row in set (0.07 sec)

MariaDB [bovespa]>

I tried to use the both drivers:

https://downloads.mariadb.com/Connectors/java/connector-java-2.0.1/mariadb-java-client-2.0.1.jar

https://downloads.mariadb.com/Connectors/java/connector-java-1.5.9/mariadb-java-client-1.5.9.jar

Is there any known issue for this ? Or am I missing something ?

Kleyson Rios.

Short answer:

Is not a client or driver issue. It is a Columnstore user privileges issue. Suppose that your user that you use to connect to Columnstore is called 'myuser'. Run the following as root:

-- if necessary replace % below with localhost or whatever IP you use for the connection
GRANT ALL PRIVILEGES ON `infinidb_vtable`.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

Now try to connect again with 'myuser', no matter whether you use DBeaver or whatever client. You should be able to run the insert.

Long answer:

First of all, let's review the proper create table syntax because they are different between Columnstore and Innodb when it comes to defining the autoincrement fields:

-- Columnstore syntax
CREATE TABLE schema.mytable
(
  deaf_id bigint NOT NULL COMMENT 'autoincrement=1',
  name varchar(80) NOT NULL,
  country varchar(14) NOT NULL
) ENGINE=ColumnStore;

-- InnoDB syntax
CREATE TABLE schema.mytable
(
  deaf_id bigint NOT NULL AUTO_INCREMENT,
  name varchar(80) NOT NULL,
  country varchar(14) NOT NULL,
  PRIMARY KEY (`deaf_id`)
) ENGINE=InnoDB;

Don't mix them up as you might get errors or unexpected results.

The issue you described gave me some headaches too in the past. The error below is pretty misleading:

SQL Error [1364] [HY000]: (conn:4) Field 'deaf_id' doesn't have a default value

If you tried to run:

select * from mytable;

you would get a much more useful error:

ERROR 1044 (42000): Access denied for user 'myuser'@'%' to database 'infinidb_vtable'

Based on this error I found this page which gives detailed explanations:

https://mariadb.com/kb/en/mariadb/columnstore-database-user-management/

So, just run the GRANT that I wrote at the beginning of the answer and your problem is solved. Don't forget to run the same grant on all the users that you want to be able to access Columnstore tables.

As a short advice before finishing.. Think twice whether you want to use Columnstore or stick with MySQL/MariaDB InnoDB. We are currently under a Columnstore evaluation. It showed us some really impressive performance when it came to the speed of selects on huge tables. I doubt I could have obtained half of that performance with MySQL/MariaDB even after ellaborate tuning.

But Columnstore has its own drawbacks. I would mention two of them which gave us some headaches:

• the speed of the inserts is poor (up to 20 times slower than on a simillar InnoDB table according to some of our tests)

• Columnstore did not yet reach maturity so it might give you headaches at times

So choose your database engine carefully.

[Edit:]

I forgot to mention that I'm speaking about MariaDB Columnstore 1.0.9. Future versions might show different syntax/behaviour, etc..