Connect to db using DBI & pool in Shiny

[Cecinerock]

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

[andresrcs]

@Cecinerock

Error: Failed to connect to database: Error: Access denied for user 'student'@'XXX.XXX.XX.XXX' (using password: NO)

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.

@Cecinerock

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

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.

[Cecinerock]

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)

[hadley]

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.