Problems connecting mariadb to base

[raffone]

Hi there,

I’m trying to connect a newly created mariadb database to base so I can administrate a company database. I’ve created the database and named it ‘gufdatabase’. I can connect it to dbeaver with no problem so I think I’ve set it up correctly. When I try to connect to base the problems begin. Here are the steps I’ve done so far. I’ve also downloaded the latest version of the Java connector and set it up in the advanced settings in Libre Office.

On the select database page, I’ve selected ‘connect to an existing database’ and selected ‘MySQL’ from the drop-down menu.

On the set up MySQL connection page I’ve selected ‘connect using JDBC’ option’.

On the Set up JDBC connection page I typed or selected the following:
Database name: gufdatabase (the name of the database that I created and can view in dbeaver)
Server: localhost
Port number: 3306

The MySQL JDBC driver class box contains the following:
‘org.mariadb.jdbc.Driver’
When I click the ‘test class’ button I get a dialogue box saying

‘The JDBC driver was loaded successfully’

On the Set up user authentication page the test fails with both with or without the password I set up for the database. (This is the password that works in dbeaver). I’ve stated the username as ‘root’.

If I finish the wizard and create a .odb in base this also fails to connect to the mariadb database. Any ideas where I am going wrong?

[Ratslinger]

Hello,
Tested using:

Version: 7.4.3.2 / LibreOffice Community
Build ID: 1048a8393ae2eeec98dff31b5c133c5f1d08b890
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

on Ubuntu 20.04 with MySQL 8.0.31 and all connected without issue

You do not mention your LO version.
.
You can try instead of:

@raffone

‘connect to an existing database’ and selected ‘MySQL’ from the drop-down menu.

.
use:

 ‘connect to an existing database’ and selected ‘JDBC’ from the drop-down menu.

[RobertG]

What is the error, which is thrown by connecting to the special database?
You could connect through JDBC, seems working.
You should have a username and a password, which is defined for the database. Do you really use ‘root’ as the username?
Screenshot of the message, which appears, will help here.

[raffone]

Hi RobertG,

With some fiddling about and connecting through JDBC I’ve got it working. Thanks so much for taking the time to respond so quickly.

Regards,

Mike

[raffone]

Hi Ratslinger,

With some fiddling about and connecting through JDBC I’ve got it working. Thanks so much for taking the time to respond so quickly.

Regards,

Mike

[LaserM]

I am having the exact same problem but using a remote mariadb server.
Connect to an existing database: MySQL/MariaDB
Connect using JDBC
Database & Server: Same as use in DBeaver
Port: 3306, Driver class: org.mariadb.jdbc.Driver, Tests good.
User name & password, same as used in DBeaver.
ERROR: "The connection could not be established.”

I’m using a Mac (M1) running OS 15.1.1, LO version 24.8.2.1 (AARCH 64), latest version of Java and MariaDB drivers.

I have been trying all sorts of things: making sure I have the latest versions, uninstalling & reinstalling Java, … I have been messing with this for many hours and don’t see what I am doing wrong. It appears that the connector is loading OK but not interfacing with the DB properly. Please note that the same settings in DBeaver and PHPAdmin work fine. Any suggestions?

[Villeroy]

Ask the database administrator of the remote mariadb server about connection details, listening ports, legitimate user accounts. Possibly he/she can give you a valid connection URL for JDBC drivers.

Status bars of 3 Base documents successfully connected to test database “dummy” served by a MariaDB server running on localhost, listening to port 3306.
The first one is a manually entered JDBC-URL (via connection type “JDBC”)
The second one comes from the wizard (via connection type Maria/MySQL, then JDBC).
The third one comes from the wizard (via connection type Maria/MySQL, then MariaDB C connector).
Currently, I have no ODBC driver installed, but it used to be working with ODBC as well.

[LaserM]

Did that already and the connection details work fine on DBeaver, but not on LibreOffice.

[LaserM]

I am embarrassed to say that I had a typo in the server name all this time. It is working fine now.

[LaserM]

I spoke too soon.
LibreOffice is now connecting fine but drops the link in about a minute. There does not seem to be a solution other than closing the DB and re-opening it. I am finding a lot of reports of this happening, but I have not found a good solution. I have read tons of information on the MariaDB connectors and LibreOffice Base advice but still found nothing that works.

