App publish to Shinyappsio can not connect MySQL

Posit Professional & Hosted Products shinyapps.io
1

When I published it to shinyapps.io, Mysql database can't get data from my app. I want to develop an app to record the data to the Mysql.

Here are some informations: (1) That app work well in local. (2) Mysql database can be accessed from aother IP address. (3) Warning logs: Warning: Error in .local: Failed to connect to database: Error: Can't connect to MySQL server on '133.41.xxx.xxx' (110)

If need any other information, please let me konw.

Here is the app code

library(pool)
library(DBI)
library(dplyr)
library(RMySQL)
library(datasets)

options(shiny.usecairo = FALSE)
cost <- as.character(c(500,200,180))
at <- c('14:55','12:30','21:30')
t <- rbind(at,cost)
c1 <- c('departure time','cost')
t <- cbind(c1,t)
mode1 <- c('mode','plane','train','bus')
colnames(t) <- mode1

ui <- fluidPage(
absolutePanel(wellPanel(selectInput(inputId="selectInput_1",selected=NULL, choices=c("plane","train","bus"), multiple=FALSE, width=NULL, selectize=TRUE, label="choose", size=NULL)), top=296, height=80, left=246, width=382),
absolutePanel(wellPanel(tableOutput(outputId="tableOutput_1")), top=113, height=178, left=246, width=379),
absolutePanel(wellPanel(textOutput(inline=FALSE, outputId="textOutput_1")), top=7, height=101, left=245, width=380),
absolutePanel(wellPanel(actionButton(width=65, inputId="actionButton_1", icon=NULL, label="submit")), top=439, height=102, left=547, width=100)

)
server <- function(input, output) {
output$tableOutput_1 <- renderTable({
t})

output$textOutput_1 <- renderText({'What kind of transportation did you choose to return home during the COVID-19 ?'})
observeEvent(input$actionButton_1,{
con <- dbConnect(MySQL(),
user = 'root',
password = 'xxxxxxxx',
host = '133.41.xxx.xxx',
dbname='app',
port = 3306
)
re <- dbSendStatement(con,
# paste0("INSERT INTO tappable VALUES ('",input$selectInput_1,"')")
"INSERT INTO tappable VALUES (9)"

)})
}
shinyApp(ui=ui, server=server)

Here are logs

2022-10-21T02:27:21.829011+00:00 shinyapps[7323740]: Warning: Error in .local: Failed to connect to database: Error: Can't connect to MySQL server on '133.41.177.100' (110)
2022-10-21T02:27:21.842041+00:00 shinyapps[7323740]: 15:
2022-10-21T02:27:21.842072+00:00 shinyapps[7323740]: 13: fn
2022-10-21T02:27:21.842086+00:00 shinyapps[7323740]: 8: retry
2022-10-21T02:27:21.842090+00:00 shinyapps[7323740]: 7: connect$retryingStartServer
2022-10-21T02:27:21.842094+00:00 shinyapps[7323740]: 6: eval
2022-10-21T02:27:21.842097+00:00 shinyapps[7323740]: 5: eval
2022-10-21T02:27:21.842100+00:00 shinyapps[7323740]: 4: eval
2022-10-21T02:27:21.842103+00:00 shinyapps[7323740]: 3: eval
2022-10-21T02:27:21.842105+00:00 shinyapps[7323740]: 2: eval.parent
2022-10-21T02:27:21.842114+00:00 shinyapps[7323740]: 1: local
2022-10-21T02:27:45.625927+00:00 shinyapps[7323740]: Running on host: b48a6160f659
2022-10-21T02:27:45.625958+00:00 shinyapps[7323740]: Server version: 2022.09.0
2022-10-21T02:27:45.625963+00:00 shinyapps[7323740]: LANG: C.UTF-8
2022-10-21T02:27:45.625975+00:00 shinyapps[7323740]: Working directory: /srv/connect/apps/questionnare
2022-10-21T02:27:45.626241+00:00 shinyapps[7323740]: Running content using the current R environment
2022-10-21T02:27:45.630485+00:00 shinyapps[7323740]: R version: 4.2.1
2022-10-21T02:27:45.630527+00:00 shinyapps[7323740]: shiny version: 1.7.2
2022-10-21T02:27:45.630544+00:00 shinyapps[7323740]: httpuv version: 1.6.6
2022-10-21T02:27:45.630560+00:00 shinyapps[7323740]: rmarkdown version: (none)
2022-10-21T02:27:45.630569+00:00 shinyapps[7323740]: knitr version: (none)
2022-10-21T02:27:45.630583+00:00 shinyapps[7323740]: jsonlite version: 1.8.2
2022-10-21T02:27:45.630589+00:00 shinyapps[7323740]: RJSONIO version: (none)
2022-10-21T02:27:45.630605+00:00 shinyapps[7323740]: htmltools version: 0.5.3
2022-10-21T02:27:45.630611+00:00 shinyapps[7323740]: reticulate version: (none)
2022-10-21T02:27:45.630879+00:00 shinyapps[7323740]: Using pandoc: /opt/connect/ext/pandoc/2.16
2022-10-21T02:27:45.951908+00:00 shinyapps[7323740]: Starting R with process ID: '109'
2022-10-21T02:27:45.952258+00:00 shinyapps[7323740]: Shiny application starting ...
2022-10-21T02:27:46.541560+00:00 shinyapps[7323740]: Attaching package: ‘dplyr’
2022-10-21T02:27:46.542074+00:00 shinyapps[7323740]: The following objects are masked from ‘package:stats’:
2022-10-21T02:27:46.542096+00:00 shinyapps[7323740]: filter, lag
2022-10-21T02:27:46.542322+00:00 shinyapps[7323740]: The following objects are masked from ‘package:base’:
2022-10-21T02:27:46.542342+00:00 shinyapps[7323740]: intersect, setdiff, setequal, union```
2

Well, this is most likely a problem with your MySQL security settings, are you sure this is a reachable public IP accepting connections on port 3306? Have you checked firewall rules in your server allow for incoming traffic from remote locations?

3

Thank you for the reply.

（1）Are you sure this is a reachable public IP accepting connections on port 3306?
I got IPv4 IP address from commed prompt use ipconfig . Mysql from anther PC in different ip can connect to this Mysql database. I also add new Inbound rules though port 3306 in firewall settings.

mysql> select user,host from mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| root             | %              |
| unlimited        | %              |
| user             | %              |

(2) Have you checked firewall rules in your server allow for incoming traffic from remote locations?
To develop this app I turn off the firewall in windows setting center.

4

That doesn't really answer my question, even more, it suggests you are using a local IP (within your LAN). Usually, the public IP (if you have one, which is not always the case) is assigned to your router (or modem), there, you need to configure port forwarding to redirect incoming traffic toward your MySQL server.

5

This reply is very useful.
How can I get My public IP? I use a web

https://www.whatismyip.com/

But I can not connect to my mysql though this IP.

6

As I said, you don't necessarily have a public IP assigned, Internet Service Providers very often use a NAT3 configuration which basically means they group several users under a single public IP. Check your router's WAN IP, if it doesn't match what you get on that website, then you don't have a public IP.

Also, even if you you do have a public IP, you need to configure port forwarding in your router to redirect incoming traffic towards your server. The exact configuration steps vary depending on your router's brand but here you can find an example.

7

I'm facing a similar problem. I'm using Windows, thus finding it hard to follow up on Andres' blog. Any help?