Shiny timing out with Pool connection

[Devin Keane]

Hello, I'm trying to use my shiny app with MySQL via Pooled connection:

pool <<- dbPool(drv = RMySQL::MySQL(), 

               dbname = "sigma401k", 

               host = "sigma401k.c4cj5sy73xe4.us-east-1.rds.amazonaws.com", 

               port = 3306, 

               username = "***", 

               password = "***")

I have no issues running this locally, maintaining connections, fetching/sending data in between R and SQL.  However, when deployed, the app stays blank and times out, claiming no database connection is possible.  My shiny.io logs say this:

I was previously using the RMySQL/DBI version of dbConnect / dbDisconnect for each query  (I have about 10 queries in the app at various reactive stages), which is obviously not the way to go being slow and losing connections, but it did work when deployed.  Is this an issue with package versions?  See below for all the versions I'm running.  I'd really like to use Pool but it's giving me fits.  Thanks in advance!

[Bárbara Borges]

Are you using `dplyr` to run any of the queries? If so, please downgrade from `dplyr` 0.7.0 to `dplyr` 0.5.0 for the time being (this compatibility issue is almost fixed, but it's not on the master branch of `pool` yet):

devtools::install_github("hadley/dp...@v0.5.0")

You need to do this for now -- this is a temporary fix, as we work on compatibility with `dplyr` 0.7.0 and the newly released `dbplyr`. We've put up a notice on the README (https://github.com/rstudio/pool) and the article about this (https://shiny.rstudio.com/articles/pool-dplyr.html).

However, if your app is failing and you're NOT using `dplyr` to run the queries, that is unexpected. Can you tell me which version of `DBI` you have and give me a minimal reproducible example (even if it works fine locally, it's still useful because I can test-deploy it myself and track down any possible bugs that way)

Let me know, thanks!

[Devin Keane]

Thanks for the reply!  Unfortunately it's not dplyr, I read up on your pages about that so am avoiding it. I believe I have the latest version of DBI (0.7). The pool connection should establish right after loading all packages when initializing the app.  I will create a short reproducable example soon.

[Devin Keane]

Mystery solved.  I needed to change the security settings in my AWS account to accept all IP addresses.  Worked fine from my home IP but not from Shiny's, but because of all the version differences with R version 3.4 I was focused on that as the issue.  Everything works fine now. Thanks!