Comments - LibreOffice Base

I ran into some problems with setting up mariadb with Libreoffice (LO) base. I am not a programmer and it is likely problems I am having are related to that.

I work through a charity to help Indigenous Peoples in Canada. One large problem remote/isolated communities have is administrative capacity, particularly when it comes to data management. In that regard I am working to set up a simple mariadb database with data entry and quiries through LO base. My problem is as folows:

I am unable to connect to mariadb from Libreoffice Base with ODBC. The error I receive is:

“SQL Status: 42000 Error code: 1044 [ma-3.1.10]Access denied for user 'rherbert'@'localhost' to database 'CAID' /home/abuild/rpmbuild/BUILD/libreoffice-7.3.3.1/connectivity/source/drivers/odbc/Otools.cxx:357

For Background:

1. I am using OpenSuse Tumbleweed 20220426 with KDE Plasma 5.24.4.; Libreoffice is 7.3.3.1; Mariadb 10.7.3-2.1 server and client were installed with Tumbleweed’s installation along with libmariadb-devel 3.2.6.1-2 and libmariadb3.

2. Aside from a “root” user in mariadb, I added a user and create a database: “CREATE USER 'rherbert'@'localhost' IDENTIFIED BY 'password123';” “create database CAID;”

3. From YAST: mariadb-connector-odbc 3.1.10-1.9, unixODBC 2.3.11-1.2, and unixODBC-devel 2.3.11-1.2 were installed. odbcinst was not available on YAST but when quireid in a command line, odbcinst was a dependency of unixODBC. openssh-clients was previously installed.

4. Ini files were placed into a folder /.database and run with “sudo odbcinst -i -d -f MariaDB_odbc_driver_template.ini” and “sudo odbcinst -i -s -l -f MariaDB_odbc_data_source_template.ini”

5. In the file: mariaDB_odbc_driver_template.ini is: [MariaDB ODBC 3.1 Driver] Description = MariaDB Connector/ODBC v.3.110-1.9 Driver = /usr/lib64/mariadb/libmaodbc.so

6. In the file: MariaDB_odbc_data_source_template.ini is: [CAIDdb-server] Description=CAIDdb server Driver=MariaDB ODBC 3.1 Driver SERVER=127.0.0.1 USER=rherbert PASSWORD=password123 DATABASE=CAID PORT=3306

7. My computer has a hostname but I am uncertain if that can be used in the DSN, so I used 127.0.0.1 as this can be used as a hostname to access mariadb with “mysql -u rherbert -p -h 127.0.0.1”

8. Using Libreoffice Base (1) I selected an existing database, (2) selected ODBC, (3) choose the name of the data source (CAIDdb-server), (4) entered user as “rherbert” and toggled need password, (5) selected Test connection, (6) entered passwrod123, and received the error message above error message.

What have I done wrong? Do note that I have tried a variety of ways to access mariadb from LO Base before using ODBC as a selection from within an existing database.

As a small aside why is the mariadb command line prompt read “Mariadb [(none)]”. What does the none denote and how, if need be, do I ‘fill-in’ what ‘none’ is?

Thanks, Richard from caid.ca

It could be that you are unable to connect using a hostname - see Troubleshooting Connection Issues.

The "none" represents the database you are connected to. If you switched to a database (e.g with USE), the "none" should be replaced with the name of your selected database.

I appreciate your quick response time.

It turns out my problem [solved] was more basic:

1. I had defined the user "rherbert" in a command line but had not granted privileges to the user. I resolved this by logging into mariadb and adding:

GRANT ALL PRIVILEGES ON *.* to 'rherbert'@'localhost' WITH GRANT OPTION;

2. I also needed to change the SERVER in DSN to "localhost" and adjust the DSN to be more generic as follows:

[CAIDdb-server] Description=CAIDdb server Driver=MariaDB ODBC 3.1 Driver SERVER=localhost USER= PASSWORD= DATABASE=CAID PORT=3306

These changes worked well. I will also be able to have multiple users log in using the more generic DSN.

I am pleased with the connectivity between Libreoffice Base and mariadb - well done mariadb developers!

Thanks, Richard from caid.ca