For some reason
DBI::dbConnect is connecting to the wrong database with no warning or error. I have several system data sources configured in
/usr/local/etc/odbc.ini and no user data sources in
~/.odbc.ini. The system data sources are all listed in the Connections tab in RStudio. There is one additional listing in the Connections tab. This database was the first database created on localhost and the database name is the same as the username, call it user1. When I try to connect to one of the configured DSNs, e.g.
con <- DBI::dbConnect(odbc::odbc(), dsn='dsn1', timeout = 10)
it connects with no error but to the wrong database. It connects to user1. The
dbGetInfo method falsely claims that it is connected to the DSN
> dbGetInfo(con)$sourcename  "dsn1"
but in the Connections tab in RStudio it shows that I am connected to user1 and
dbListTables shows the tables from user1 not dsn1. Also dbplyr shows that it is connected to user1
con %>% tbl('table1') %>% head(n=1) # Source: lazy query [?? x 10] # Database: postgres [user1@localhost:/]
It seems like a bug that it would connect to a different database and for
dbGetInfo to give an incorrect sourcename, but I'm not sure if the bug is in the DBI package, the odbc package, or the unixODBC software. I'm also not sure if the bug surfaces because of a misconfiguration on my part. Any advice for troubleshooting?
I am using Mac OS 10.14.5 and unixODBC is installed with Homebrew.