To review:
• Connect to an existing database: MySQL/MariaDB on a hosted server service.
• DBeaver works fine but occasionally drops the connection, in which case a reconnect popup box with a button fixes the connection.
• I’m using a Mac (M1) running OS 15.1.1, LO version 24.8.2.1 (AARCH 64)
• latest version of Java and MariaDB drivers (mariadb-java-client-3.5.1.jar) Because I have a Mac with the Apple silicon, it appears that the MariaDB JDBC java connector is preferred.
Connect using jdbc:mariadb://host_name:3306/database_name

• Works fine for about 60 seconds. Is there a parameter I can add to the connection string to keep the connection alive?

[Wanderer]

You may try autoReconnect=true

Connection Closed/Reset to MariaDB Mysql

Hi, I installed MariaDB on a server (ip 192.168.1.245) and I connect with Libreoffice front end and JDBC connector (MariaDB specific) . I can access to the DB from different PCs and for the most part there are no problems, but from two PCs I receive different types of errors: Connection Closed, Connection Reset, the data content could not be loaded when I open a form and an error related to CYGWIN64 is identified Can anyone help me? [error_LO] Buongiorno, ho installato su un server MariaDB…

[LaserM]

@LaserM

mariadb-java-client-3.5.1.jar)

Thanks for the suggestion. Unfortunately, this feature was removed in the 3.0.0 version rewrite.

About MariaDB Connector/J

However, I will try installing a pre 3.0.0 version of the connector and see if this works. But because I have a Mac on the newer silicon platform, older versions of anything are problematic so I try to keep to the latest revisions.

This is really starting to look like a bug in the LibreOffice/MariaDB connector updates.

[iplaw67]

If the feature autoreconnect=True was removed by the Mariadb team, then it is an upstream problem, not something related to LO.

The fact that LO drops the connection over WAN has always been problematic. The previous workaround of using autoreconnect=True was just that, a workaround.

There are many things still not implemented in the sdbc driver code, it is also one of the unfortunate drawbacks of LO Base. Usually though, the jar connectors have tended to have better functionality - did you try with the mysql jdbc connector jar to see whether the autoreconnect string is still supported?

[LaserM]

Thanks for the suggestion. I will try the mysql as well as older versions of the MariaDB version. For now, I have some other things to catchup on and will get back to this in a few days.

I really like the idea of using LibreOffice as a hosted SQL database client for multiple users. I have a few different applications that this inexpensive solution could work for and the possible uses are extensive. To get something useful working to tide me over, I will use a shared .odb file, carefully limiting it to one user at a time. I will still need to work out a hosted DB connector eventually.

If there are any developers watching this, I recommend checking out how DBeaver solved this. Their reconnect popup window works great.

[iplaw67]

Unfortunately, writing java bridging code to handle this probably isn’t high on the list of LO development priorities, given that the overall goal is to reduce reliance on Java code, some day, maybe never. For the rest, it is a matter of trying to prevent bitrot in the existing Base code, already a thankless task.

[mariosv]

@iplaw67

Unfortunately, writing java bridging code to handle this probably isn’t high on the list of LO development priorities,

As I know, none of Java bridges was developed in the project. Even more, LO is trying to avoid any use of Java. That was one of the reasons to choose Firebird for the embedded database.

[LaserM]

It is starting to look like I can’t reliably use LO as a client for the MariaDB hosted server we have been using. According to the MariaDB site, they are only maintaining the java connector for current and future updates.

I really appreciate the great feedback given here.

[Wanderer]

@LaserM

they are only maintaining the java connector

“They” is MariaDB. Have you tried the direct connector provided by LibreOffice/TDF?
.
I have not much experience with the java-connectors, as I use usually ODBC. I remember the use of odbc-drivers was possible on MacOS, but Apple has not provided/removed a possibillity to configure this. There were 3rd-party drivers available when I checked this (around 6 years ago).

[LaserM]

Yes, I tried the ODBC connector and could not get it to work. It is possible I did not have the server address string correct, but I tried several things with it. Excel also uses the ODBC connector to link to databases and I couldn’t get that to work either.

Various versions of this with and without the prefix and suffix:
mariadb://server-name:3306/

I have not yet tried installing older connector versions.

[iplaw67]

ODBC support on macOS is a whole other topic. It can be done, but not via the version of LO obtainable from the Appstore.

When it does run, it has its own issues, due to the lack of ongoing ODBC support development in the LO codebase.

I have used Actual Technologies ODBC driver in the past. Again, it has worked on and off over the years, depending on macOS version, LO version, and their ODBC driver version.