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

You could try `show full processlist;` to see the open connections. — cryo111, Jul 08 '13 at 01:57

StatSandwich · Accepted Answer · 2013-11-01T20:01:07.407

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

Taz · Answer 2 · 2015-10-23T15:27:46.143

15

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

edited Oct 23 '15 at 15:27

answered Oct 23 '15 at 15:19

Taz

5,755
6
26
63

This answer is far better than the accepted answer – Neal Barsch Apr 27 '21 at 17:01

score 3 · Answer 3 · answered Jan 02 '18 at 09:47

3

Simplest:

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

List all connections and disconnect them by lapply

answered Jan 02 '18 at 09:47

Giovanni Campagnoli

39
4

score 2 · Answer 4 · answered Oct 15 '16 at 08:10

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

score 1 · Answer 5 · edited Dec 11 '19 at 22:02

1

lapply(dbListConnections(MySQL()), dbDisconnect)

edited Dec 11 '19 at 22:02

Dharman

30,962
25
85
135

answered Dec 11 '19 at 17:42

Satish Mehta

11
2

1

please explain why do you think this solution works to benefit the community. – briantaurostack7 Dec 11 '19 at 17:48

score 1 · Answer 6 · answered Mar 08 '20 at 20:53

In current releases the "dbListConnections" function is deprecated and DBI no longer requires drivers to maintain a list of connections. As such, the above solutions may no longer work. E.g. in RMariaDB the above solutions create errors.

I made with the following alternative that uses the MySQL server's functionality and that should work with current DBI / driver versions:

### listing all open connection to a server with open connection
query <- dbSendQuery(mydb, "SHOW processlist;")
processlist <- dbFetch(query)
dbClearResult(query)

### getting the id of your current connection so that you don't close that one
query <- dbSendQuery(mydb, "SELECT CONNECTION_ID();")
current_id <- dbFetch(query)
dbClearResult(query)

### making a list with all other open processes by a particular set of users
# E.g. when you are working on Amazon Web Services you might not want to close 
# the "rdsadmin" connection to the AWS console. Here e.g. I choose only "admin" 
# connections that I opened myself. If you really want to kill all connections,
# just delete the "processlist$User == "admin" &" bit.
queries <- paste0("KILL ",processlist[processlist$User == "admin" & processlist$Id != current_id[1,1],"Id"],";")

### making function to kill connections
kill_connections <- function(x) {
  query <- dbSendQuery(mydb, x)
  dbClearResult(query)
}

### killing other connections
lapply(queries, kill_connections)

### killing current connection
dbDisconnect(mydb)

Closing active connections using RMySQL

6 Answers6

Linked