RStudio Console vs RStudio Terminal Different Behaviour Causing ODBC Error

[sturu]

Continuing the discussion from odbc not working in Rstudio-server ("Can't open lib"), but works in cli:

Hi all,

I'm having a bit too much 'fun' with this problem and it's about time I asked for suggestions.

I am seeing some weird behaviour when trying to run the same code on the Rstudio server open source console & terminal on a Centos7 server.

Weird behaviour

The same code (a fully-qualified DBI::dbConnect(odbc::odbc()...) call) works perfectly fine when called from an R prompt either in a standalone shell or in the Rstudio terminal, but fails to connect when called from the Rstudio console in the same (fresh) session:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][ODBC Firebird Driver]Unable to connect to data source: library '/apps/firebird/2.5.9/libfbembed.so' failed to load

The same is true for system calls (e.g. system('module list')) from within the R console vs running the same code in the Rstudio terminal, either in bash or an R prompt.

What I've tried so far

I've ruled out environment variables by swapping each of the environments. The odbc connection string works fine too. I suspect it's a permissions/shell setup/.bash_profile problem.

Suspicions

To solve this problem I think I need to understand how each of the Rstudio console & Rstudio terminal launches, & with what permissions - whoami gives the same result (my username), but each has clearly had a different launch pathway - there are some differences in sourced files, & echo ~ gives quite different results.

Any suggestions on where to go next? Installing the database drivers using a different method (which worked around the problem in the above thread) isn't an option for me. I've got a bit out of my depth at this point!

Thanks

Stuart

[sturu]

So after some more troubleshooting, trying to edit the environment via systemd ExecStart scripts, PAM environment scripts etc I'm not getting anywhere.

However, after fixing both my /etc/profile & /etc/bashrc files to point to the correct version of R:

Calling this from the Rstudio console doesn't work:

# ... = Abbreviated
DBI::dbConnect( odbc::odbc(), ... )

but calling this from the Rstudio console works fine:

# ... = Abbreviated
system("Rscript -e 'DBI::dbConnect( odbc::odbc(), ... )'")

I can probably use this as a workaround for the entire process for the time being, but I'm suspicious I'm hitting a limit with Open Source in my environment (environment modules required) - I may need to buy Rstudio Workbench Pro to configure the R session before it opens as outlined in the manual here, but I'll have to read up on this more, & it feels speculative as I still don't know what's causing this.

I'd still be very grateful to hear if anyone has any ideas how I can troubleshoot this behaviour further - something quite specific is going on if code runs fine when called as a second process, but not when called directly from the main process & environment variables have been ruled out.

[sturu]

So I've worked this out after running into the same issue on Rstudio Connect.

It looks like DBI::dbConnect or odbc::odbc are performing system calls which don't inherit the environment variables from the R session, or from /etc/profile, /etc/profile.d/.. or ~/.bashrc (unless I did something stupid). Hence even though my session had correct values for LD_LIBRARY_PATH (set in Renviron.site) the resulting connection attempt did not, so couldn't find certain required system libraries.

I solved it on Rstudio Connect using a program supervisor to supply the correct environment variables as described in this article.

To achieve the same thing on Rstudio Server I'd need to buy the Pro/Workspace version, but for now my workaround is to alter the systemd service definition to provide a wrapper script in ExecStart, which adds the necessary environment variables before starting the original rstudio-server ExecStart script.

Hopefully this helps someone else out there!