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!
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:
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.
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.
This is a wild one!! One possible way to resolve these env vars as well is to use the not-very-well-documented env-vars file. I believe this works much like systemd config, but was added in the few years or so for these types of use cases. I'm surprised that environment variables are not getting passed to the connection though - I think this definitely deserves an issue in the odbc package!