CODEWITHSUNDEEP
shinyrmysql
Rich Pauloo
Rich Pauloo

Reputation: 8412

Connect to MySQL from shinyapps.io

I have a working Shiny App that queries a remote MySQL database via pool that I can run on my local machine.

The MySQL server has whitelisted shinyapps.io IP addresses.

When I deploy it to shinyapps.io, I get this error:

enter image description here

.global

library(shiny)
library(DBI)
library(pool)
library(DT)

pool <- dbPool(
  drv      = RMySQL::MySQL(),
  dbname   = "gw_observatory",
  host     = "sage.metro.ucdavis.edu",
  username = "gw_observatory", 
  password = "password"
)
onStop(function() {
  poolClose(pool)
})

.server

shinyServer(function(input, output, session) {

  output$data_table <- renderDataTable({

    DT::datatable(pool %>% tbl("small_data") %>% collect())

  })

.ui

shinyUI(
  fluidPage(
      mainPanel(DT::dataTableOutput("data_table"))
  )
)

Upvotes: 2

Views: 1485

Answers (1)

Rich Pauloo
Rich Pauloo

Reputation: 8412

UPDATE: It works now. My system admin added a port, and that fixed the issue.

For anyone down the line that is running into this issue, there are the steps I recommend:

  1. make sure your shinyapp works locally

  2. if you get deployment errors on shinyapps.io, make sure:

    • your database has whitelisted shinyappsio IP addresses
    • your host is an external, public IP or URL and not an internal one
    • some institutions have very guarded firewalls. Try adding a port to your database.

I hope this helps someone down the line!

pool <- dbPool(
  drv      = RMySQL::MySQL(),
  dbname   = "some_name",
  host     = "123.45.678.901",
  username = "some_username", 
  password = "password",
  port     = 1234567
)

Upvotes: 1

Related Questions