Mariadb connection/connector questions

English
#1

Hi
I’m on a laptop running Linux Mint 19.3 and LibreOffice 7.3.4.2
and on a desktop running Linux Mint 19.3 and LibreOffice 7.3.3.2
and I’m using LibreOffice Base connecting to a mariadb database.

I’m working on a hobby project and started out setting up an embedded hsqldb database and then an older version of the hsqldb split database and then updated to hsqldb 2.3.2 not too long ago.

I decided to try to set up mysql in a server relationship (and then decided on mariadb) to learn about connecting to the database myself (rather than let the split database wizard take care of it for me) and to learn setting up multiple users.

I have a connection now using a mysql connector, but I was having some problems getting there and wanted to see whether anyone can tell whether I am doing something wrong.

I read posts that talked about opening a libreoffice file (I chose writer) going to the tools and options and advanced selections and selecting the “class path” button on the right, then choosing “add archive” from the box that comes up and navigating to the location of the connectors in the window that comes up.

Prior to that I had used the file manager and located the jar files for the connectors at /usr/share/java/ but in the window I had gotten to above from libreoffice for selecting archives, when I navigate to the /usr/share/ contents it doesn’t even show a java folder that I could open to look for the jar files.

I ended up copying both my mysql and mariadb jar files and pasting a copy on my desktop folder. After doing that, I could find both of those through the libreoffice process of adding an archive to the class path that I mentioned above.

After doing that, when I opened LO Base and chose to connect to an existing database (which I created in mariadb through the command line and named picturemgt) and selected mysql from the dropdown list and next selected the connect using JDBC option and next entered my database name and localhost for the server and the default port of 3306,
I pushed the test class button using the default that was in the box saying com.mysql.jdbc.Driver and also using org.mariadb.jdbc.Driver. In both cases it told me that the JDBC driver was loaded successfully.

When I next enter my mariadb username and password and test the connection, I only get a successful connection with the mysql driver.

Lastly before the questions, even after getting a local connection on the computer where I have the mariadb files, I couldn’t make a connection from my laptop to the desktop computer with either connector. I found out that by default mariadb listens only on 127.0.0.1 so I made some changes. So where I said above that I originally was testing the connection with the server named as localhost, I currently have the desktop computer’s IP address in the server field and asked mariadb to listen for that IP address as well as the IP address for my laptop and I have a working connection from my laptop. I think there was also some tweaking with the ufw firewall.

So I’m wondering:

  1. why couldn’t I get to the /usr/share/java/mysql-… and mariadb-… jars through the libreoffice process
  2. where should those jar files go if not in /usr/share/java/
  3. even though I have (or libreoffice has) a working connection through the mysql connector, is there something else I should have done differently to get the mariadb connector to work
  4. the computer where my new database lives had an installation of mysql from four or five years ago and the java folder had an existing connector that I first copied onto my desktop to try and connect with. It was 5.1.45. I realized that 8.0.29 is a more current version and downloaded that and also copied it to my desktop folder so that I could point libreoffice to it. How do I know which com.mysql.jdbc.Driver is being used?

Any thoughts or suggestions?

#2

Long text - didn’t read all …

  • JDBC connection is slower than direct connetion. Since LO 6.2 the connector is included in packages from LO.

  • Simplest way for getting JDBC-connections: Add a *.jar as classpath in LO. I have added mariadb-java-client-2.4.1.jar

  • Connection will be made with org.mariadb.jdbc.Driver, server will be localhost on a local system (or if you connect to server by ssh-tunnel).

But remember: If the connectin with JDBC is much slower, if you connect to tables with many rows. Seems to be a problem with Java, because it is the same with internal HSQLDB: While scrolling to last row through a table with 30 000 rows and 15 columns I will need 30 seconds in internal HSQLDB and also JDBC-connected MariaDB. Connecting with direct connection (or internal Firebird) will do this job in 1 second here.

#3

menu:Tools>Options… [Ctrl+F12] → Advanced
[Class Path…] point to /usr/lib/java/mysql.jar
Restart the office suite.

#4

There are 4 different ways to connect MariaDB/MySQL with Base.
When you choose database type “MySQL” while connecting a new database you get 3 options:

  1. ODBC
  2. JDBC
  3. Direct (SDBC)
    You may also choose database type “JDBC” and provide a valid jdbc:mysql: URL.
    Bildschirmfoto von 2022-06-25 14-38-41
    URL = sdbc:mysql:jdbc:localhost:3306/TESTDB

Bildschirmfoto von 2022-06-25 14-35-22
URL = jdbc:mysql://localhost:3306/TESTDB

Bildschirmfoto von 2022-06-25 14-36-40
URL = sdbc:mysql:mysqlc:LOCALHOST:3306/TESTDB

The driver class for both JDBC connections is com.mysql.jdbc.Driver

Apart from ODBC I successfully tried 3 connections MySQL(JDBC), jdbc:mysql and SDBC.
The most irritating issue with connections to external databases may be that you need to have some database up and running before you can connect to it. Base will not create a new MariaDB/MySQLDB for you.

#5

Thanks for your reply.
I do have a working mysql jdbc connection and after @RobertG mentioned the direct connection, I set up one of those.

But my original question was related to the aspect of pointing libreoffice to the connectors. When I go through the process of tools/options/advanced/classpath/addarchive in lbreoffice and attempt to navigate to the java file located at /usr/share/java/, libreoffice couldn’t even see the java folder and obviously then couldn’t find the jar files.

I copied the jar files to the desktop folder and pointed libreoffice at that, but thought I must be doing something wrong if it won’t look at the java folder.

But I do have a working mysql jdbc connection and now a new direct connection. I guess as long as it is working I won’t worry about why the process of pointing libreoffice to the jar files didn’t seem to work the way everyone seemed to say that it should.

#6

Have tried it. Opend LO

and go to

All *.jar-files will be shown here on OpenSUSE 15.3 64bit rpm Linux. But: Isn’t any file in this folder automatically linked to Java? I have only downloaded the *,jar-files to my home folder. This isn’t a Java path so I have to add the archive …

Older connections up to MySQL 5.1 will run with com.mysql.jdbc.Driver, newer will run with com.mysql.cj.jdbc.Driver. But MariaDB should be connected by the *.jar-file for MariaDB: org.mariadb.jdbc.Driver.

Could connect to MariaDB with all those connections here, but have to use this connection string for MariaDB with MySQL-driver 8.0.29:

jdbc:mysql://localhost/«Databasename»?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.