[MCOL-780] auto increment needs to use MariaDB's auto increment methods Created: 2017-06-20  Updated: 2021-12-27  Resolved: 2020-03-11

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

Type: New Feature Priority: Major
Reporter: David Thompson (Inactive) Assignee: Todd Stoffel (Inactive)
Resolution: Won't Do Votes: 3
Labels: community

Issue Links:
Duplicate
is duplicated by MCOL-479 insert omitting not null autoincremen... Closed
is duplicated by MCOL-3620 Auto Increment expects the column to ... Closed
Relates
relates to MCOL-530 autoincrement not updating in catalog... Closed
Epic Link: ColumnStore Compatibility Improvements

 Description   

We have a ColumnStore table with an autoincrement column (defined as a comment in the table, as documented here).

We noticed that none of the usual methods of retrieving the current autoincrement value for the table work:
• SELECT LAST_INSERT_ID()
o Doesn't work, that is, it is not affected by inserts or updates into ColumnStore tables. Works fine for InnoDB.
• SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<SCHEMA>' AND TABLE_NAME = '<TABLE>'
o Always returns NULL;
• mysql_insert_id() (from libmysql)
o Always returns 0.
• JDBC driver calls
o Always return 0.
This is breaking our code, as the ORMs we use (for instance, Rails' Active Record or Scala's SORM) depend on this value being returned and/or available somehow.

Is this a bug? If not, is there any other way to retrieve the current autoincrement value?

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

Can reproduce with python client, create table:

create table autoinc_test(
 
id int,
 
user_id varchar(10))
 
engine=columnstore comment 'autoincrement=id';

then running the following python code will always return 0. MCOL-530 exists for not updating the system catalog which is likely related.

import mysql.connector as mariadb
 
conn = mariadb.connect(user='root', password='', host='172.21.21.7', database='test')
 
cursor = conn.cursor()
 
cursor.execute("INSERT INTO autoinc_test (user_id) VALUES (%s)", (123456,))
 
print cursor.lastrowid
 
cursor.close()
 
conn.close()

Comment by Andrew Hutchings (Inactive) [ 2017-06-20 ]

Our autoincrement implementation is separate to MariaDB's so MariaDB's autoincrement based functions do not work with ColumnStore. Some planning needs to be done to make this works as there are some things to solve around multi-UM setups. For example the handling of the auto_increment_increment variable. That said NDB engine has already solved these problems with MySQL so we should be able to solve them.

I thought we had a Jira for this already but I can't find it.

In the mean time the NEXTVALUE column in calpontsys.syscolumn can get the next autoincrement value.

Comment by MG [ 2017-08-11 ]

On a related note, INSERT of NULL to the ai column gives a warning:

MariaDB [mgtest]> insert into ai select null, null;
Query OK, 1 row affected, 1 warning (1.20 sec)
Records: 1  Duplicates: 0  Warnings: 1

MariaDB [mgtest]> show warnings;
---------------------------------------

Level   Code Message                   

---------------------------------------

Warning 1048 Column 'id' cannot be null

---------------------------------------
1 row in set (0.00 sec)

MariaDB [mgtest]> select * from ai;
--------+

id foo 

--------+

  1 NULL
  2 NULL
  3 NULL

--------+
3 rows in set (0.01 sec)

Comment by Andrew Hutchings (Inactive) [ 2017-12-04 ]

We should implement full auto increment support. Each UM needs to have offset/increment set in server variables with one spare for cpimport.

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