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
[1] "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.
Well, looks like it was only a temporary fix... it's stopped working again. I'm having trouble understanding how that could be as I've made no more changes to the drivers or DSN configuration. Reinstalled psqlodbc and it's working again for now.