host not read from .my.cnf

[klopianoRTA]

I'm having an issue with the latest version of RMySQL. Specifically it appears that the host parameter in the .my.cnf file is not being read. I know the .my.cnf file is being read to get the user and password.

The following error for the first case below
Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Note the three cases below. The second case works when I specify the exact same hostname as in the .my.cnf. I have not needed to do this before installing 0.10.16. Is this behavior expected?

library(RMySQL)

• This does not work
  tmpConn = dbConnect(RMySQL::MySQL(),group="auroraedsl", dbname="schemaname")
• This does work
  tmpConn = dbConnect(RMySQL::MySQL(),group="groupname", dbname="schemaname", host="hostname")
• This also works
  tmpConn = dbConnect(RMariaDB::MariaDB(),group="groupname", dbname="schemaname")

Session Info

`R version 3.5.2 (2018-12-20)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Mojave 10.14.2

Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.5/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] RMySQL_0.10.16 DBI_1.0.0

loaded via a namespace (and not attached):
[1] bit_1.1-14 compiler_3.5.2 hms_0.4.2 tools_3.5.2
[5] RMariaDB_1.0.6 yaml_2.2.0 Rcpp_1.0.0 bit64_0.9-7
[9] pkgconfig_2.0.2 rlang_0.3.1

[klopianoRTA]

To be clear - The command above that does not work does work when using version 0.10.15. I was having trouble using install_version to get 0.10.15 installed so I ended up having a colleague send me the RMySQL director from the Library directory on their machine. Manually pasting this in my Library directory allowed me to get back to 0.10.15.

In reading through recent commits to master, my guess is deleting this file may be causing the issue

https://github.com/r-dbi/RMySQL/blob/3a7febefa03e16c6b1365856a51935087a8e2064/tools/mysql-connector-c.rb

[mstuart1]

I am also having this problem.

Input:
db <- src_mysql(dbname = x, default.file = path.expand("~/myconfig.cnf"), port = 3306, create = F)

Output:
Error in .local(drv, ...) :
Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

This line of code worked Thursday 1/24/2019 and did not work on Friday 1/25/2019 and the only thing I can think of is that I updated some package unwittingly that caused some change. I have tried to install the same configuration as my co-worker who is still able to run this line, but I cannot install RMySQL 0.10.11, the oldest I can install is RMySQL 0.10.14. Older than that I get an installation error:
./RS-MySQL.h:31:10: fatal error: 'mysql_version.h' file not found. I also get in an error during installation that does not prevent installation: can't open config file: /usr/local/etc/openssl/openssl.cnf rmysql.

I am running:
MacOS Mojave 10.14.2
RStudio 1.1.463
R 3.3.3
DBI 0.5-1
dplyr 0.5.0
RMySQL 0.10.14

I have also tried this with
R 3.5
DBI 1.0.0, 1.1.0, 1.2.0
dplyr 0.7.4, 0.7.8
RMySQL 0.10.15, 0.10.16

I tried copying the raw of the mysql-connector-c.rb mentioned above and placing that file into the RMySQL directory but that didn't help.

[dan-reznik]

I just had this issue myself.

  con <- DBI::dbConnect(RMySQL::MySQL(),
                        default.file = ".my.cnf",
                        group = "mygroup")

worked perfectly under 3.5.1 but when I upgraded R to 3.5.2, it stopped working! Calling dbConnect() with all the connection credentials as parameters works, so the problem seems to be with PARSING of .my.cnf when DBI and RMySQL are run within 3.5.2

[klopianoRTA]

@dan-reznik Are you using RMySQL 0.10.16? I've had no trouble with 0.10.15 and R 3.5.2

[mstuart1]

Yes, I am using 0.10.16. But I've also tried with 0.10.15 and had the same issue.

[dan-reznik]

i started to use RMariaDB and the issue went away

[mstuart1]

I get the same error whether I use RMySQL or RMariaDB:
Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[dan-reznik]

try this

  con <- DBI::dbConnect(RMariaDB::MariaDB() # RMySQL::MySQL(),
                        ,default.file = "./.my.cnf" # notice the ./ 
                        ,group = "the group name in your file"
                        )

[gserapio]

Has anyone had luck resolving this issue for MySQL? Unfortunately, I can't migrate my data to MariaDB.

[dan-reznik]

u don' t need to migrate. keep db as mysql. just use the RMariaDB package

…

On Tue, Feb 26, 2019 at 11:13 PM Greg Serapio-García < ***@***.***> wrote: Has anyone had luck resolving this issue for MySQL? Unfortunately, I can't migrate my data to MariaDB. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#225 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APeqlvb7LoIJ880WnleeOxW7hVocxFFMks5vRem4gaJpZM4Z4OrJ> .

[mstuart1]

Currently the .my.cnf file is in my top directory (~). The format of the file contents are:

# Config file to connect to the remote DB
[client]
username=my_username
password=my_password
host=my.host.connection.edu

So should I try:
con <- DBI::dbConnect(RMariaDB::MariaDB() # RMySQL::MySQL(),
,default.file = "~/.my.cnf" # notice the ./
,group = "the group name in your file"
)

and would the group name be "client"?

Or should I move my file to the working directory of my repo?

[wikithink]

@mstuart1 yes,group name is "client", but suggest change the name to avoid some possible troubles.

[mstuart1]

con <- DBI::dbConnect(RMySQL::MySQL(), default.file="~/my.cnf", group="client")

I'm still getting an error for this:

Error in .local(drv, ...) :
Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
In addition: Warning message:
In .local(drv, ...) : partial argument match of 'group' to 'groups'

[mstuart1]

Figured out that I can use rstudioapi to request password. Except it doesn't work with package testing or Rmarkdown knitting :/

[dc1340]

I'm also trying to deal with this issue, and can't migrate to MariaDB due to it currently not handling JSON columns. Interestingly, RMySql successfully handles the .my.cnf in my development environment (Debian GNU/Linux 9 (stretch)), but it's failing in the Rstudio Connect server I'm trying to publish to ( Ubuntu 18.04.3 LTS)

• Session Info for Publishing Environment where failures are occurring:

R version 3.6.0 (2019-04-26)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.3 LTS

Matrix products: default
BLAS: /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1

locale:
[1] LC_CTYPE=C.UTF-8 LC_NUMERIC=C LC_TIME=C.UTF-8
[4] LC_COLLATE=C.UTF-8 LC_MONETARY=C.UTF-8 LC_MESSAGES=C.UTF-8
[7] LC_PAPER=C.UTF-8 LC_NAME=C LC_ADDRESS=C
[10] LC_TELEPHONE=C LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] DBI_1.0.0.9003

loaded via a namespace (and not attached):
[1] compiler_3.6.0 RMySQL_0.10.17

• Dev Session Info (No errors):

R version 3.6.0 (2019-04-26)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.3 LTS

Matrix products: default
BLAS: /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1

locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
[3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
[5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=C
[7] LC_PAPER=en_US.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] DBI_1.0.0.9003

loaded via a namespace (and not attached):
[1] compiler_3.6.0 RMySQL_0.10.17