How to run RMySQL in shinyapps (working fine locally)

Question

I have a weird problem: Using RMySQL from Shiny (running locally) I have no problem to retrieve data from MySQL database (small table, few rows only). But once the app is deployed (shinyapps.io) the query result contains zero rows (but column names are fine). Looking at the shinyapps.io log:

Warning in dbFetch(rs, n = n, ...) : error while fetching rows

What I am doing wrong? The exact same thing was working before and now I can't make it running. MySQL connection seems fine.

library(shiny)
library(DBI)

ui <- fluidPage(
    numericInput("nrows", "Enter the number of rows to display:", 5),
    tableOutput("tbl")
)

server <- function(input, output, session) {
    output$tbl <- renderTable({
        conn <- dbConnect(
            drv = RMySQL::MySQL(),
            dbname = "***",
            host = "***",
            username = "***",
            password = "***")
        on.exit(dbDisconnect(conn), add = TRUE)
        dbGetQuery(conn, paste0(
            "SELECT * FROM datasets LIMIT ", input$nrows, ";"))
    })
}

shinyApp(ui, server)

EDIT: When I use Shiny dummy database (from this example) it is working fine, so looks like some problem with MySQL but can't figure it out what... Any ideas?

dbname = "shinydemo",
host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
username = "guest",
password = "guest")

EDIT2 I tried everything. Create new table, new database (same hosting though), different shinyapps account, fresh R installation with all updated packages, still the same problem. When app is running locally, everything is fine. But from shinyapps - error and zero results (except colnames).

Answer 1

Ok, I have no idea why, but looks like changing table engine fix the issue

ALTER TABLE table_name ENGINE = InnoDB