RStudio Shiny Error mysqlNewConnection最多16个连接 [英] RStudio Shiny Error mysqlNewConnection maximum of 16 connections

问题描述

我运行了一个闪亮的服务器，该服务器连接到MySQL数据库.到目前为止运行良好的页面现在给我这个错误:

I got a shiny server running that connects to a MySQL database. The page that was so far working fine is giving me this error now:

Error in mysqlNewConnection(drv, ...) : RS-DBI driver: 
(cannot allocate a new connection -- maximum of 16 connections already opened)

这让我想知道如何在交互式网页中处理开放的mysql连接.

Which makes me wonder how should I be handling open mysql connections in an interactive webpage.

首先，dbConnect(MySQL(),...)语句应该在shinyServer方法之前还是内部?

Firstly, should the dbConnect(MySQL(),...) statement be before the shinyServer method or inside?

如果我在server.R的末尾添加dbDisconnect(dbcon)，那么我会从页面收到Error: expired MysqLConnection错误，并且不会显示任何数据.

If I add a dbDisconnect(dbcon) at the end of server.R, then I get an Error: expired MysqLConnection error from the page, and does not show any data.

我也在shinyServer方法之内或之前对此进行了尝试:

I tried with this as well inside or before the shinyServer method:

 on.exit(dbDisconnect(dbcon), add=TRUE)

或

 on.exit(dbDisconnect(dbcon))

因此，页面的代码仅在我保持连接打开的情况下才对我有用，我认为这可能会导致上述最大连接错误.

So the code for the page itself only works for me if I leave the connection open, which I suppose then can cause the maximum connections error above.

如何在Shiny中处理这些情况?

How to handle these situations in Shiny?

推荐答案

您可以在global.R文件中或shinyServer外部设置连接，例如https://groups.google.com/forum/#!topic/shiny-discuss/0VjQc2a6z3M 是:

You can set the connection in the global.R file or outside shinyServer an example from https://groups.google.com/forum/#!topic/shiny-discuss/0VjQc2a6z3M is:

library(RMySQL)

getConnection <- function(group) {

  if (!exists('.connection', where=.GlobalEnv)) {
    .connection <<- dbConnect(MySQL(), group=group)
  } else if (class(try(dbGetQuery(.connection, "SELECT 1"))) == "try-error") {
    dbDisconnect(.connection)
    .connection <<- dbConnect(MySQL(), group=group)
  }

  return(.connection)
}

这定义了一个检查全局环境中连接的函数.如果找不到，它将创建一个.如果找到一个但无法连接，则重新启动连接.没有给出明确的断开连接，因此我认为连接最终将只允许超时.

This defines a functions that checks for a connection in the global env. If one is not found it creates one. If one is found but cannot be connected to then the connection is restarted. No explicit disconnect is given so I guess the connection is just allowed to timeout eventually.

这篇关于RStudio Shiny Error mysqlNewConnection最多16个连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！