Auto increment with MySQL

[trueriver]

hi, when creating a table in design view in the embedded datbase, it is straightforward to get a primary key that auto-increments. You fill in the key name, select integer (integer), and a yes/no box appears in the lower part of the screen for auto-increment.

The same does not seem to happen when attempting the same in MySQL, whether connected to a localhost database or over the internet. In both cases I am connecting using JDBC with the Oracle recommended connector.

I can create an auto incrmement field by connecting to the mysql database using the mysql client.

I am not clear if I am doing something wrong, forgetting something, or whether this is a gap in the Oracle JDBC class.

=======

Edit 20th Aug: Additional detail

If I open a table created from the mysql cli that has an autofield, the value shows on new records and this works normally and shows in the relevant “cell”, as your screenshot shows.

However, when I edit that table and select the row defining that field, the Autovalue drop down never appears.

If I make any change to that field, it loses its AutoField attribute.

If I copy & paste the table the copy loses the autovalue.

If Base offers to create a new PK for the copy table, the checkbox for AutoValue is greyed out. Altogether it seems clear that Base is not communicationg this fact to the underlying database.

My Base is version 5.2.7.2 and is using Oracle Java 1.8.0.181 so when I downloaded the connector it seemed sensible to use mysql-connector-java8.0.11.jar

When I instaled it, I did the “Test Class” and it seemed to load OK (but then I know it partly works…)

Do you recommend I change to the java.5.1 connector?

If so, should I also downgrade the Java runtime that was installed before I consciously did anything to choose a JRE?RE does your Base use?

And if so, how would I downgrade the runtime? I am worried that other things in my OS might depend on Java 8 if I do downgrade it…

ON the other side of the argument, there is something clunky going on here … I can get Firebird, FlameRobin, and MySQL running ish, but none of them in a fully featured way… the common factor seems to be the JRE as the respctive connectors are different.

I did wonder if the difference was caused by using a local connection rather than the net, but I can’t get AutoValue to work on a test/demo server running the latest MySQL version at db4free.net

In the meantime the work-around is to create those tables using the cli. For production work that is what I would do anyway; for prototyping I prefer to use a GUI to tweak things and test variations…

R~~

[Ratslinger]

Hello,

Have not seen any problems in this area. With localhost:

using:

Edit 2018-08-20:

Have done some further testing on this. JRE does not seem to be a concern. Have tries with various connectors under JDK-10, Open JDK 1.8.0_181, JRE 1.8.0_102 and others.

Creating a NEW table with an auto increment field was no problem using a variety of connectors (including mysql-connector-java-8.0.12.jar).

Copying a table with an auto increment field worked with mysql-connector-java-5.1.42.jar but dropped the auto increment using mysql-connector-java-8.0.12.jar.

When modifying an EXISTING table with auto increment set to NO, none of the connectors would allow resetting to YES using the GUI. HOWEVER, this can be reset from the main screen menu Tools->SQL with:

ALTER TABLE `Table_Name` CHANGE COLUMN `ID` `ID` INT(10) NOT NULL AUTO_INCREMENT ;

where ID is the field name to contain the auto increment.

Certainly appears to be a connector problem in the copy process but not certain exactly where the problem lies with changing the auto increment from NO to YES. Could be either the connector or Base.

[trueriver]

I will put my problem with auto-increment on a new table down to my using an earlier version of Base than you (we discussed this elsewhere – it is not practicable for me to install the latest LO).

I confirm that your ALTER TABLE statement works when used as you described. The same statement also works when applied to the database from a mysql> prompt even while base has a live connection to the database (but pay attention if you try to alter the same table from both directions at once!)

[paradigm]

Using the latest version of LibreOffice. Ver 6.1.0.3, on Ubuntu 18.04.1 LTS. I’m having the same issue as the OP.
I’m using Java-8-openjdk-amd64 + mysql-connector-java-8.0.12.jar.

In order to add auto increment to my tables I had to use MySQL workbench, and it was a pain since data and relationships were already filled in. I’ll have to edit the tables with Workbench from now on so I can avoid going through that again because LO accidentally removes the auto increment.

[Ratslinger]

@paradigm What is not apparent from the original post is it is running on Qubes OS. That OS runs things each in its’ own virtual work space (added security level) and further complicates interaction.

As you can see from my answer, the problems I saw was using the connector you are using and copying tables and SQL took care of the other. I have typically used the Native SDBC connector for a local connection and never had any such problems. It also provides the best performance.