Comments - LibreOffice Base

I have the following message appear when I try to create relationships between tables in LO Base:

"LibreOffice Base: Relation Design The database does not support relations"

I have access to my database using ODBC through a localhost set up in linux opensuse tumbleweed. I had trouble with auto increment for IDs not functioning when tables and ID incrementation were set from Base. I had to set the start of each table in command lines to have auto increment work. I set up some tables with a bit of data to work on a form when I discovered that selecting Tools -> Relationships gave me the above message.

How do I get relation setting to work through LO Base with mariadb? Why is there a message indicating th relational mariadb database does not support relations?

Richard caid.ca

My system is 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.

Hi,

this has nothing to do with MariaDB, but with LO.

Can you please take a look into this link or similar solutions you get after Google searching the result error from LO and let me know?

Hi Robert,

no problem, let's try to figure out together.

You said that MariaDB appears to be functioning meaning you have installed MariaDB correctl. The same goes for lobase. OK. You said above that "I have access to my database using ODBC through a localhost".

I understood this statement that following instruction in this KB article you managed to connect your MariaDB server with lobase client. And this is the whole meaning of this article.

This article is not showing you how to use all lobase functions with database, it is not intended to be, but is not restricted to present even that.

Btw. I have created this article and have updated with new information and this article is the wiki page, meaning article may have bugs and also you, Robert, if you find a bug, you can edit it and/or add additional information you find important to be visible in article.

I have gone again through all procedure in this KB article and I can confirm that if anyone is following the steps will be able to successfully connect LibreOffice Base client to MariaDB server through ODBC connection, what is the main point of the KB article.

Also I have created new table from lobase "Task" window with auto-increment key column and can confirm that after saving the table, it is visible and working correctly.

Regarding the relationships, relation design is not supported in LibreOffice Base with this ODBC connection type based on this https://ask.libreoffice.org/t/base-the-database-does-not-support-relations/23527/2 where is said "ODBC is broken with a known bug and has to my knowledge not yet been fixed." So it has nothing to do with MariaDB, but with the way how LibreOffice Base handles the relationship with specific driver. And yes, I could get the view of relationship following first link I have shared with you, it is possible to see/edit relationship but not to save (and make changes to data schema), but again this is limitation to lobase in conjuction with ODBC driver and not MariaDB server itself.

See also https://ask.libreoffice.org/t/mysql-relationships-not-shown-till-exit-and-re-enter-base/35111/2

Maybe Relation Design can be used with JDBC https://ask.libreoffice.org/t/how-do-i-set-up-a-jdbc-connector-for-mariadb-or-mysql-in-base/21360/14 but I don't know haven't tried.

Why is there only ODBC connector? I needed to verify in my settings does it work and wanted to contribute through KB. Feel free to add JDBC connection KB :) and let me know does Relation Design works.

Kindly regards

Hi Anel,

Thank you for responding.

I removed ODBC connectors from my computer. Then, I worked on connecting through JDBC.

I was able to set up LOBase with a JDBC connector by downloading mariadb-java-client and mysql-connector-java using YAST (I am running a current version of Opensuse Tumbleweed) and then following this basic set of instructions http://www.giustetti.net/wiki/index.php?title=Connect_libreoffice_to_mariadb.

However, after I fumbled my way through to get relationships working, I found that I could not set my tables properly with auto-increment. I stopped there realizing that I may had solved my problem with ODBC.

I deleted my JDBC odb files, reinstalled both mariadb-connector-odbc and unixodbc using YAST. I did NOT install unixodbc-dev or odbcinst (odbcinst is part of unixodbc_and openssh was already installed. I followed instruction for ini files.

I opened LOBase and follow the path of -- Open LOBase -> connect to an existing database -> MySQL -> Connect using ODBC -> etc. This brought me back to the same problem of Relationships not working. (I already had created mariadb databases and input users from a command line.)

I then went in to edit each table to match the foreign key Field Types to the same Field Type as the primary keys. I had used Field Type Integer[MEDIUMINT] with 7 characters for my primary keys. With Primary and Foreign Key Type Fields matching, Relationships worked with the ODBC connector.

So, to have the instructions you presented on this page work, I had 3 problems that when resolved had tables, auto-increment and Relationships working. These may be common sense to you, but as I am starting out with mariadb, LOBase, and ODBC, this was a learning curve for me:

1. use only letters and numbers in database names - DO NOT use underscores. 2. when "connecting to an existing database", select MySQL and then at the next choice, chose "Connect using ODBC". 3. Ensure that Field Types and character lengths of your Primary and Foreign Keys match BEFORE attempting to create relationships.

Give this a try and see if it works for you. I will slowly begin to get forms running, then queries, and finally reports. I will stop with JDBC at this time, but note that Relationships were working with it but there were a few other kinks still to address.

Thank you for your help

Anel, thank you for responding.

I do agree that mariadb appears to be functioning for me. However, I also have Libreoffice base functioning well for me with its embedded HSQLDB. The problem at hand is the connection between Base and mariadb.

With no offence intended, Mariadb. com is advancing LO Base as a GUI for mariadb. In fact, these comments are on the Mariabd.com page for using an ODBC connector for Base to access mariadb. In that regard, there needs to be some degree of support to ensure that connection works. How that support comes about is not at issue, however I need to access to that support or knowledge base.

I have gone back and reset my connection to mariadb using an ODBC in an alternate manner as suggested by JDBC connector support. Open Base -> connected to an existing database -> MySQL -> Connect using ODBC -> etc. instead of choosing ODBC initially. This resolve problems with creating tables with auto increment and being able to edit tables after they are created. I am also able to select Tools -> Relationships and add tables BUT I cannot create relations either by dragging or by selecting "New Relations".

I am aware that you, Anel, are an accomplished programmer, and I am not. It is precisely that reason why I (and others) need a help.

There is still something missing from the instructions on this page to create a functioning ODBC connection. What am I missing ..... or if there is a problem with ODBC connections, where are there 'current' detailed instructions/information on connecting mariadb with Base using a JDBC connection? Information you linked me to was out of date.

Thank you for your help, Richard caid.ca

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