dbConnect works in RStudio but fails in R on macOS

Posit Professional & Hosted Products
, ,
1

I am attempting to connect to Oracle Database using the odbc package on macOS High Sierra. I have installed Oracle Instant Client, including the ODBC package, configured a DSN, and can connect within RStudio, i.e., the following works:

conn <- DBI::dbConnect(
  drv = odbc::odbc(),
  dsn = "mydsn"
)

When I attempt the same from R, I get an error:

Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libsqora.dylib.12.1' : file not found

Now, the file exists at /usr/local/Cellar/instantclient-basic/12.2.0.1.0-2/lib and is symlinked into /usr/local/lib. I think this has something to do with one or more of the following, but I haven't had much luck tracking it down.

  • System Integrity Protection
  • the DYLD_FALLBACK_LIBRARY_PATH environment variable
  • differences in how RStudio launches R versus launching R at the command line
  • something to do with install_name_path

I've read through this article on the startup differences, but nothing I tweak in R seems to work. In particular, I (unsuccessfully) tried manually setting the path, i.e.,

Sys.setenv(DYLD_FALLBACK_LIBRARY_PATH = "/usr/local/lib")

I'd appreciate any suggestions. I haven't included a reprex because this seems like such an in-the-Mac-weeds problem that I am not even sure what information would be helpful. I should note that this problem also seems to affect rendering R Markdown inside RStudio, e.g., an RMD that contains the dbConnect() statement will render, and the rendered document will show the 'file not found' error. So, I am guessing that the issues (rendering RMD, launching R from the CLI) are related.

I posted a version of this question on the Oracle Community forums as well, but so far haven't had any replies.

Thank you in advance.

2

Are you comparing "R in the terminal" vs "R in RStudio"? I'm not sure I completely understand the "R vs. RStudio dichotomy." If so, it might be worth inspecting the environment with Sys.getenv() and looking for differences in the two. The RStudio IDE has some differences in startup to R in the terminal. You could obviously do the same in the RMarkdown render, as well. Oracle Instant Client has a knack for being finnicky and environment-variable-sensitive. I think LD_LIBRARY_PATH, ORACLE_HOME and PATH are some of the common problems, if I remember correctly.

EDIT: It sounds like you may have gone down this road already - apologies if there is redundancy here :slight_smile:

3

This is now resolved. The main clue from from the "Understanding R's Startup" article linked above, where @slopp mentions that RStudio "doesn’t 'start' R, it uses R as a library, either as a DLL on Windows or as a shared object on Mac and Linux." This is intriguing, because it suggests that there may be different binaries involved for each environment. This seems to matter for run-path dependent libraries.

It turns that that the Oracle ODBC driver does use @rpath, here is the (partial) output from otool -l libsqora.dylib.12.1:

Load command 3
          cmd LC_ID_DYLIB
      cmdsize 56
         name @rpath/libsqora.dylib.12.1 (offset 24)
   time stamp 1 Wed Dec 31 19:00:01 1969
      current version 0.0.0
compatibility version 0.0.0
...
Load command 12
          cmd LC_LOAD_DYLIB
      cmdsize 56
         name @rpath/libclntsh.dylib.12.1 (offset 24)
   time stamp 2 Wed Dec 31 19:00:02 1969
      current version 0.0.0
compatibility version 0.0.0
Load command 13
          cmd LC_LOAD_DYLIB
      cmdsize 56
         name @rpath/libodbcinst.2.dylib (offset 24)
   time stamp 2 Wed Dec 31 19:00:02 1969
      current version 3.0.0
compatibility version 3.0.0
Load command 14
          cmd LC_RPATH
      cmdsize 48
         path @executable_path/../../oracle/lib (offset 12)

My understanding is that the value of @rpath at the time libsqora.dylib.12.1 is loaded will determine whether these libraries can be found. I made the following changes:

install_name_tool -id "/usr/local/opt/instantclient-basic/lib/libsqora.dylib.12.1" libsqora.dylib.12.1
install_name_tool -change "@rpath/libodbcinst.2.dylib" "/usr/local/lib/libodbcinst.2.dylib" libsqora.dylib.12.1
install_name_tool -change "@rpath/libclntsh.dylib.12.1" "/usr/local/lib/libclntsh.dylib.12.1" libsqora.dylib.12.1
install_name_tool -rpath "@executable_path/../../oracle/lib" "/usr/local/lib" libsqora.dylib.12.1

where /usr/local/opt/instantclient-basic/lib is the location of libsqora.dylib.12.1.

So, I turned all the run-paths into absolute paths, and that seems to have fixed it. Everything is now working in (command-line) R, as well as RStudio, including knitting R Markdown.

3 Likes