Add protocol = "tcp" as argument to dbConnect()

[matteodelucchi]

Situation

I host a mysql DB in a local docker container.

docker-compose.yml

version: '3.2'

services:
    db:
        image: mysql:8.0
        command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        volumes:
            - ./mysql-dockerDB/var/lib/mysql:/var/lib/mysql
            - ./dockermnt:/docker-entrypoint-initdb.d
        container_name: mysqlDB
        restart: always
        ports:
            - '6603:3306'
        environment:
            MYSQL_ROOT_PASSWORD: secret

I expose the port 6603 to the localhost which allows me to access mysql in the container by i.e.:
mysql --host=localhost --port=6603 --user=root --password=secret --protocol=tcp

Problem

When I try to connect my local R Studio (not in docker) to mysqlDB via the open port with

con <- dbConnect(RMariaDB::MariaDB(),
                  dbname = "dbname",
                  username = "root",
                  password = "secret",
                  port = 6603)

I get the error: Error: Failed to connect: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) . The same error happens when I specify host = "localhost" explicitly.
Which is unexpected, as I don't want to connect to the socket file rather using the TCP network protocol.
Unfortunately the argument protocol = "TCP" is not supported.

Workaround

I could solve the issue with specifying the protocol in the configuration file:

.my.cnf

[destination]
  port=6603
  proto=TCP

and then in R specifying the path to the configuration file and groups argument.

con <- dbConnect(RMariaDB::MariaDB(),
                  dbname = "dbname",
                  username = "root",
                  password = "secret",
                  groups="destination",
                  default.file = "<path-to-configuration-file>/.my.cnf")

Proposed Solution

In regard of the raising popularity of container stacks, it would be very valuable to have an argument in the method dbConnect() to directly specify the protocol type. Then, the workaround with the config file would be obsolete.
I imagine sth like this

con <- dbConnect(RMariaDB::MariaDB(),
                  dbname = "dbname",
                  username = "root",
                  password = "secret",
                  port = 6603,
                  protocol = "tcp")

[krlmlr]

Thanks. Does it work if you set host = "127.0.0.1" ?

I wonder if we should switch to TCP when a port argument has been provided.

[matteodelucchi]

In that case, I get this error:
Can't read dir of '/etc/mysql/mariadb.conf.d/' (OS errno 2 - No such file or directory) [ERROR] Fatal error in defaults handling. Program aborted!

The directory actually would exist and contains the file mysqld.cnf, but I want to access the mysql in my docker container and not the local installation. I'm not an expert, but I think the path /etc/mysql/mariadb.conf.d/mysqld.cnf leads to a config file of my local installation and not the one in the docker container (didn't mount it to the docker as volume).

[krlmlr]

Can you connect to the MariaDB server from your host with the mysql command? What arguments do you use?

[matteodelucchi]

It's a MySQL server to which I connect with the arguments mentioned above (see "situation"). I read that the support of the R package RMySQL is not guaranteed anymore (didn't verify that though!) which is why I use RMariaDB to connect. I would like to mention that the connection can be established using the config file, it's just not very handy and would be much more convenient to specify the protocol as argument and updating the manual respectively.

[krlmlr]

I'm looking for ways to avoid adding an extra argument.

Can you connect with mysql --host=127.0.0.1 --port=6603 --user=root --password=secret ?

[matteodelucchi]

No, this results in:

$ mysql --host=localhost --port=6603 --user=root --password=secret
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Whereas in contrast specifying the protocol argument everything runs smoothly:

$ mysql --host=localhost --port=6603 --user=root --password=secret --protocol=tcp
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.21 MySQL Community Server - GPL
[...]

[krlmlr]

What about mysql --host=127.0.0.1 --port=6603 --user=root --password=secret ?

[matteodelucchi]

This works:

$ mysql --host=127.0.0.1 --port=6603 --user=root --password=secret
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.21 MySQL Community Server - GPL
[...]

But analogously specifying the host in R doesn't:

> con <- dbConnect(RMariaDB::MariaDB(),
+                  dbname="dbname",
+                  username="root",
+                  password="secret",
+                  host = "127.0.0.1")
Error: Failed to connect: Can't connect to MySQL server on '127.0.0.1' (111)
> con <- dbConnect(RMariaDB::MariaDB(),
+                  dbname="dbname",
+                  username="root",
+                  password="secret",
+                  host = "localhost")
Error: Failed to connect: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

[krlmlr]

Does your first R code need port = ... ?

[matteodelucchi]

My fault! Sry...

> con <- dbConnect(RMariaDB::MariaDB(),
+                  dbname="dbname",
+                  username="root",
+                  password="secret",
+                  host = "127.0.0.1",
+                  port = 6603)

Specifying host and port establishes successfully the connection. I wonder why the default host argument, which is NULL and corresponds to localhost, doesn't work...

> con <- dbConnect(RMariaDB::MariaDB(),
+                  dbname="dbname",
+                  username="root",
+                  password="secret",
+                  port = 6603)
Error: Failed to connect: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

[krlmlr]

The behavior is documented in ?dbConnect in this package.

I agree that your second example should switch to TCP/IP because you have specified a port. On the other hand, perhaps we really should follow the mysql semantics as you suggest and add a port argument?

Would you like to contribute a pull request?

[matteodelucchi]

I'll do but it will take some time.

[krlmlr]

We need to call mysql_options() with MYSQL_OPT_PROTOCOL in DbConnection::connect() .