dbConnect with odbc does not connect to specified DSN

[jtbates]

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.

[Blair09M]

I'm not seeing this on my end, so I have a few questions to help diagnose what's going on:

• Which PostgreSQL driver are you using?
• Does each DSN have a different user? Or is the user for each DSN user1?
• Could you provide the results of devtools::session_info()?

[jtbates]

Which PostgreSQL driver are you using?

The latest version from Homebrew:

$ brew info psqlodbc
psqlodbc: stable 10.03.0000 (bottled), HEAD
Official PostgreSQL ODBC driver
https://odbc.postgresql.org
/usr/local/Cellar/psqlodbc/10.03.0000 (6 files, 915.2KB)

Does each DSN have a different user? Or is the user for each DSN user1 ?

There are two local DSNs that have the same user and the remote DSN that I am trying to connect to dsn1 has a different user.

Could you provide the results of devtools::session_info() ?

Yes, here are the results:

> devtools::session_info()
─ Session info ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.0 (2019-04-26)
 os       macOS Mojave 10.14.5        
 system   x86_64, darwin15.6.0        
 ui       RStudio                     
 language (EN)                        
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       America/Phoenix             
 date     2019-07-29                  

─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version date       lib source        
 assertthat    0.2.1   2019-03-21 [1] CRAN (R 3.6.0)
 backports     1.1.4   2019-04-10 [1] CRAN (R 3.6.0)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.6.0)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.6.0)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.6.0)
 callr         3.2.0   2019-03-15 [1] CRAN (R 3.6.0)
 cli           1.1.0   2019-03-19 [1] CRAN (R 3.6.0)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.6.0)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.6.0)
 dbplyr      * 1.4.2   2019-06-17 [1] CRAN (R 3.6.0)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.6.0)
 devtools      2.1.0   2019-07-06 [1] CRAN (R 3.6.0)
 digest        0.6.19  2019-05-20 [1] CRAN (R 3.6.0)
 dplyr       * 0.8.1   2019-05-14 [1] CRAN (R 3.6.0)
 DT            0.7     2019-06-11 [1] CRAN (R 3.6.0)
 fansi         0.4.0   2018-10-05 [1] CRAN (R 3.6.0)
 fs            1.3.1   2019-05-06 [1] CRAN (R 3.6.0)
 glue          1.3.1   2019-03-12 [1] CRAN (R 3.6.0)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.6.0)
 htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.6.0)
 htmlwidgets   1.3     2018-09-30 [1] CRAN (R 3.6.0)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.6.0)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.6.0)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.6.0)
 packrat       0.5.0   2018-11-14 [1] CRAN (R 3.6.0)
 pillar        1.4.1   2019-05-28 [1] CRAN (R 3.6.0)
 pkgbuild      1.0.3   2019-03-20 [1] CRAN (R 3.6.0)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.6.0)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.6.0)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.6.0)
 processx      3.3.1   2019-05-08 [1] CRAN (R 3.6.0)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.6.0)
 purrr         0.3.2   2019-03-15 [1] CRAN (R 3.6.0)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.6.0)
 Rcpp          1.0.1   2019-03-17 [1] CRAN (R 3.6.0)
 remotes       2.1.0   2019-06-24 [1] CRAN (R 3.6.0)
 rlang         0.3.4   2019-04-07 [1] CRAN (R 3.6.0)
 RODBC         1.3-15  2017-05-05 [1] CRAN (R 3.6.0)
 RODBCDBI      0.1.1   2016-03-14 [1] CRAN (R 3.6.0)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.6.0)
 rstudioapi    0.10    2019-03-19 [1] CRAN (R 3.6.0)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.6.0)
 testthat      2.1.1   2019-04-23 [1] CRAN (R 3.6.0)
 tibble        2.1.3   2019-06-06 [1] CRAN (R 3.6.0)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.6.0)
 usethis       1.5.1   2019-07-04 [1] CRAN (R 3.6.0)
 utf8          1.1.4   2018-05-24 [1] CRAN (R 3.6.0)
 vctrs         0.1.0   2018-11-29 [1] CRAN (R 3.6.0)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.6.0)
 zeallot       0.1.0   2018-01-28 [1] CRAN (R 3.6.0)

[1] /Library/Frameworks/R.framework/Versions/3.6/Resources/library

[jtbates]

I've uninstalled and reinstalled unixodbc and psqlodbc in Homebrew and that appears to have fixed the problem.

[jtbates]

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.