Lack of connectivity to MariaDB on a NAS

[jay-sf]

Hi

Thanks for maintaining both DBI and RMariaDB!

I have a problem connecting to a MariaDB on a Synology NAS. It doesn't work at all using RMariaDB::MariaDB and just unsatisfactory using RMySQL::MySQL().

I first asked a question on Stack Overflow which at least brought me a small step further, but nothing more.

So, according to the documentation, I have tried this:

library(RMariaDB)

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

But I get 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>

Then tried with the RMySQL::MySQL() driver, which actually works but it almost takes forever (207 secs):

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))

Curiously it's always around 207 secs. Before you ask, the database actually just has tiny dimensions 86x4.

I'm not sure if it is a Synology, a Network, a Linux, or a problem related to your packages, but maybe you have an idea what's going on and can help?

Here my 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

Cheers!

[krlmlr]

Thanks. This looks like a fairly specialized application, could this be a firewall of sorts (on the Synology or elsewhere) that interferes here?

Can you please try connecting using the mysql executable? Are results different between MySQL and MariaDB client? (AFAIR both provide such an executable.)

[jay-sf]

Thanks, I opened 3307 port in NAS firewall which was interestingly closed, but without improvement of connection speed. Then I tried your suggestion and used mysql in the terminal which works but it also took the felt 207 seconds there. What I did next was to ssh into the NAS and use mysql in this way. This worked as expected, the connection was there immediately. Strangely enough, when using the RemoDB v4.2.14 Android app, I can access the database perfectly. Since the phone connects via WiFi, I tried the R script in OP on my laptop but slow speed persists, I takes exactly the bewitched 207 seconds.

[krlmlr]

Thanks. Did you use the MySQL or MariaDB version of mysql in your local tests?

It seems that RemoDB is doing something differently, I haven't found sources for RemoDB though.

Would an SSH tunnel work for you?

[jay-sf]

Thanks, using SELECT VERSION(); gives me 10.3.32-MariaDB for both options, Ubuntu and ssh.

However after starting mysql, on Ubuntu I get

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 899
Server version: 5.5.5-10.3.32-MariaDB Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

whereas via ssh:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 900
Server version: 10.3.32-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

which is slightly different.

Do you mean an ssh tunnel in the R script? Would you mind to elaborate a little on that?

[krlmlr]

Can you try installing the MariaDB monitor on Ubuntu and try it there?

For an SSH tunnel, you would connect to the device via ssh and use the -L switch to establish a tunnel, essentially a port on your local machine that forwards to a (perhaps different) port on the remote machine: https://www.ssh.com/academy/ssh/tunneling/example .