BASE does not recognize the existing primary key

[the ber]

I am using Libreoffice Base versions 6.3 and 6.4 on different Linux systems, as a front end to connect to MariaDB databases. On some of the systems I can add data to the tables, and on some systems I cannot. I discovered that the "defective" systems think that the tables have no primary key. In fact every one of my tables has a primary key.

For example: using Kubuntu 19.10 I can add data to the table. Using the command line I can add data to the table. Using Fedora 31 I cannot add data to the same table, because the table has no primary key (according to Base). If I add the primary key in Base ("Tables...Edit"), the command seems to be executed, but when I recheck the table properties there is still no primary key. Just to emphasize: there is definitely a primary key in the table; Base just doesn't recognize it.

Is there something I can do to convince Base that the primary key does exist?

[Villeroy]

Compare the driver versions and properties of the database documents on the working systems and the failing systems.
See menu:Edit>Database>Properties... of your database document.
There are built-in sdbc drivers, odbc drivers and jdbc drivers to connect a Base document with MariaDB.

[the ber]

Thanks for the reply. I'll get started looking for the differences among the various systems and report back later.

[the ber]

I've done some checking, but I'm afraid I haven't gotten closer to the solution. Here's what I've done so far:

• I checked the database properties inside Base as you suggested, but there is not much information there as far as I can see.

• I looked in the LibreOffice settings: Tools/Options/Advanced/"Open Expert Configuration". Here I searched for 'java' and found a lot more information.

After printing and comparing these java settings for several systems I wasn't able to find a common problem. For example, I have Manjaro installed on two separate machines; one works fine with MariaDB and the other one does not detect the primary keys under Manjaro. Both of these Manjaros have the same drivers listed in the "Expert Configuration":

• For URLPattern['sdbc:mysql:jdbc:*'] the driver value is com.mysql.jdbc.Driver for both systems.

• For URLPattern['jdbc:oracle:thin:*'] the driver value is oracle.jdbc.driver.OracleDriver for both systems.

There is a Java runtime environment installed in each case, but there is not always a Class Path specified. However this seems to make no difference in whether or not the primary keys are recognized.

Is there anything else that I can look for?

[Villeroy]

That was it on the Base side, I'm afraid. I'm not familiar with MySQL/MariaDB.

[the ber]

Thanks very much just the same. I appreciate all the help that you give on this site.

[Villeroy]

I'm running Ubuntu 18.4 vanilla desktop system.
I installed mariadb-server and libreoffice-mysql-connector.
Added a database with an almighty user.
Connected a Base document to the new database using the built-in driver.
The status bar reads: MySQL (native)
Base lets me add a table with primary key.
----------------------------
Then I installed libmysql-java
Added /usr/share/java/libmysql-connector-java.jar to the LibreOffice class path
Connected another Base document to the same database using the JDBC driver.
The status bar reads: MySQL (JDBC)
It shows my table with its primary key.

[robleyd]

A shot in the dark - check your mariadb error logs and maybe transaction logs and see if they give a hint.

[Villeroy]

Without testing ODBC, there is be a second way to get a jdbc connection.
So I connected a third Base document via
File>New>Database...
[X] Connect to existing db and then choose "JDBC".
-----------
URL is: jdbc:mysql://localhost:3306/TESTDB
Driver is: com.mysql.jdbc.Driver
-----------
On a first glance, this connection works as well as the other JDBC connection which was established via "Connect to existing db" of type MySQL and then choose "JDBC" when the wizard offers the 3 choices for MySQL.

The following macro reports the internally stored connection URL, driver class (if any) and driver path (if any).

Code: Select all

Sub showURL_Class()
	src = ThisComponent.DataSource
	url = src.URL
	with src.Settings
		sClass = .JavaDriverClass
		sJar = .JavaDriverClassPath
	end with
	inputbox "","", "URL: "& url & " | " & _
		"Driver Class: "& sClass & " | " & _
		"Driver Path: "& sJar
End Sub

Connection #1: URL: sdbc:mysql:mysqlc:localhost:3306/TESTDB | Driver Class: | Driver Path:
Connection #2: URL: sdbc:mysql:jdbc:localhost:3306/TESTDB | Driver Class: com.mysql.jdbc.Driver | Driver Path:
Connection #3: URL: jdbc:mysql://localhost:3306/TESTDB | Driver Class: com.mysql.jdbc.Driver | Driver Path:
--------------------
Theoretically you can specify a JDBC driver path for individual Base documents. In this case, "Driver Path:" would report the path to that jar file.
My JDBC driver for MySQL/MariaDB is set globally in the LibreOffice Java options: /usr/share/java/libmysql-connector-java.jar which provides the com.mysql.jdbc.Driver for both flavours of JDBC connection.

[the ber]

Sorry Mr. Villeroy, I just noticed your last post today. I have been using workarounds to connect to my databases, since the weather has been too good for spending much study time at the computer. I will get back to testing sometime soon.

[Villeroy]

MySQL has different types of tables. This could be another reason why Base does not detect all table properties. I don't know.