<AutoField> not working

[GordonFM]

Hello!

I’m new to DB administration and keep running into an issue with ‘(AutoFields)’ not working. Here is the exact error:

[MySQL][ODBC 8.0(a) Driver][mysqld-8.0.21]Field ‘ProductIDN’ doesn’t have a default value

Image of error

I am running MySQL 8.0.21 on a host W10 machine. On my client machine, I’m running LibreOffice Base 7.0.1.2 (x64). I am using the ODBC connector/driver and can connect to DB seemingly fine to make and edit tables.

I’m getting an issue where after inputting a few records in a table and then coming back to the table, the ‘ProductIDN’ field that I’m using as a primary key is not auto incrementing anymore. I created a new table and it seems to be working but I am worried it will just stop working unless I know what is going on.

In MySQL workbench I have confirmed that the primary key columns in the working table and the non-working table are tagged as ‘int AI PK’.

Also, possibly related, I am getting the following error when I try to copy a table with a working AutoField:

SQL Status: 42000
Error code: 1064

[MySQL][ODBC 8.0(a) Driver][mysqld-8.0.21]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.products_22 (ProductIDN integer NOT NULL auto_increment,Name varchar(128’ at line 1

Image of error when copying the table

Thoughts?
Thanks for you time!

[frofa]

Hi: There appears to be some recent BUGS relating to MySQL and Base. Perhaps check those first, and post a COMMENT back if they seem to fit your case.

[frofa]

Also, can you give us the full error message (looks like the above error messages you quote by be incomplete). What were the exact steps you took to create the ‘non-working’ table? Can you provide a SCREENSHOT of the Base EDIT window for both the working & non-working tables (or the equivalent info from MySQL workbench).

[frofa]

See my comments above. Revised 1.

[GordonFM]

I’m going to ‘answer’ my own question here because I have made enough progress to continue without constant frustration…

I gave up on using the ODBC driver. The install, configuration and connection for ODBC seemed to work well, however I don’t think it is ‘playing nice’ with MySQL 8.0.21. While using LO Base with ODBC driver - I get more data type options when making a table [like int auto increment] however, I’m finding the syntax ODBC uses to copy tables and interact with data types is just not right and stops my progress.

I was going to try a ‘direct’ connection but could not find the module for LO Base Win64.

So, I went with the JDBC driver. To note, if anyone tries this, before you try connecting, you have to open a ‘local’ or otherwise working database to get into LO Base proper so you can edit the advanced settings to add your JDBC connector path. The JDBC driver has been handling table copying and much better - no errors yet. The only thing it seems the JDBC does not do great is allowing you to configure when you build your table. The auto-increment data types are not listed. This is not a huge deal as I can easily set the data column to ‘AI’ in my MySQL workbench. I’m sure there is some SQL command to do this in the LO Base query window but I’m not familiar enough with syntax yet.

TL;DR
Use the JDBC

Take care and stay safe!