PDA

View Full Version : Mariadb: Can't set identity on creating of table



Joseph Mullins
22-Mar-2022, 09:12 PM
When creating a new table, you can't set the planned identity field without saving, even if it meets the criteria.

15284

This error is prompted.

Steps to reproduce

Create a new table
Create a column called ID, Numeric, size 9.0
Change property "is identity" to "True"
Get the above error, even though it has no decimals.


Work around
Save the table, then make identity, and save again. But this is not obvious at first.

Martin Moleman
23-Mar-2022, 04:25 AM
Thanks for reporting. I could reproduce the issue. We will look into it.
As you found you can work around by doing in 2 steps.

Joseph Mullins
26-May-2022, 03:26 PM
Hey Martin,

Any update on this? It seems we can't even create an identity column anymore since the latest release. I have confirmed this on a fresh project with screenshot attached.

Steps:

Create new table
Create ID column
Create Primary Index
Set Identity before save, failed as OP.
Save without Identity, Saved successful.
Set to Identity, and save. Errored in image, can't set a column to Identity


General Error. 42000
Incorrect column specifier for column 'ID' Table=testtable (1), Source=ODBC_DRV.Structure_End.CreateTable


15444

Martin Moleman
13-Jun-2022, 06:57 AM
Well, that was a tough one. Eventually I found the difference with your first post was the ID column defined as 10.0 where in the opening post it was 9.0.

As it turns out, MySQL/MariaDB does not allow auto_increment on decimal columns, only on integer columns.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types.


In this case you had defined the column as numeric(10.0) The odbc_drv makes this a numeric(10.0) on MySQL and that can not have auto_increment.
If you define as numeric 9.0, odbc_drv will make this an integer and auto_increment will work.

I will see if we can improve on the error here.

Joseph Mullins
13-Jun-2022, 01:29 PM
Thanks Martin,

I didn't realize 9.0 would make it an int. When I created an identity in the background, it seems the studio is still showing the integer as a 10.0 numeric; even after reconnecting.

I have just tested and can confirm changing to 9.0 in studio works; although it still has the original error of not being able to set on create.

IJmert
15-Jun-2022, 10:25 AM
We configure the studio (tab database) to edit using native SQL data types. Much better. Than you can select tinyint, smallint, integer, bigint depending on your needs. Much much better.

Martin Moleman
16-Jun-2022, 02:02 AM
Yes, entering native types is much easier, but currently this only works for mssqldrv and db2_drv.

With odbc_drv (used in this case for MySQL/MariaDB) you enter the DataFlex type and not the SQL native type.

Martin Moleman
16-Jun-2022, 02:25 AM
Yes, the handling of sizes is a bit confusing in this case.

When creating a new column there is mapping from DataFlex type to native type:

DF Num 9.0 is mapped to native integer (because the max integer value (2147483647) fits in 9.0)

DF Num 10.0 is mapped to native nuneric(10.0) (because it can contain values larger than the max integer value)

When opening (or connecting to) existing column, there is a native type to DataFlex type mapping


Native integer maps to DF Num(10.0) Because the max integer value (2147483647) is 10 digits.

As IJmert mentions it would be easier if you could enter native types in the Studio, but currently not possible with odbc_drv.


The original issue has been fixed for the next version.