libreoffice-base: ODBC is broken

hlandau commented 5 years ago

Issue description

LibreOffice Base can't use ODBC on NixOS 18.09.

Steps to reproduce

Open LibreOffice Base. Connect to an existing database. Select ODBC. Next. Press Browse. "Could not load the program library libodbc.so or it is corrupted. The ODBC data source selection is not available."

unixODBC is in systemPackages. Configuration includes:

  environment.unixODBCDrivers = with pkgs.unixODBCDrivers; [ sqlite psql ];

Technical details

system: "x86_64-linux"
host os: Linux 4.14.79, NixOS, 18.09.1922.97e0d53d669 (Jellyfish)
multi-user?: yes
sandbox: yes
version: nix-env (Nix) 2.1.3
channels(root): "nixos-18.09.1922.97e0d53d669"
nixpkgs: /nix/var/nix/profiles/per-user/root/channels/nixos

7c6f434c commented 5 years ago

unixODBC is in systemPackages. Configuration includes:

Hm. Is it large/has something large in the closure that is not a dependency of LibreOffice?

/run/current-system/sw/lib is not used for library loading by default; you might try adding it (or, better, just the unixODBC lib directory) to LD_LIBRARY_PATH. Note that you probably want to add, keeping the old contents, as LD_LIBRARY_PATH on NixOS is also used for OpenGL driver libraries.

Maybe it is a good idea to just link LibreOffice against the library in the medium term.

hlandau commented 5 years ago

Things get further with

LD_LIBRARY_PATH=/run/current-system/sw/lib:$LD_LIBRARY_PATH libreoffice --base

The available databases can be listed. Pressing 'Test Connection', however yields:

SQL Status: 01000

[unixODBC][Driver Manager]Can't open lib '/nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so' : file not found

which is strange because that file exists:

$ ls -l /nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/
total 340
-r-xr-xr-x 1 root root  25176 Jan  1  1970 libsqlite3_mod_blobtoxy-0.9993.so
-r-xr-xr-x 1 root root   1080 Jan  1  1970 libsqlite3_mod_blobtoxy.la
lrwxrwxrwx 1 root root     33 Jan  1  1970 libsqlite3_mod_blobtoxy.so -> libsqlite3_mod_blobtoxy-0.9993.so
-r-xr-xr-x 1 root root  25864 Jan  1  1970 libsqlite3_mod_csvtable-0.9993.so
-r-xr-xr-x 1 root root   1080 Jan  1  1970 libsqlite3_mod_csvtable.la
lrwxrwxrwx 1 root root     33 Jan  1  1970 libsqlite3_mod_csvtable.so -> libsqlite3_mod_csvtable-0.9993.so
-r-xr-xr-x 1 root root  34224 Jan  1  1970 libsqlite3_mod_impexp-0.9993.so
-r-xr-xr-x 1 root root   1068 Jan  1  1970 libsqlite3_mod_impexp.la
lrwxrwxrwx 1 root root     31 Jan  1  1970 libsqlite3_mod_impexp.so -> libsqlite3_mod_impexp-0.9993.so
-r-xr-xr-x 1 root root  32016 Jan  1  1970 libsqlite3_mod_zipfile-0.9993.so
-r-xr-xr-x 1 root root   1078 Jan  1  1970 libsqlite3_mod_zipfile.la
lrwxrwxrwx 1 root root     32 Jan  1  1970 libsqlite3_mod_zipfile.so -> libsqlite3_mod_zipfile-0.9993.so
-r-xr-xr-x 1 root root 198816 Jan  1  1970 libsqlite3odbc-0.9993.so
-r-xr-xr-x 1 root root   1128 Jan  1  1970 libsqlite3odbc.la
lrwxrwxrwx 1 root root     24 Jan  1  1970 libsqlite3odbc.so -> libsqlite3odbc-0.9993.so

It's not a missing dependency either:

