Bug 120695 - FILEOPEN unixODBC connection to sqlite database not working
Summary: FILEOPEN unixODBC connection to sqlite database not working
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.3.1 rc
Hardware: x86-64 (AMD64) All
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2018-10-19 06:38 UTC by verlata
Modified: 2019-02-06 11:13 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description verlata 2018-10-19 06:38:36 UTC
Description:
After upgrading to 6.1.3 (from 6.1.2) the unixODBC connection to a sqlite database isn't working anymore.
I tried the connection with isql client, and I can execute queries succesfully. Therefore I think it's a LO problem.
I also tried to create a fresh new LO database linked to the same DSN: "test connection" button says the connection was successful, but no data is showed opening tables or executing queries.

Steps to Reproduce:
1. Create a sqlite database, a table inside it and populate the table with some records.
2. Create a new unixODBC DSN pointing to the sqlite DB
3. Create a new base database linked to the ODBC DSN just created
4. Open the base database and open the table you populated in step 1.

Actual Results:
No record showed

Expected Results:
All records showed


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Version: 6.1.3.1
Build ID: 1:6.1.3~rc1-1
CPU threads: 4; OS: Linux 4.18; UI render: default; VCL: gtk3; 
Locale: it-IT (it_IT.UTF-8); Calc: group threaded
Comment 1 Alex Thurgood 2018-10-19 14:44:42 UTC
When testing this on Mac, I get a rather unhelpful "could not connect" error message and nothing else.

Tested with the libslqite3odbc driver obtained from Christian Werner's site on my own master 620alpha build.

If I use iodbctest from the terminal, I can connect to the sqlite3 database. Seems indeed that there is a problem somewhere.
Comment 2 Xisco Faulí 2018-10-23 16:07:19 UTC
(In reply to verlata from comment #0)
> Description:
> After upgrading to 6.1.3 (from 6.1.2) the unixODBC connection to a sqlite
> database isn't working anymore.

I guess we can mark it as regression then...
Comment 3 Julien Nabet 2018-10-24 19:16:59 UTC
I could reproduce this but have no idea what the cause may be, can’t help here.
Comment 4 verlata 2018-11-03 09:12:24 UTC
Just updated to Version: 6.1.3.1
Build ID: 1:6.1.3~rc1-2 (debian testing).
Bug still there.

Anybody can suggest what could be done to detect the cause of the problem?

TIA
Comment 5 verlata 2018-11-03 09:16:18 UTC
Since I can't anymore use queries and reports, I would mark this bug as "critical".

Thanks
Comment 6 Alex Thurgood 2018-11-08 08:14:16 UTC
I tested access to a sqlite3 db from Excel on MacOS Mojave using Christian Werner's ODBC driver. It still fails, but at least I get a more helpful error message :

file system sandbox blocked open() of '/usr/local/lib/libsqlite3odbc-0.9994.dylib'

So, at least for OSX, the reason for failure to even access the db seems to be that the dylib is sandboxed by the system.
Comment 7 Lionel Elie Mamane 2018-11-08 09:31:36 UTC
(In reply to Alex Thurgood from comment #6)
> I tested access to a sqlite3 db from Excel on MacOS Mojave using Christian
> Werner's ODBC driver. It still fails, but at least I get a more helpful
> error message :
> 
> file system sandbox blocked open() of
> '/usr/local/lib/libsqlite3odbc-0.9994.dylib'
> 
> So, at least for OSX, the reason for failure to even access the db seems to
> be that the dylib is sandboxed by the system.

This looks like Excel is sandboxed, and is not allowed to load the ODBC driver.
Comment 8 Heinz Repp 2018-11-22 16:27:42 UTC
Same happens with 6.1.3 on Ubuntu with unixODBC sqlite3 databases: they all can be opened, test connection is successful, but all all lack any record, all tables show empty. With isql I can access the same DSN and get all records. So LO suggests that all records have been lost, fortunately this is not the case. But most people will believe LO killed all their data. As this is pretty visible and existing databases cannot be used any more with the current version, a solution is dire needed.
Comment 9 verlata 2018-12-18 20:16:03 UTC
Just updated to 6.1.3.2: problem still there.
Comment 10 Alex Thurgood 2018-12-24 14:56:15 UTC
It appears that the problem might be more general than just sqlite, see also bug 122306
Comment 11 verlata 2019-01-01 10:09:09 UTC
updated to 6.1.4.2: no solution yet.
Comment 12 Robert Großkopf 2019-01-29 16:52:44 UTC
Is the right version marked here? If I read all the descriptions it seem the bug appears with LO 6.1.3, not LO 6.1.2. 

I have switched the version to LO 6.1.3.1 to mark it as the first version the bug appears.
Comment 13 verlata 2019-01-30 06:49:16 UTC
@Robert: you are right. I choose 6.1.2 because at the time I opened the bug report 6.1.3 was not availabable in the version dropdown list.

Still waiting for an hint to help me debug and fix the problem...
Comment 14 Alex Thurgood 2019-02-06 10:49:42 UTC
Confirming that this works on LO 6073

Build ID: 1:6.0.7-0ubuntu0.18.04.2
Threads CPU : 4; OS : Linux 4.15; UI Render : par défaut; VCL: gtk3; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 15 Alex Thurgood 2019-02-06 11:13:09 UTC
I can still access and write to a sqlite db over ODBC using my own master build

Version: 6.2.0.0.alpha0+
Build ID: 965ac9915280e3d570d7b32ff20799507f4e42eb
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: fr-FR (fr_FR.UTF-8); Calc: threaded

In all of my testing the ODBC datasource was a system declared DSN and not a user one (if that makes any difference).

These tests were carried out on Linux Mint Tara (19)