BASE does not recognize the existing primary key

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

BASE does not recognize the existing primary key

Postby the ber » Sun Apr 12, 2020 7:50 pm

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?
LO 7 with split databases, on Windows 7 and various Linux Distros; MariaDB 10.5 on Linux
User avatar
the ber
 
Posts: 23
Joined: Sun Jul 12, 2009 4:26 pm
Location: Germany

Re: BASE does not recognize the existing primary key

Postby Villeroy » Mon Apr 13, 2020 2:39 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE does not recognize the existing primary key

Postby the ber » Mon Apr 13, 2020 9:34 am

Thanks for the reply. I'll get started looking for the differences among the various systems and report back later.
LO 7 with split databases, on Windows 7 and various Linux Distros; MariaDB 10.5 on Linux
User avatar
the ber
 
Posts: 23
Joined: Sun Jul 12, 2009 4:26 pm
Location: Germany

Re: BASE does not recognize the existing primary key

Postby the ber » Mon Apr 13, 2020 2:58 pm

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?
LO 7 with split databases, on Windows 7 and various Linux Distros; MariaDB 10.5 on Linux
User avatar
the ber
 
Posts: 23
Joined: Sun Jul 12, 2009 4:26 pm
Location: Germany

Re: BASE does not recognize the existing primary key

Postby Villeroy » Mon Apr 13, 2020 4:09 pm

That was it on the Base side, I'm afraid. I'm not familiar with MySQL/MariaDB.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE does not recognize the existing primary key

Postby the ber » Mon Apr 13, 2020 5:05 pm

Thanks very much just the same. I appreciate all the help that you give on this site.
LO 7 with split databases, on Windows 7 and various Linux Distros; MariaDB 10.5 on Linux
User avatar
the ber
 
Posts: 23
Joined: Sun Jul 12, 2009 4:26 pm
Location: Germany

Re: BASE does not recognize the existing primary key

Postby Villeroy » Mon Apr 13, 2020 10:48 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE does not recognize the existing primary key

Postby robleyd » Tue Apr 14, 2020 1:11 am

A shot in the dark - check your mariadb error logs and maybe transaction logs and see if they give a hint.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 4119
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: BASE does not recognize the existing primary key

Postby Villeroy » Tue Apr 14, 2020 10:35 am

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE does not recognize the existing primary key

Postby the ber » Sun Apr 26, 2020 7:52 am

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.
LO 7 with split databases, on Windows 7 and various Linux Distros; MariaDB 10.5 on Linux
User avatar
the ber
 
Posts: 23
Joined: Sun Jul 12, 2009 4:26 pm
Location: Germany

Re: BASE does not recognize the existing primary key

Postby Villeroy » Sun Apr 26, 2020 5:01 pm

MySQL has different types of tables. This could be another reason why Base does not detect all table properties. I don't know.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest