"Can't connect to server on '127.0.0.1'" in RMariaDB, but mysql works

[uhkeller]

trafficstars

I want to connect to a MariaDB database on a remote server. The remote db only accepts local connections, so I first have to establish an SSH tunnel and then connect through that. Unfortunately, I cannot connect with RMariaDB. Here's what I did:

Establish an SSH tunnel:

ssh -L 3306:localhost:3306 myuser@remote_ip

While the SSH connection is up I can connect to the remote database on port 3306 on the local machine. This works using DBeaver as well as using mysql:

mysql -h 127.0.0.1 -u dbuser -p

However, it does not work using RMariaDB on my current machine:

con <-
  dbConnect(RMariaDB::MariaDB(), 
            user = "dbuser",
            password = "dbpassword",
            host = "127.0.0.1",
            dbname = "dbname",
            port = 3306)
Error: Failed to connect: Can't connect to server on '127.0.0.1' (60)

I'm using RMariaDB 1.2.2 with R 4.2.1 (homebrew) on macOS 12.6 (Apple Silicon). Some months back the same approach did work on an Intel Mac, also with homebrew R and RMariaDB.

Edit: To my relief it turns out that it still works with RMySQL.

[krlmlr]

Thanks. Can you please try installing RMariaDB from source, perhaps by cloning this repository?

[uhkeller]

Thank you for your quick response! The package was installed from source (precompiled binaries cannot be used with the homebrew-compiled version of R). I've now installed it from GitHub:

devtools::install_github("r-dbi/RMariaDB")

But get exactly the same error.

[krlmlr]

Thanks. Could you please reinstall and post the installation log?

[uhkeller]

ownloading GitHub repo r-dbi/RMariaDB@HEAD
✔  checking for file ‘/private/var/folders/t3/bqbwnz817290522m1phs7s5d8dfxgk/T/Rtmpfcw5MV/remotes1534e77bbd076/r-dbi-RMariaDB-4925d5f/DESCRIPTION’ ...
─  preparing ‘RMariaDB’:
✔  checking DESCRIPTION meta-information ...
─  cleaning src
─  running ‘cleanup’
─  checking for LF line-endings in source and make files and shell scripts
─  checking for empty or unneeded directories (365ms)
─  building ‘RMariaDB_1.2.2.9000.tar.gz’
   Warning: invalid uid value replaced by that for user 'nobody'
   Warning: invalid gid value replaced by that for user 'nobody'
   
Installing package into ‘/opt/homebrew/lib/R/site-library’
(as ‘lib’ is unspecified)
* installing *source* package ‘RMariaDB’ ...
** using staged installation
Using mariadb_config
Found mysql_config cflags and libs!
Using PKG_CFLAGS=-I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql
Using PKG_LIBS=-L/usr/local/opt/openssl/lib -L/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/lib/mariadb/ -lmariadb
Using PKG_PLOGR=
** libs
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c DbConnection.cpp -o DbConnection.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c DbResult.cpp -o DbResult.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaBinding.cpp -o MariaBinding.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaResult.cpp -o MariaResult.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaResultImpl.cpp -o MariaResultImpl.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaResultPrep.cpp -o MariaResultPrep.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaResultSimple.cpp -o MariaResultSimple.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaRow.cpp -o MariaRow.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaTypes.cpp -o MariaTypes.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c MariaUtils.cpp -o MariaUtils.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c RcppExports.cpp -o RcppExports.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c connection.cpp -o connection.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c driver.cpp -o driver.o
clang++ -std=gnu++14 -I"/opt/homebrew/Cellar/r/4.2.1_4/lib/R/include" -DNDEBUG -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb -I/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/include/mariadb/mysql -Ivendor -DRCPP_DEFAULT_INCLUDE_CALL=false -DRCPP_USING_UTF8_ERROR_STRING -DBOOST_NO_AUTO_PTR  -I'/opt/homebrew/lib/R/site-library/plogr/include' -I'/opt/homebrew/lib/R/site-library/Rcpp/include' -I/opt/homebrew/opt/gettext/include -I/opt/homebrew/opt/readline/include -I/opt/homebrew/opt/xz/include -I/opt/homebrew/include   -fPIC  -g -O2  -c result.cpp -o result.o
clang++ -std=gnu++14 -dynamiclib -Wl,-headerpad_max_install_names -undefined dynamic_lookup -single_module -multiply_defined suppress -L/opt/homebrew/Cellar/r/4.2.1_4/lib/R/lib -L/opt/homebrew/opt/gettext/lib -L/opt/homebrew/opt/readline/lib -L/opt/homebrew/opt/xz/lib -L/opt/homebrew/lib -o RMariaDB.so DbConnection.o DbResult.o MariaBinding.o MariaResult.o MariaResultImpl.o MariaResultPrep.o MariaResultSimple.o MariaRow.o MariaTypes.o MariaUtils.o RcppExports.o connection.o driver.o result.o -L/usr/local/opt/openssl/lib -L/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/lib/mariadb/ -lmariadb -L/opt/homebrew/Cellar/r/4.2.1_4/lib/R/lib -lR -lintl -Wl,-framework -Wl,CoreFoundation
ld: warning: directory not found for option '-L/usr/local/opt/openssl/lib'
ld: warning: -undefined dynamic_lookup may not work with chained fixups
installing to /opt/homebrew/lib/R/site-library/00LOCK-RMariaDB/00new/RMariaDB/libs
** R
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded from temporary location
** checking absolute paths in shared objects and dynamic libraries
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
* DONE (RMariaDB)
*

