Error on disconnect about databaseconnector

[jreps]

that makes sense, I think I started getting it when I updated RStudio!

[schuemie]

Hi @alondhe . I haven't seen this error message before. Does it also occur outside of SCCS?

[jreps]

I'm getting the same thing with PLP

[schuemie]

Hmmm, I'm not able to reproduce this error. What versions of DatabaseConnector and RStudio are you running?

[chrisknoll]

This might not be it (since @alondhe says that it's intermittent) but is it possible that there's a try-with-resources block surrounding some code that would cause the open connection to be closed after leaving the try{} block? Described here: https://stackoverflow.com/questions/42494178/java-sql-sqlexception-database-connection-closed

Edit: nevermind, that would only happen if you were writing java code with try{}...this is happening at the R-wrapper level. Sorry for the noise.

[schuemie]

Hi @chrisknoll , what my guess is, is that RStudio doesn't know the connection is closed. In the newer versions of RStudio there's a Connections tab that shows open connections, and disconnect lets RStudio know when a connection closes.

Maybe the connection is closed from the server side?

[chrisknoll]

It sounds like you're saying that the error isn't from the PLP/SCCS package at all, rather it's RStudio noticing a connection and then trying to get some meta on it?

[schuemie]

Yes, that is my guess. But I'm not sure why I'm not seeing it.

[alondhe]

Agreed @jreps. I'm running on version 1.1.383, which is right around the time the Connections panel was added. This panel refreshes connection metadata often, to coincide with active SQL connections. Must be a bug with it. There is a newer version, 1.1.453, I'll try to replicate with it.

[alondhe]

Apparently, you can use this to prevent the Connections tab from loading:
options(connectionObserver = NULL)

Is this something we'd want DatabaseConnector::connect to set, or just document?

[chrisknoll]

Good times....I need to re-install all my tools since I got this new lappy....should be fun working through all the new issues!

Thanks for the tip on turning that off, @alondhe .

[schuemie]

Noooo! I didn't spend all this time on setting up communication between DatabaseConnector and RStudio for people to just turn it off at the first sign of trouble.

I am now able to reproduce the error: It happens when you open multiple connections to the same server in the same R session. Then, if you close one, it somehow confuses which one gets closed. @alondhe , @jreps : would you be able to tell me whether you still had connections open before you kicked off PLP or SCCS?

In general it is best practice to close connections once you are done with them. If you do that, I'm pretty sure the problem does not occur. In the mean time I'll try to figure out a solution.

[schuemie]

I just committed a fix that allows you to have multiple connections to the same server open at once. Please use devtools::install_github("ohdsi/DatabaseConnector") to install it and let me know if this solves the problem.

In the mean time, keep an eye on the connections tab, and ask yourself whether you really need to have all those connections open ;-)

[chrisknoll]

In the mean time, keep an eye on the connections tab, and ask yourself whether you really need to have all those connections open ;-)

Always good advice, but on the topic of resource leaks, I am not sure that when PLE executes, everything is closed up completely...sometimes when I finish an 'execute()' and i want to delete results for a re-run, there is a file lock somewhere that I need to restart R to clear. I think the same thing might be happening with db connections where connections that are created during the process aren't closed afterwards (or possibly left open when an error occurs, so maybe some cleanup on error is needed).

Of course I don't have concrete data to back this up, but just pointing out something that might want to be looked for.

[schuemie]

@chrisknoll : for that reason I've started putting the disconnect call in an on.exit clause, like here. That way, the connection is also closed when the function is terminated prematurely due to an error or uses action.

ff has its own weird logic of when to give up file locks that has eluded me so far. But definitely something to look at as well.

[chrisknoll]

ff has its own weird logic of when to give up file locks that has eluded me so far. But definitely something to look at as well.

Now that you mention that, I believe it was those .ff files that was preventing me from deleting.