How to select MySQL version when installing RMySQL in linux

Question

I am running an R application in a Docker container and would like to connect to a MySQL 8 database using the RMySQL library. I can connect to the database from the container using the mysql terminal command without any problems, but when I call dbConnect() in my R script I get an unknown SSL connection error (see below).

As I am using the same SSL certificates with the terminal command as well as in my R script, it must have something to do with the MySQL version used by RMySQL. I need to have MySQL 5.7 installed in the container, too, as some other R packages cannot be built otherwise. However, according to the thread below you can specify the directory of the MySQL version to be used when installing RMySQL:

adding RMySQL package to R fails (on Windows)?

So I set $MYSQL_HOME in /etc/R/Renviron.site and called install.packages('RMySQL',type='source').

Apparently you also need to copy some .lib and .dll files to make it work in Windows, but where do I find the equivalent files in Linux?

Database handler script:

#' @import DBI
#' @import RMySQL

connectToDatabase <- function(myproject.db_config) {
  dbConfig <- read.properties(myproject.db_config)
  print(dbConfig)

  dbHandle <- dbConnect(
    MySQL(),
    dbname = dbConfig$databaseName,
    host = dbConfig$host,
    port = as.integer(dbConfig$port),
    user = dbConfig$user,
    password = dbConfig$password
  )
  return(dbHandle)
}

Config file passed to the read.properties() command:

host=domain.subdomain.de
port=3306
user=john.doe
password=mypassword
databaseName=my_db
ssl-ca=/staging/mysql-ssl/ca.pem
ssl-cert=/staging/mysql-ssl/client-cert.pem
ssl-key=/staging/mysql-ssl/client-key.pem

Error message in R:

Error in connection_create(host, username, password, dbname, as.integer(port), : Failed to connect: SSL connection error: unknown error number

score 0 · Accepted Answer · answered Nov 26 '19 at 16:52

I found the solution by myself. The SSL certificates were not applied in the dbConnect() call. They need to be defined in a mysql.cnf file which is passed to dbConnect() in the default.file parameter:

dbHandle <- dbConnect(
    MySQL(),
    dbname = dbConfig$databaseName,
    host = dbConfig$host,
    port = as.integer(dbConfig$port),
    user = dbConfig$user,
    password = dbConfig$password,
    default.file = dbConfig$configFile
)

Config file passed to the read.properties() command:

host=domain.subdomain.de
port=3306
user=john.doe
password=mypassword
databaseName=my_db
configFile=/staging/config/mysql.cnf

mysql.cnf:

[mysqld]
ssl-ca=/staging/mysql-ssl/ca.pem
ssl-cert=/staging/mysql-ssl/client-cert.pem
ssl-key=/staging/mysql-ssl/client-key.pem

How to select MySQL version when installing RMySQL in linux

1 Answers1