Connect to db using DBI & pool in Shiny

I'm trying to connect to a demo MySQL db from a shiny app.
I used two ways: DBI::dbConnect() and pool::dbPool()

When using pool::dbPool() I got the error:
Error: Failed to connect to database: Error: Access denied for user 'student'@'XXX.XXX.XX.XXX' (using password: NO)
(IP address removed)

I got the same error when using DBI::dbConnect() .

Here is the server function I used:

 server <- shinyServer(function(input, output){
     output$table <- renderTable({
           if (input$check == TRUE){
                 my_db <- DBI::dbConnect(
                         drv = RMySQL::MySQL(),
                         dbname = input$nameDbi,
                         host = input$hostDbi,
                         port = input$portDbi,
                         username = input$userNameDbi,
                         password = input$passWordDb)
                 my_db <- tbl(my_db)

I tried also to put input$hostDbi, input$hostDbi, input$portDbi, userNameDbi & input$passWordDb into get() it returns the error: Failed to connect to database: Error: Unknown MySQL Server Host 'input$hostDbi' (11001)

Somebody has encountred this errors before?
Any idea about how to fix it?

Thanks :slight_smile:

This suggest your user is not allowed to connect to the database, this is related to the MySQL security configuration rather than R or any package.

You are not showing a complete example, but is seems like you don't have an input exactly named hostDbi so the dbConnect() is taking 'input$hostDbi' as a literal string.

1 Like

Thanks for your return.
Thus, when using in the R console the following code:

con1 <- dbConnect(RMySQL::MySQL(), 
                  dbname = "company",
                  host = "", 
                  port = 3306, 
                  user = "student", 
                  password = "datacamp"

The connection to the db is well established. However when integrated to shiny it doesn't work. I think the inputs type is not recognized. (MySQL db connection info are right)

Here is the ui, where I think inputs are exactly named:

  ui <- shinyUI(
        # inputs
          textInput("nameDbi", "Db name :"),
          textInput("hostDbi", "Db host :"),
          numericInput("portDbi","Server port", value = 1000, min = 1, max = 10000),
          textInput("userNameDbi", "Login :"), 
          passwordInput("passWordDbi", "Password :", value = ""),
          checkboxInput("check","Check to connect :")),
        # outputs

Loaded packages:


I'd recommend adding a browser() statement immediately before DBI::dbConnect() so you can interactively experiment to figure out the difference between the code that works from the console and the code that doesn't work in Shiny.

1 Like

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.