0

I am attempting to connect to a remote database that requires SSH keys. I am running the Shiny Server on Ubuntu 20. The following snippet works from the R interpreter, but fails when in my Shiny app.

library(RMySQL) library(callr) setwd('/folder/to/pemfile') rs <- r_session$new(wait = TRUE, wait_timeout = 3500) rs$call(function(){ session <- ssh::ssh_connect("<user-name>@<ip-address>", keyfile = "myfile.pem", verbose = FALSE) ssh::ssh_tunnel(session, port=3307, target="127.0.0.1:3306") }) conn <- dbConnect(MySQL(), user='<db-user>', password='<db-password>', dbname='<db-name>', port=3307, host='127.0.0.1') query <- "<insert-query-here>" result <- dbSendQuery(conn, query) data_df <- fetch(result, n = -1) # Close Remote Session. rs$close() setwd('/path/to/original/folder')

The error message that I receive from the Shiny app is

 Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to MySQL server on '127.0.0.1' (115) Calls: runApp ... ..stacktraceon.. -> dbConnect -> dbConnect -> .local Execution halted

From other posts I have tried changing the MySQL server bind address to 0.0.0.0 from 127.0.0.1, but the same error is returned. Additionally, I tried using localhost instead of 127.0.0.1, but that fails for both the interpreter and in the Shiny App with an Access Denied error. I am leaning towards this being an issue with the Shiny installation given it works from the R terminal when run line by line.

MySQL version running on the Ubuntu machine

mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

R Package Version

> packageVersion('RMySQL') [1] ‘0.10.22’ > packageVersion('callr') [1] ‘3.7.0’ > > packageVersion('shiny') [1] ‘1.7.1’ >
Improve this question

1 Answer 1

Reset to default
1

I'm not sure I would call this a "fix", but the Shiny app works after I did the following.

Sys.sleep(1) # <- Added this line before the line below conn <- dbConnect(MySQL(), user='<db-user>', password='<db-password>', dbname='<db-name>', port=3307, host='127.0.0.1')

I came to this solution by running the Shiny app from the browser, and copying/pasting into the R interpreter, both with failure.

However, when I copied/pasted the code into the terminal, then ran the connection line manually, it worked as expected so I added a sleep line and it worked.

If anyone has additional explanation, I'd be very curious.

EDIT: I have come to the conclusion, that the reason for the error is because ssh_tunnel does not have enough time to make the connection before running dbConnect().

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.