2

From R running on a Ubuntu machine, I want to connect to a MariaDB living on a Synology NAS and read the table. As the documentation suggests, I do

library(RMariaDB)

con <- dbConnect(
  drv=RMariaDB::MariaDB(), 
  username='<user>',
  password='<password>', 
  host='192.168.1.2', 
  port=3307,
  dbname='foo'
)

but this just fails with the error:

Error: Failed to connect: Lost connection to MySQL server at 
 'waiting for initial communication packet', system error: 110

The port is correct since otherwise I get

Error: Failed to connect: Can't connect to MySQL server on '
  192.168.1.2:<wrongport>' (111)

The driver also appears to be available:

RMariaDB::MariaDB()
# <MariaDBDriver>

Note, that I already installed MariaDB Connector/ODBC and UnixODBC. The database itself is accessible since I am able to connect using RemoDB on Android with the same credentials. Maybe I just missed something basic.

Finally, I would simply like to do this:

dat <- dbReadTable(con)

Specs:

Ubuntu:      20.04.3 LTS 
Linux:       5.13.0-27-generic x86_64 
mysql:       8.0.27-0ubuntu0.20.04.1 

DSM:         7.0.1-42218 
phpMyAdmin:  4.9.7-1032 
MariaDB 10:  10.3.29-1038

R:           4.1.2
RStudio:     2021.9.0.351
RMariaDB:    1.2.1
odbc:        1.3.3
DBI:         1.1.2

Update

I managed to connect to the database without using RMariaDB::MariaDB() but using RMySQL::MySQL() instead1. However, there are two remaining issues:

  1. This answer states, "RMariaDB will be a replacement for RMySQL, so in near future you will not get bug or security fixes for RMySQL anymore." Is this really true? What is now the best way to connect to my MariaDB?

  2. Establishing the connection takes a very long time (see timing below). What could be the issue? I have no clue what could be wrong since there are many steps involved.

system.time(
  mydb <- dbConnect(RMySQL::MySQL(), user=db_user, password=db_password,
                   dbname=db_name, host=db_host, port=db_port)
  )
#  user  system elapsed 
# 0.070   0.116 206.902

s <- paste0("select * from ", db_table)
rs <- dbSendQuery(mydb, s)
df <- fetch(rs, n=-1)
on.exit(dbDisconnect(mydb))
jay.sf
  • 60,139
  • 8
  • 53
  • 110
  • you could perhaps try to connect with standard obdc : `drv=odbc::odbc(),Driver = "MariaDB ODBC 3.0 Driver", Server=...)`. This worked for me on `MariaDB` – Waldi Feb 01 '22 at 21:30
  • @Waldi Thanks, gives me `Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'MariaDB ODBC 3.0 Driver' : file not found` though. Conversely in terminal I get `odbc-mariadb is already the newest version (3.1.4-1)`. What strikes me is that `odbc::odbcListDrivers()` gives an empty list. – jay.sf Feb 02 '22 at 06:31
  • Did you install https://mariadb.com/kb/en/about-mariadb-connector-odbc/ on the linux computer? This is needed to avoid above error message. – Waldi Feb 02 '22 at 09:50
  • @Waldi Yes, more precisely I did [this](https://mariadb.com/kb/en/building-mariadb-connectorodbc-from-source/). I am not sure if this is an RMariaDB, Ubuntu, Synology, MySQL, a firewall, or whatever problem. – jay.sf Feb 08 '22 at 04:43

0 Answers0