How to make a brilliant application for communicating with a cloud-based relational database (in MySQL)?

Question

This may seem fairly simple for many experts, but after spending hours I have not yet come up with the right solution, I might have missed something that is easy to set up.

My question is how to get this brilliant application to talk to a cloud-based relational database like Google MySQL services after deploying to shinyapps.io

I successfully ran this brilliant application locally on my 64-bit Windows 7 machine because I specified User DSN as google_sql with the correct MySQL ODBC 5.3 ANSI driver driver, ip, password, etc., so in the odbcConnect line of code odbcConnect I can simply provide dsn, username and password to open the connection. However, when I deploy it to shinyapps.io, this did not work out with my expectation. I assume my google_sql DSN is not recognized by shinyapps.io, so to get it working, what should I do? Should I change the code? Or tune in to shinyapps.io

PS: It's not about how to install RMySQL, someone sends ans to a similar question here (unless they think that RMySQL can do what RODBC cannot do) connecting a brilliant application to the mysql database on the server

server.R

  library(shiny) # library(RODBC) library(RMySQL) # ch <- odbcConnect(dsn = "google_sql", uid = "abc", pwd = "def") ch <- dbConnect(MySQL(),user='abc',password='def', host = 'cloud_rdb_ip_address', dbname = 'my_db') shinyServer(function(input, output) { statement <- reactive({ if(input$attribute == 'All'){ sprintf("SELECT * FROM test_db WHERE country = '%s' AND item = '%s' AND year = '%s' AND data_source = '%s'", input$country,input$item,input$year,input$data_source) }else{ sprintf("SELECT * FROM test_db WHERE country = '%s' AND item = '%s' AND attribute = '%s' AND year = '%s' AND data_source = '%s'", input$country,input$item,input$attribute,input$year,input$data_source) } }) output$result <- renderTable(dbFetch(dbSendQuery(ch, statement=statement()),n=1000)) }) 

ui.R

 library(shiny) shinyUI(fluidPage( # Application title headerPanel("Sales Database User Interface"), fluidRow( column(4, selectInput('country','Country',c('United States','European Union','China'),selected = NULL), selectInput('item','Item',c('Shoes','Hat','Pants','T-Shirt'),selected = NULL), selectInput('attribute','Attribute',c('All','Sales','Procurement'),selected = NULL) ), column(4, selectInput('year','Calendar Year',c('2014/2015','2015/2016'),selected = NULL), selectInput('data_source','Data Source',c('Automation','Manual'),selected = NULL) ) ), submitButton(text = "Submit", icon = NULL), # Sidebar with a slider input for the number of bins # Show a plot of the generated distribution mainPanel( tableOutput("result") ) )) 

I think it's worth publishing the shiny showLogs() error shiny showLogs() for an expert to enlighten me pls,

 2015-05-04T06:32:16.143534+00:00 shinyapps[40315]: R version: 3.1.2 2015-05-04T06:32:16.392183+00:00 shinyapps[40315]: 2015-05-04T06:32:16.143596+00:00 shinyapps[40315]: shiny version: 0.11.1 2015-05-04T06:32:16.392185+00:00 shinyapps[40315]: Listening on http://0.0.0.0:51336 2015-05-04T06:32:16.143598+00:00 shinyapps[40315]: rmarkdown version: NA 2015-05-04T06:32:16.143607+00:00 shinyapps[40315]: knitr version: NA 2015-05-04T06:32:16.143608+00:00 shinyapps[40315]: jsonlite version: NA 2015-05-04T06:32:16.143616+00:00 shinyapps[40315]: RJSONIO version: 1.3.0 2015-05-04T06:32:16.143660+00:00 shinyapps[40315]: htmltools version: 0.2.6 2015-05-04T06:32:16.386758+00:00 shinyapps[40315]: Using RJSONIO for JSON processing 2015-05-04T06:32:16.386763+00:00 shinyapps[40315]: Starting R with process ID: '27' 2015-05-04T06:32:19.572072+00:00 shinyapps[40315]: Loading required package: DBI 2015-05-04T06:32:19.831544+00:00 shinyapps[40315]: Error in .local(drv, ...) : 2015-05-04T06:32:19.831547+00:00 shinyapps[40315]: Failed to connect to database: Error: Lost connection to MySQL server at 'reading initial communication packet', system error: 0 2015-05-04T06:32:19.831549+00:00 shinyapps[40315]: 

PS: I think I need a white list of shinyapps.io ip address for my Google. Could to enable deployment on shinyapps.io.

Answer 1

I really managed to answer this question myself, but I wanted to share it, because it may be related to others.

Listed below are the IP addresses you need for whitelisting.

54.204.29.251

54.204.34.9

54.204.36.75

54.204.37.78

Answer 2

The list provided in the pidig89 answer is the correct IP list, but instead of trusting some random list found in the SO answer, you can find the most relevant information, the date list on your support site: https: //support.rstudio. com / hc / en-us / articles / 217592507-How-do-I-give-my-application-on-shinyapps-io-access-to-my-remote-database-

(They "formally" announced this as the recommended "white list" method on the mailing list on July 28, 2016 )

Answer 3

If you think that this is a problem with shinyapps with whitelisting your IP (I'm not saying that the actual problem, but if there is one), then I send google shinyapps to the group, as shinyapps developers control it and often respond.

https://groups.google.com/forum/#!forum/shinyapps-users