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

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

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

Method 3

Simplest:

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

List all connections and disconnect them by lapply

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

Method 5

lapply(dbListConnections(MySQL()), dbDisconnect)

Method 6

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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating