[MCOL-737] Mariadb columnstore autoincrement not working Created: 2017-06-03  Updated: 2017-08-09  Resolved: 2017-08-09

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.9
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Kleyson Rios Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

github.com/mariadb-corporation/mariadb-columnstore-docker.gi‌​t
mariadb-columnstore-1.0.8-1-centos7.x86_64.rpm.tar.gz and
mariadb-columnstore-1.0.9-1-centos7.x86_64.rpm.tar.gz


 Description   

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

The SHOW WARNINGS command returns an EMPTY SET.

 Comments   
Comment by David Thompson (Inactive) [ 2017-06-05 ]

Thanks, I can reproduce with DBeaver (which i've not used before) but i cannot using Squirrel which is another jdbc based database client, so it appears specific to DBeaver. Will need further investigation to determine if this is a ColumnStore issue, connector issue, or DBeaver issue.

Comment by Kleyson Rios [ 2017-06-05 ]

I'm not sure that is a DBeaver issue because I'm using Pentaho Kettle (PDI) to load data into Mariadb-CS and I'm getting the same error.

Comment by David Thompson (Inactive) [ 2017-06-07 ]

Enabling query logging i can see that DBeaver / jdbc is forcing STRICT_TRANS_MODE which has the effect of enabling the behavior you see. Specifically it executes:
set session autocommit=1,sql_mode='IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

Someone else raised this in a dbeaver forum:
http://dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=1716

I'd assume kettle must be doing the same.

You could also just qualify the auto inc column which is what STRICT_TRANS_MODE requires:
INSERT INTO mytable (deaf_id, name, country) VALUES(0,'ny', 'usa');

Comment by David Thompson (Inactive) [ 2017-06-07 ]

This may be helpful for kettle: http://forums.pentaho.com/showthread.php?217882-Setting-SQL_MODE-for-Mysql-database-only-(Pentaho-stripping-comments)

Let me know if this works.

Comment by Kleyson Rios [ 2017-06-10 ]

Hi,

Using:

jdbc:mysql://localhost:3306/dbtest?sessionVariables=sql_mode='MSSQL'

the autoincrement works fine using Kettle.

Shouldn't this default mode be enforced by the JDBC driver ? I don't understand why the autoincrement (or this MODE) works for some tools, such as Squirrel, and doesn't work for another ones, such as DBeaver and Kettle. Is every application programmed internally to set the initial mode ?

Best Regards.

Comment by David Thompson (Inactive) [ 2017-06-10 ]

It seems that way. Also STRICT_TRANS_TABLES becomes default on with MariaDB 10.2 which will be the base server version for our next ColumnStore release 1.1.

https://mariadb.com/kb/en/mariadb/sql-mode/

Comment by David Thompson (Inactive) [ 2017-08-09 ]

third party tool / connector behavior is the cause.

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