0

I cannot paste the entire script here, but I am explaining the situation. If you have ever got leaked DB connections then you would be knowing what I am talking about.

I have an R script file that has many functions (around 50) that use db connections using the DBI & RMySQL R packages. I have consolidated all DB access through 4 or 5 functions. I use on.exit(dbDisconnect(db)) in every single function where a dbConnect is used.

I discovered that just on loading this script using source("dbscripts.R") causes one DB connection to leak. I see this when I run the command

dbListConnections(MySQL())

[[1]] MySQLConnection:0,607>

[[2]] MySQLConnection:0,608>

[[3]] MySQLConnection:0,609>

[[4]] MySQLConnection:0,610>

I see one more DB connection added to the list everytime. This quickly reaches to 16 and my script stops working.

The problem is, I am unable to find out which line of code is causing the leak. I have checked each dbConnect line in the code. All of them are within functions and no dbConnect happens outside in the main code.

So, why is the connection leak occurring?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Lazarus Thurston
  • 1,197
  • 15
  • 33
  • it seems like you've created a connection that you haven't closed. I don't know of anyway to find it other than making a list of all of the connections you've made and matching them against all of your `on.exit`s. I feel your pain. I had a similar problem this week. took me an hour to find the one I missed. – Benjamin Sep 16 '17 at 12:40
  • I am intrigued how even without running any function could any `dbConnect` run. I can understand if any `DbConnect` line was sitting outside in the main body of the code. – Lazarus Thurston Sep 16 '17 at 12:45
  • Do you ever use `on.exit` for something else? The default mode of `on.exit` is quizzically to over-write the list of functions; if yes, then using `on.exit(..., add=TRUE)` should help. – r2evans Sep 16 '17 at 14:06
  • Short of that, unfortunately this question is completely unreproducible. I'm gathering that it's a not-small file, so posting it would be cluttering (if even possible, if private). If there is literally no code outside of your function declaration blocks, then it is really a mystery that `source`ing alone would cause this. I often have ancillary code-blocks (examples, backup code) within `if (FALSE) { ... }` blocks to prevent this kind of mistake (while keeping the code available for manual sourcing), but this is not infallible. – r2evans Sep 16 '17 at 14:10
  • How do I make a list of connections @Benjamin? Because I am not opening any connection in the main body of the R script? All of the `dbConnect` lines are inside function bodies and I am not yet executing any function. – Lazarus Thurston Sep 16 '17 at 16:24
  • 1
    I have a very 'high tech' process of opening each file and doing a search for `dbConnect`, and writing down each object I find. then I search for `dbDisconnect` and cross out every object I've closed. I also look for instances for `dbConnect` that I haven't assigned to an object. I approach the entire process with the assumption that I've done something I didn't intend to do. – Benjamin Sep 16 '17 at 16:32
  • Anyone else wanting to suggest a method. I can't find any leaks manually. – Lazarus Thurston Sep 22 '17 at 08:11

0 Answers0