Comments - LibreOffice Base
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
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