I hadn't noticed the warning about openssl before. The same appears when installing from CRAN. OpenSSL is installed on my system (through homebrew), but the folder is /opt/homebrew/opt/openssl/lib, not /usr/local/opt/openssl/lib.

[krlmlr]

Thanks, this is weird.

What do the command lines mariadb_config --libs and mysql_config --libs show? These are used by our ./configure script in https://github.com/r-dbi/RMariaDB/blob/main/configure.

[uhkeller]

➜  ~ mariadb_config --libs
-L/opt/homebrew/Cellar/mariadb-connector-c/3.3.2/lib/mariadb/ -lmariadb
➜  ~ mysql_config --libs
zsh: command not found: mysql_config
➜  ~ brew unlink mariadb-connector-c
Unlinking /opt/homebrew/Cellar/mariadb-connector-c/3.3.2... 5 symlinks removed.
➜  ~ brew link mariadb              
Linking /opt/homebrew/Cellar/mariadb/10.8.3_1... 321 symlinks created.
➜  ~ mysql_config --libs            
-L/opt/homebrew/Cellar/mariadb/10.8.3_1/lib  -lmariadb -lssl -lcrypto

Homebrew can't create symlinks for both mariadb and mariadb-connector-c at the same time. When mariadb-connector-c is linked, mysql_config does not work. When mariadb is linked, compilation of RMariaDB fails with this error:

ld: warning: directory not found for option '-L/usr/local/opt/openssl/lib'
ld: library not found for -lssl
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [RMariaDB.so] Error 1

[krlmlr]

Thanks, confirming similar behavior. It's a mess.

@jeroen: Do we need to fix the Homebrew formula, or take it even further upstream to MariaDB?

[uhkeller]

I managed to get RMariaDB working by installing mysql and mysql-connector-c++ in brew and then installing RMariaDB (from source). Maybe this helps others with the same problem while the issue is not resolved on the Homebrew/MariaDB side. (Sorry if this is a super obvious solution, it wasn't for me.)

[jeroen]

Does it work if you compile with the autobrew libs?

Sys.setenv(CI=1)
devtools::install_github("r-dbi/RMariaDB")

Or the binary from r-universe:

install.packages("RMariaDB", repos = "https://r-dbi.r-universe.dev")

[uhkeller]

Thanks for your reply! Autobrew sounds great, I wasn't aware of it. I'm a bit reluctant to use it though as I could not find any information on what it actually does. Does it use the default homebrew prefix? Do I have to set up anything before it works? I'm on a brand new machine and really don't want to risk messing up my setup by doing things I don't fully understand (as I have done multiple times in the past…).

As for the r-universe package, using install.packages() without the type argument just installs from source. I'm using the homebrew version of R so I don't think there are prebuilt R package binaries for it. And this installs an Intel binary though I'm on Apple Silicon:

install.packages("RMariaDB", repos = "https://r-dbi.r-universe.dev/", type = "mac.binary")

[jeroen]

Autobrew are just the default libs that are used for CRAN binary packages. You don't have to install anything special, it just works. But you do have to first remove your homebrew installation of mariadb-connector-c otherwise that one is still used.

brew remove mariadb-connector-c

And then just set the CI envvar and run the installation

Sys.setenv(CI=1)
devtools::install_github("r-dbi/RMariaDB")

[uhkeller]

Thanks again, it worked! Package installed and I can connect to DBs through an SSH tunnel. Feels a bit like magic. I would really appreciate it if you could point me towards additional information. I know I'm getting off topic here, but this is too intriguing. Does this work for all packages that have binaries available on CRAN? Is there any reason not to put Sys.setenv(CI=1) in my .Rprofile? I could not find anything on this in the R macOS FAQ or anywhere else…

[jeroen]

No, it's just something we do in the RMariaDB configure script to download our own build of mariadb-connector-c if it was not found on the system.

https://github.com/r-dbi/RMariaDB/blob/0d1ee4d0c8257ec142e54d8b6ea1a2befa1d2955/configure#L52-L57

Currently our build has mariadb-connector-c 3.1.11, so the bug must have appeared after that version.

[uhkeller]

Ah, ok. Thanks for the explanation.