使用RMySQL关闭活动连接 [英] Closing active connections using RMySQL

问题描述

按照

As per my question earlier today, I suspect I have an issue with unclosed connections that is blocking data from being injected into my MySQL database. Data is being allowed into tables that are not currently being used (hence I suspect many open connections preventing uploading into that particular table).

我正在Ubuntu服务器上使用RMySQL将数据上传到MySQL数据库.

I am using RMySQL on Ubuntu servers to upload data onto a MySQL database.

我正在寻找一种方法:a)确定连接是否打开b)关闭它们. SQL命令行中的命令exec sp_who和exec sp_who2返回SQL代码错误.

I'm looking for a way to a) determine if connections are open b) close them if they are. The command exec sp_who and exec sp_who2 from the SQL command line returns an SQL code error.

另一注:我可以连接，完成上传过程并成功结束R进程，而仅尝试使用该表时，服务器上没有数据(通过SQL命令行检查).

Another note: I am able to connect, complete the uploading process, and end the R process successfully, and there is no data on the server (checked via the SQL command line) when I try only that table.

(顺便说一句，如果其他所有方法都失败了，只需删除该表并创建一个具有相同名称的新表即可解决它?这很痛苦，但是可行.)

(By the way,: If all else fails, would simply deleting the table and creating a new one with the same name fix it? It would be quite a pain, but doable.)

推荐答案

a. dbListConnections( dbDriver( drv = "MySQL"))

b. dbDisconnect( dbListConnections( dbDriver( drv = "MySQL"))[[index of MySQLConnection you want to close]]).关闭所有:lapply( dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)

b. dbDisconnect( dbListConnections( dbDriver( drv = "MySQL"))[[index of MySQLConnection you want to close]]). To close all: lapply( dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)

是的，您可以重写表，当然，您将丢失所有数据.或者，您可以指定dbWriteTable(, ..., overwrite = TRUE).

Yes, you could just rewrite the table, of course you would lose all data. Or you can specify dbWriteTable(, ..., overwrite = TRUE).

我还将使用其他选项，例如row.names，header，field.types，quote，sep，eol.我在RMySQL中也有很多奇怪的行为.我不记得具体细节，但是当我做错了某些事情(例如忘记设置row.names)时，似乎没有任何错误消息. HTH

I would also play with the other options, like row.names, header, field.types, quote, sep, eol. I've had a lot of weird behavior in RMySQL as well. I can't remember specifics, but it seems like I've had no error message when I had done something wrong, like forget to set row.names. HTH

这篇关于使用RMySQL关闭活动连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！