$ ldd /nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so 
    linux-vdso.so.1 (0x00007fff1b7c3000)
    libodbcinst.so.2 => /nix/store/6hchbqdhf669d5jxkrm1ib67w28px6na-unixODBC-2.3.7/lib/libodbcinst.so.2 (0x00007f2a12d28000)
    libdl.so.2 => /nix/store/7gx4kiv5m0i7d7qkixq2cwzbr10lvxwc-glibc-2.27/lib/libdl.so.2 (0x00007f2a12b20000)
    libpthread.so.0 => /nix/store/7gx4kiv5m0i7d7qkixq2cwzbr10lvxwc-glibc-2.27/lib/libpthread.so.0 (0x00007f2a12900000)
    libc.so.6 => /nix/store/7gx4kiv5m0i7d7qkixq2cwzbr10lvxwc-glibc-2.27/lib/libc.so.6 (0x00007f2a12548000)
    /nix/store/7gx4kiv5m0i7d7qkixq2cwzbr10lvxwc-glibc-2.27/lib64/ld-linux-x86-64.so.2 (0x00007f2a13178000)

7c6f434c commented 5 years ago

[unixODBC][Driver Manager]Can't open lib '/nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so' : file not found

Maybe in the initialisation code it tries to open SQLite library? Two options (not even mutually exclusive): add this to LD_LIBRARY_PATH, too; strace the process (probably with -f and probably with -s 999 and probably with -o /tmp/lo.log I would guess)

hlandau commented 5 years ago

Adding to LD_LIBRARY_PATH does nothing.

9565  openat(AT_FDCWD, "/nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so", O_RDONLY|O_CLOEXEC) = 25

It opens the file fine.

$ cat x.log |grep -E 'open.*\('
...
9565  openat(AT_FDCWD, "/nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so", O_RDONLY|O_CLOEXEC) = 25             
9565  openat(AT_FDCWD, "/run/current-system/sw/lib/libodbcinst.so.2", O_RDONLY|O_CLOEXEC) = 25  
$

After successfully opening libsqlite3odbc.so, the only other attempted file open is libodbcinst.so.2.

7c6f434c commented 5 years ago

$ cat x.log |grep -E 'open.*(' ... 9565 openat(AT_FDCWD, "/nix/store/ln3gc3r3gj16yc0kik84mxazx5bdlb0r-sqlite-connector-odbc-0.9993/lib/libsqlite3odbc.so", O_RDONLY|O_CLOEXEC) = 25
9565 openat(AT_FDCWD, "/run/current-system/sw/lib/libodbcinst.so.2", O_RDONLY|O_CLOEXEC) = 25
$
After successfully opening `libsqlite3odbc.so`, the only other attempted file open is `libodbcinst.so.2`.

Maybe look for ENOENT (of any origin) after that open?

hlandau commented 5 years ago

There's no ENOENT after that open; the above lists the only attempted file open after opening libsqlite3odbc.so.

7c6f434c commented 5 years ago

Interesting that there are no ENOENT at all (from execve or something); a search shows that ODBC error messages are not always straightforward, though…

stale[bot] commented 4 years ago

Thank you for your contributions.

This has been automatically marked as stale because it has had no activity for 180 days.

If this is still important to you, we ask that you leave a comment below. Your comment can be as simple as "still important to me". This lets people see that at least one person still cares about this. Someone will have to do this at most twice a year if there is no other activity.

Here are suggestions that might help resolve this more quickly:

Search for maintainers and people that previously touched the related code and @ mention them in a comment.
Ask on the NixOS Discourse.
Ask on the #nixos channel on irc.freenode.net.

juanjoseamaker commented 1 year ago

still important to me

raman2106 commented 1 year ago

need a solution for this problem

bgsmase commented 10 months ago

Just experienced the same problem and Internet search found this issue.

bgsmase commented 10 months ago

On further testing I have found that adding the path to the unixODBC lib folder to LD_LIBRARY_PATH (as opposed to the sqlite-connector-odbc lib folder which I think was what was tried above) seems to fix problem. I am using nix-shell on Ubuntu 22.04 with nixpkgs rather than NixOS so maybe not exactly the same as above but using nix-shell with the following path and setting up my odbc config files now works to connect from libreoffice to my sqlite databases

let
  pkgs = import <nixpkgs> {};
in
  pkgs.mkShell {
    buildInputs = [
      pkgs.libreoffice-fresh
      pkgs.sqlite
      pkgs.unixODBC
      pkgs.unixODBCDrivers.sqlite
      pkgs.temurin-jre-bin-17
    ];
  shellHook =
    ''
      export ODBCSYSINI="/home/bgsmase"
      export LD_LIBRARY_PATH="/nix/store/lrz7907axnf1b17bbys9aw9089jyvppa-unixODBC-2.3.11/lib:$LD_LIBRARY_PATH"
    '';
  }

(I'm relatively new to Nix so this may not be the best way of doing things.)

NixOS / nixpkgs