Setting timezone parameter in dbConnect throws an error (R 4.2.0 only, Win)

Alternikaner commented 2 years ago

dbConnect throws an error when a timezone is specified.

For a properly specified connection string (cs),

library(DBI)
library(odbc)

dbConnect(odbc::odbc(), .connection_string = cs, timezone = "Africa/Johannesburg")

fails with the error: Error in odbc_connect(connection_string, timezone = timezone, timezone_out = timezone_out, : Error loading time zone (Africa/Johannesburg)

...whereas the identical call without timezone works as expected.

Attempting connections with all possible timezones, e.g.

attempts <- lapply(OlsonNames(), \(x) try(DBI::dbConnect(odbc::odbc(), .connection_string = cs, timezone = x)))

leads to a string of error messages (all timezones are equally broken), Including a few - only appearing if one runs through a sizeable batch, for some reason - of the form: /usr/share/zoneinfo/Pacific/Auckland: No such file or directory

...which seems to point to a Unix-alike directory structure being assumed somewhere.

Additional info:

behaviour appeared on upgrade to 4.2.0 (was stable up to and including 4.1.3)
the issue seems to disappear in a reprex or when executed via callr
TZDIR points to the actual, correct location (noted in session info)
Very likely shares a root cause with https://github.com/tidyverse/lubridate/issues/1041; possibly related to https://github.com/tidyverse/lubridate/issues/928

Database

Microsoft SQL Server Version: 13.00.5264

Reproducible Example

# conversion issue only
x <- "2022-06-01 08:45:23"

as.POSIXct(x) # works; local time (SAST)
as.POSIXct(x, tz = "Pacific/Auckland") # works; sets to NZST
lubridate::ymd_hms(x) # works; UTC assumed
lubridate::ymd_hms(x, tz = "Pacific/Auckland") # Error: CCTZ: Unrecognized output timezone: "Pacific/Auckland"

-->

Session Info

```r library(DBI) library(odbc) Sys.timezone() #> [1] "Africa/Johannesburg" Sys.getenv("TZDIR") #> [1] "C:/PROGRA~1/R/R-42~1.0/share/zoneinfo" devtools::session_info() #> ─ Session info ─────────────────────────────────────────────────────────────── #> setting value #> version R version 4.2.0 (2022-04-22 ucrt) #> os Windows 10 x64 (build 19044) #> system x86_64, mingw32 #> ui RTerm #> language (EN) #> collate English_South Africa.utf8 #> ctype English_South Africa.utf8 #> tz Africa/Johannesburg #> date 2022-06-01 #> pandoc 2.17.1.1 @ C:/Users/ludwigb/AppData/Local/Programs/RStudio/bin/quarto/bin/ (via rmarkdown) #> #> ─ Packages ─────────────────────────────────────────────────────────────────── #> package * version date (UTC) lib source #> bit 4.0.4 2020-08-04 [1] CRAN (R 4.1.3) #> bit64 4.0.5 2020-08-30 [1] CRAN (R 4.1.3) #> blob 1.2.3 2022-04-10 [1] CRAN (R 4.2.0) #> brio 1.1.3 2021-11-30 [1] CRAN (R 4.1.3) #> cachem 1.0.6 2021-08-19 [1] CRAN (R 4.1.3) #> callr 3.7.0 2021-04-20 [1] CRAN (R 4.1.3) #> cli 3.3.0 2022-04-25 [1] CRAN (R 4.2.0) #> crayon 1.5.1 2022-03-26 [1] CRAN (R 4.1.3) #> DBI * 1.1.2 2021-12-20 [1] CRAN (R 4.1.3) #> desc 1.4.1 2022-03-06 [1] CRAN (R 4.1.3) #> devtools 2.4.3 2021-11-30 [1] CRAN (R 4.1.3) #> digest 0.6.29 2021-12-01 [1] CRAN (R 4.1.3) #> ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.1.3) #> evaluate 0.15 2022-02-18 [1] CRAN (R 4.1.3) #> fansi 1.0.3 2022-03-24 [1] CRAN (R 4.1.3) #> fastmap 1.1.0 2021-01-25 [1] CRAN (R 4.1.3) #> fs 1.5.2 2021-12-08 [1] CRAN (R 4.1.3) #> glue 1.6.2 2022-02-24 [1] CRAN (R 4.1.3) #> highr 0.9 2021-04-16 [1] CRAN (R 4.1.3) #> hms 1.1.1 2021-09-26 [1] CRAN (R 4.1.3) #> htmltools 0.5.2 2021-08-25 [1] CRAN (R 4.1.3) #> knitr 1.39 2022-04-26 [1] CRAN (R 4.2.0) #> lifecycle 1.0.1 2021-09-24 [1] CRAN (R 4.1.3) #> magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.2.0) #> memoise 2.0.1 2021-11-26 [1] CRAN (R 4.1.3) #> odbc * 1.3.3 2021-11-30 [1] CRAN (R 4.1.3) #> pillar 1.7.0 2022-02-01 [1] CRAN (R 4.1.3) #> pkgbuild 1.3.1 2021-12-20 [1] CRAN (R 4.1.3) #> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.1.3) #> pkgload 1.2.4 2021-11-30 [1] CRAN (R 4.1.3) #> prettyunits 1.1.1 2020-01-24 [1] CRAN (R 4.1.3) #> processx 3.5.3 2022-03-25 [1] CRAN (R 4.1.3) #> ps 1.7.0 2022-04-23 [1] CRAN (R 4.2.0) #> purrr 0.3.4 2020-04-17 [1] CRAN (R 4.1.3) #> R.cache 0.15.0 2021-04-30 [1] CRAN (R 4.2.0) #> R.methodsS3 1.8.1 2020-08-26 [1] CRAN (R 4.2.0) #> R.oo 1.24.0 2020-08-26 [1] CRAN (R 4.2.0) #> R.utils 2.11.0 2021-09-26 [1] CRAN (R 4.2.0) #> R6 2.5.1 2021-08-19 [1] CRAN (R 4.1.3) #> Rcpp 1.0.8.3 2022-03-17 [1] CRAN (R 4.1.3) #> remotes 2.4.2 2021-11-30 [1] CRAN (R 4.1.3) #> reprex 2.0.1 2021-08-05 [1] CRAN (R 4.1.3) #> rlang 1.0.2 2022-03-04 [1] CRAN (R 4.1.3) #> rmarkdown 2.14 2022-04-25 [1] CRAN (R 4.2.0) #> rprojroot 2.0.3 2022-04-02 [1] CRAN (R 4.1.3) #> rstudioapi 0.13 2020-11-12 [1] CRAN (R 4.1.3) #> sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.1.3) #> stringi 1.7.6 2021-11-29 [1] CRAN (R 4.1.2) #> stringr 1.4.0 2019-02-10 [1] CRAN (R 4.1.3) #> styler 1.7.0 2022-03-13 [1] CRAN (R 4.2.0) #> testthat 3.1.4 2022-04-26 [1] CRAN (R 4.2.0) #> tibble 3.1.7 2022-05-03 [1] CRAN (R 4.2.0) #> usethis 2.1.6 2022-05-25 [1] CRAN (R 4.2.0) #> utf8 1.2.2 2021-07-24 [1] CRAN (R 4.1.3) #> vctrs 0.4.1 2022-04-13 [1] CRAN (R 4.2.0) #> withr 2.5.0 2022-03-03 [1] CRAN (R 4.1.3) #> xfun 0.31 2022-05-10 [1] CRAN (R 4.2.0) #> yaml 2.3.5 2022-02-21 [1] CRAN (R 4.1.2) #> #> [1] C:/RPackages #> [2] C:/Program Files/R/R-4.2.0/library #> #> ─ ```

DavisVaughan commented 2 years ago

Strange. It does seem to suggest that this fallback to "/usr/share/zoneinfo" is running because the TZDIR env var didn't have anything in it, even though your session info reprex above does show that there is a file path for Sys.getenv("TZDIR")

https://github.com/r-dbi/odbc/blob/09ef0a23a191b6f1496e9243b91157f83a32d9ae/src/cctz/src/time_zone_info.cc#L662-L667

Alternikaner commented 2 years ago

@DavisVaughan Looking back at the sysinfo dumps of the other reporters on the lubridate side, we all share "mingw32" under system (I suspect this correlates to having installed rtools).

My theory is that this causes a FALSE in the 662 condition via ((!defined(MINGW32), skipping execution to the else. This would imply that our bug lies in the assignment on line 668.

If I'm right, callr/reprex environments don't fail because they don't load the mingw toolchain, leading to execution of 663-666 rather than the else block.

DavisVaughan commented 2 years ago

I just noticed that you have odbc built with R 4.1.3, but you are on R 4.2.0

#>  odbc        * 1.3.3   2021-11-30 [1] CRAN (R 4.1.3)

This looks to also be the case for the lubridate examples.

Can you try forcibly installing CRAN odbc with install.packages("odbc")? If you do that and restart R, then you should see (R 4.2.0) when you look at odbc in the session info

DavisVaughan commented 2 years ago

Ok im fairly confident that my comment above was dead on. I was able to reproduce this locally on my own windows machine with lubridate. See https://github.com/tidyverse/lubridate/issues/1041#issuecomment-1149930137 for details of the problem.

Just calling install.packages("odbc") and restarting R should be enough to get you an updated version of odbc built with R 4.2.0

Alternikaner commented 2 years ago

Thanks for your help Davis.

r-dbi / odbc