How do I set up a ODBC connector for MariaDB in Base?

[EasyTrieve]

I can’t seem to get LibreOffice Base to open a MariaDB database with ODBC without crashing.

I’m guessing someone is using ODBC with Base and it’s working for you. If so could please say what versions of software you are using and suggest what to use to get it going.

Update: I have recieved reports from people who can open MySQL databases, using the MySQL ODBC connectors in Base. So I have changed the question to focus more narowly on MariaDB.

I have unixODBC and iODBC installed and working. I have 2 drivers (one for MariaDB and one for mdbtools). Also have 4 data sources (DSN’s Data Source Names) I’m testing and they all work with isql.

I’m using:

• Debian 8.6, Jessie-Stable
• MariaDB 10.1 (the latest)
• unixODBC v. 2.3.4 (the latest)
• iODBC v. 3.52.9-2 (the latest)

---

/etc/odbcinst.ini

[MariaDB ODBC 2.0 Driver]
Description	= Maria DB
Driver		= /usr/lib/x86_64-linux-gnu/odbc/libmaodbc.so
Setup		= /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount	= 1

/etc/odbc.ini

[mariadb-bigbase]
#DSN            = mariadb-bigbase    #this is the same as the previous line

Description     = Bigbase
Driver          = MariaDB ODBC 2.0 Driver

#Server         = localhost          #I found that this does not work on my system
Server          = 127.0.0.1
Port            = 3306

#Note: Socket is an option to Server/Port and is slightly faster
#Socket         = /var/run/mysqld/mysqld.sock

#Option is said to be for compatibility with MySQL connector
Option          =

#Not sure what this is for:
Stmt            =

User            = root
Password        = test

Database        = bigbase

---

1) I can confirm that the ODBC driver is working with isql as follows:

#> isql mariadb-bigbase
SQL> show tables
+--------------------------------------------------------------------------+
| Tables_in_bigbase                                                        |
+--------------------------------------------------------------------------+
| locations-all-old0                                                       |
| us-zip-codes                                                             |
+--------------------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched

---

2) Next I open LibreOffice and select File | New to create a new database and get the Database Wizard.

From here I have tried two methods of connecting as follows:

attempted method A:

step 1) Connect to an existing database: MySQL
step 2) Connect using ODBC (Open Database Connectivity) 
step 3) Name of the ODBC data source on your system: mariadb-bigbase
step 4) Enter user name, click Test Connection and enter password gives: 
  The connection was established successfully.
step 5) Select: 
    * Yes, register the database for me.
    X Open the database for editing.
Then click finish and save 'New Database' in ~/Documents.

Result:  "Due to an unexpected error, LibreOffice crashed. ...

attempted method B:

step 1) Connect to an existing database: ODBC
step 2) Name of the ODBC data source on your system: mariadb-bigbase
step 3) Enter user name, click Test Connection and enter password gives: 
  The connection was established successfully.
step 4) Select: 
    * Yes, register the database for me.
    X Open the database for editing.
Then click finish and save 'New Database1' in ~/Documents.

Result:  "Due to an unexpected error, LibreOffice crashed. ...

---

I have submitted a bug report for LO 5.2.2.2, but still hope to find a way to get this to work.

Also check this Q at the MariaDB site.

[doug]

The answer for you may vary. The MariaDB ODBC driver ver 2 does seem to perform competently connecting other applications, but it does not work with LibreOffice. For me, it required both the 64-bit and 32-bit drivers-- this was an architecture thing, and once configured it was able to retrieve the table list for LibreOffice. This was an improvement over the ver 1 driver, which immediately crashed as you described. However, for my database I could load no further data because of data type mismatches of some kind. I have bug-reported type mismatch problems in the past involving MariaDB, connectors (MariaDB report here, LO bugzilla report here) but this is a connector and apparently the teams do not completely see eye-to-eye on how to do that. I don’t really know whether there is a direct connection between the reported problems and the current error message, and have stuck with MySQL connectors, which have become increasingly tenuous (the installer for the most recent version does not work for me). My hope is that MaraDB and LibreOffice generate a viable community-supported ODBC connector.

[EasyTrieve]

What OS are you on? Thanks!

[baqwas]

I have used the distro driver and the MySQL Connector/J drivers (v4.1.42 as well as v4.1.45) but continue to have the following anomaly - LibreBase will retrieve DOUBLE or FLOAT data correctly under Ubuntu 17.10 but under Raspbian Stretch (Raspberry Pi) the data is peculiarly returned as repetitive sets of integer values. Clearly there is some data type conversion issue within LibreBase for the Raspbian Stretch version.

I have no idea of the scope of effort to fix this anomaly but the response from LO folks has been demoralizing (just when I thought I was on a roll with LO products). I don’t know if LO folks care but in the absence of any response (and Raspberry Pi Foundation or MySQL folks can rightly point the finger away from themselves) I have to wonder why this inexplicable behavior persists.

Sorry for the piggyback ride but I feel that my issue is related to the thread here. My final installation of driver relied on the platform independent (4.1.45) version; therefore I do not know how to get 32-bit and 64-bit drivers running concurrently.

[EasyTrieve]

@Baqwas, I think our challenge is to bring a few of us up to speed enough to fix these things ourselves. I’m working on this, but have quite a ways to go before I can even start to fix this sort of issue. I would encourage everyone to keep learning LO with the goal someday of being able to work with the source base.