Need MYSQL_OPT_RECONNECT

[renkun-ken]

Typically, I can specify several mysql options before connecting using MariaDB C Connector introduced at https://mariadb.com/kb/en/library/mysql_optionsv/ like the following:

MYSQL *mysql = mysql_init(nullptr);
mysql_options(mysql, MYSQL_OPT_RECONNECT, &config.reconnect);
mysql_options(mysql, MYSQL_OPT_LOCAL_INFILE, 0);
mysql_options(mysql, MYSQL_INIT_COMMAND, "SET autocommit=0");

However, I don't find a way to specify MYSQL_OPT_RECONNECT in RMariaDB. I'm wondering if there any possibility to support this?

[jorainer]

Same for me. I'm running R in a docker and connecting to a MariaDb database on the host outside the docker and keep getting server has gone away errors. Would be nice if RMariaDB would reconnect automatically.

[krlmlr]

I'm sympathetic with this. What's a good way to verify that reconnection works?

[krlmlr]

This reprex temporarily starts and stops MariaDB servers with the help of the processx package. The current state isn't ideal:

library(RMariaDB)
# https://github.com/tidyverse/reprex/issues/400
con <- RMariaDB:::mariadb_default()
#> Error: Failed to connect: Can't connect to local server through socket '/tmp/mysql.sock' (61)

library(processx)
mariadb <- process$new("mariadbd")
Sys.sleep(1)

con <- RMariaDB:::mariadb_default()

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

mariadb$kill_tree()
#> mariadbd 
#>     4893

dbGetQuery(con, "SELECT 1")
#> Error: Lost connection to server during query [2013]

mariadb <- process$new("mariadbd")
Sys.sleep(1)

dbGetQuery(con, "SELECT 1")
#> Error: Server has gone away [2006]

mariadb$kill_tree()
#> mariadbd 
#>     4894

^{Created on 2021-10-29 by the reprex package (v2.0.1)}

[krlmlr]

Somewhat better with {pool}. Not perfect yet, but we might get away without having to change anything here in the end:

library(RMariaDB)
# https://github.com/tidyverse/reprex/issues/400
con <- dbConnect(MariaDB(), dbname = "test")
#> Error: Failed to connect: Can't connect to local server through socket '/tmp/mysql.sock' (61)

library(processx)
mariadb <- process$new("mariadbd")
Sys.sleep(1)

library(pool)

con <- dbPool(MariaDB(), dbname = "test")

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

mariadb$kill_tree()
#> mariadbd 
#>     5445

dbGetQuery(con, "SELECT 1")
#> Error: Server has gone away [2006]

mariadb <- process$new("mariadbd")
Sys.sleep(1)

poolCheckout(con)
#> <MariaDBConnection>
#>   Host:    localhost
#>   Server:  
#>   Client:

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

mariadb$kill_tree()
#> mariadbd 
#>     5447

^{Created on 2021-10-29 by the reprex package (v2.0.1)}

[krlmlr]

Looks like this simply opens a new connection.

[krlmlr]

With MYSQL_OPT_RECONNECT:

library(RMariaDB)
# https://github.com/tidyverse/reprex/issues/400
con <- RMariaDB:::mariadb_default()

library(processx)
mariadb <- process$new("mariadbd")
Sys.sleep(1)

con <- RMariaDB:::mariadb_default()

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

dbIsValid(con)
#> [1] TRUE

mariadb$kill_tree()
#> mariadbd 
#>     9630

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

dbIsValid(con)
#> [1] TRUE

mariadb <- process$new("mariadbd")
Sys.sleep(1)

dbGetQuery(con, "SELECT 1")
#>   1
#> 1 1

mariadb$kill_tree()
#> mariadbd 
#>     9631

^{Created on 2021-10-29 by the reprex package (v2.0.1)}

But what if the connection failure happens during a transaction? This should be opt-in at best.

[krlmlr]

Reconnection should be defined by the interface, with proper events and conditions when it's safe to reconnect (e.g. only outside of transactions).

[renkun-ken]

Any updates on this?

I think I just need a way to turn on this option when creating a connection, just like specifying some other options such as MYSQL_OPT_READ_TIMEOUT and MYSQL_OPT_WRITE_TIMEOUT. Its behavior should be defined in https://mariadb.com/kb/en/mysql_optionsv/ and I actually don't expect RMariaDB to do anything else.

In other word, I only need an interface to specify options I need.

[renkun-ken]

What if we just add reconnect to https://github.com/r-dbi/RMariaDB/blob/main/src/DbConnection.cpp#L30 so that user could simply opt-in MYSQL_OPT_RECONNECT?

[krlmlr]

We could do it for RMariaDB if you need it, clearly marking it as experimental and somewhat dangerous. Would you like to submit a PR?

[renkun-ken]

Yes, I'll submit a PR shortly.