RMySQL: Closing a connection without a handle

Question

I am experimenting with RMySQL, and have accidentally created a connection without a handle.

dbConnect(MySQL(), user = "foo", password = "bar")
connLocalDB = dbConnect(MySQL(), user = "foo", password = "bar")

Note that the return of the first call is not assigned to anything. Now, when I do a dbListConnections(MySQL()) I see two connections:

> dbListConnections(MySQL())
[[1]]
<MySQLConnection:0,0>

[[2]]
<MySQLConnection:0,1>

I then tried this:

> dbDisconnect(dbListConnections(MySQL())[[1]])
[1] TRUE

but, then, I got this:

> dbListConnections(MySQL())
[[1]]
Error in .local(dbObj, ...) : 
  internal error in RS_DBI_getConnection: corrupt connection handle

How to safely terminate a connection that is not assigned a handle?

Pitarys

You won't need to worry about this in the next version (i.e. the version on github) – Emmalynn

@Emmalynn Strangest thing. Just updated, and it now gives me > dbListConnections(MySQL()) # list the open connections Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbListConnections’ for signature ‘"MySQLDriver"’ – Pitarys

@Emmalynn All kinds of other errors popping up, including unable to perform passwordless login using a configuration file in the home directory. – Pitarys

@Emmalynn Following up to see if you can help diagnose these errors with the dev version of the package. – Pitarys

File bugs at github please. dbListConnections() is no longer implemented because it's not needed. – Emmalynn

@Emmalynn Thanks. The dev version makes too many breaking changes, and I think that I will revert to the version on CRAN for now. I assume there isn't a way to handle this (pun not intended) within that version? – Pitarys

Well it would be useful to here what those breaking changes are... – Emmalynn

@Emmalynn I will happily oblige on the RMySQL & DBI issues page. Mind you, I consider API changes without prior deprecation warnings breaking changes. – Pitarys

Answer 1

Generally for creating a connection, getting the data of a query and then closing the connection I use the following function:

getDataSql <- function( query ) {
  con = dbConnect(RMySQL::MySQL(), dbname  =  "dbname", host = "host", user = "username", password = "pasword", port = "port")
  result <- dbGetQuery(con, query)
  dbDisconnect(con)
  result
}

Tichonn