Fix test failures on Windows and Mac

[krlmlr]

#95 (comment) and following.

• DBItest compatibility
• macOS failures
• Crashes on Win32

[jeroen]

Perhaps the issue is not Windows/Mac specific but rather a bug or incompatibility with recent versions of mariadb-connector-c. Currently Windows builds with mariadb-connector-c 2.3.7 and macos builds with mariadb-connector-c 3.0.8.

[krlmlr]

Ran a debug build on Travis.

The brewed mysql instance has this:

$ mysql -D test -e "SHOW VARIABLES LIKE 'local_infile'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

This causes an error with LOAD DATA LOCAL INFILE triggered by the "can read file from disk" test.

Skipping the test if detecting this condition in 7af3a2f.

[krlmlr]

Travis is green now.

[jeroen]

OK great. Any luck with debugging Windows? I'd like to know if there is a problem with my driver builds or if this is just a bug elsewhere.

[krlmlr]

Couldn't replicate on Windows. As a last resort I could rdp into the worker, before doing that I'll double-check rtools version and compiler switches.

[krlmlr]

Saw a failure with the dev version of DBI and DBItest. Will investigate.

[krlmlr]

Steps to replicate on 32-bit Windows:

devtools::load_all()
DBItest::test_some("append_roundtrip_timestamp")

[krlmlr]

Dates with values of 2³¹ or larger are a problem:

library(DBI)
devtools::load_all()

local <- as.POSIXct(2^31, origin = "1970-01-01")
attr(local, "tzone") <- NULL
tbl_in <- data.frame(id = seq_along(local))
tbl_in$local <- local

con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")

name <- "test"

dbRemoveTable(con, name, force = FALSE)
dbCreateTable(con, name, tbl_in)
dbAppendTable(con, name, tbl_in)

[jeroen]

I think this is a case of the Year_2038_problem. A timestamp in R should be a double:

> typeof(Sys.time())
[1] "double"

This crash suggests you are using an int or time_t which overflows at 2³¹ on 32 bit systems, i.e. it cannot support dates beyond 2038-01-18 21:14:08 CST.

The solution is to avoid time_t and use a double.

[krlmlr]

Simplest reprex:

library(DBI)
devtools::load_all()

init_logging("VERB")

con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")

local <- as.POSIXct(2^31, origin = "1970-01-01")
dbGetQuery(con, "SELECT ? AS a", params = list(local))

The reason is that gmtime() is called with a negative time_t value and returns a NULL pointer. Need to adopt an alternative implementation already used in RPostgres.

[krlmlr]

Simply avoiding the crash leads to test failures, unsurprisingly: https://ci.appveyor.com/project/r-dbi/rmariadb/builds/22218641#L797

[jeroen]

We're not alone: https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.1-Year-2038-Syscalls

[krlmlr]

We should pass POSIXlt to the C code instead of calling gmtime().

[krlmlr]

r-dbi/RSQLite#333 uses Boost 1.75 for date/time functionality, maybe it's worth investigating.

[krlmlr]

The tests on Windows i386 no longer fail, maybe the new Connector/C? 🤷‍♂️

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.