Connect to db using DBI & pool in Shiny

Hi,
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 = "courses.csrrinzqubik.us-east-1.rds.amazonaws.com", 
                  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(
    dashboardPage(
      dashboardHeader(), 
      dashboardSidebar(), 
      dashboardBody(
        # inputs
        box(
          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
        box(tableOutput("table"))
      )
    )
  )

Loaded packages:

  library(shinydashboard)
  library(shiny)
  library(DBI)
  library(RMySQL)
  library(pool)

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.

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