Closing active connections using RMySQL

Question

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).

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

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.

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.)

Solution 1

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

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

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

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

Solution 2

Close all active connections:

dbDisconnectAll <- function(){
  ile <- length(dbListConnections(MySQL())  )
  lapply( dbListConnections(MySQL()), function(x) dbDisconnect(x) )
  cat(sprintf("%s connection(s) closed.\n", ile))
}

executing: dbDisconnectAll()

Solution 3

Simplest:

lapply(dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)

List all connections and disconnect them by lapply

Solution 4

Closing a connection

You can use dbDisconnect() together with dbListConnections() to disconnect those connections RMySQL is managing:

    all_cons <- dbListConnections(MySQL())
    for(con in all_cons) 
      dbDisconnect(con)

Check all connections have been closed

    dbListConnections(MySQL())

You could also kill any connection you're allowed to (not just those managed by RMySQL):

    dbGetQuery(mydb, "show processlist")

Where mydb is..

    mydb = dbConnect(MySQL(), user='user_id', password='password', 
                      dbname='db_name', host='host')

Close a particular connection

    dbGetQuery(mydb, "kill 2")
    dbGetQuery(mydb, "kill 5")

Solution 5

lapply(dbListConnections(MySQL()), dbDisconnect)

View more solutions

Comments

[briantaurostack7]

please explain why do you think this solution works to benefit the community.

[Neal Barsch]

This answer is far better than the accepted answer