[Biojava-dev] Closing ResultSets and Statements in biosql pac kage.

[Simon Foote]

To add further to David's comment on the MySQL connection pool 
datasource.  I have modified my working copy of the BioSQL 
implementation to implement true connection pooling via the Jakarta 
commons dbcp & pool packages as I think David has also.  It also allows 
the use of any other pooling system of the user's choice.  I've tested 
it mainly with MySQL, a little with Postgresql and not at all with 
Oracle.  I have plans to commit the changes as long as there's no 
objections.

David: as for the connection closed problem with Apache pooling stuff.  
If you set the following datasource parameters, you should eliminate the 
dead connection problem.  It worked for me when I had similar problems, 
but took me awhile to find out what had to be set.

<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/database?autoReconnect=true</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>

Cheers,
Simon

ps. Let me know if any objections to adding the datasource / connection 
pooling stuff to CVS

David Huen wrote:

>On Thursday 31 Jul 2003 9:39 am, Keith James wrote:
>  
>
>>>>>>>"Len" == Len Trigg <len at reeltwo.com> writes:
>>>>>>>              
>>>>>>>
>>    Len> Guys, FYI - I just committed a fix to a bug where I was
>>    Len> getting the following exception operating on sequences stored
>>    Len> in Oracle:
>>
>>[...]
>>
>>Excellent! I had expressed concern about this before, but couldn't
>>produce a real-life example. Would it be possible for you to describe
>>how to recreate the exception so that we can make a regression test?
>>Do you think it is possible to cause the exception using a minimal set
>>of test data?
>>
>>I'm proposing the addition of a new test target 'biosqltests' which
>>would specifically exercise the O/R mapping, safety and performance of
>>the biosql code. This would probably use DBUnit to populate a test
>>database as it integrates nicely with Ant/JUnit (plus I've used it
>>before on Oracle and Postgres).
>>
>>    
>>
>I think it will be necessary to keep that jUnit test from being executed 
>with the standard tests - most installs won't have a suitable DB available 
>to run a BioSQL test suite.
>
>As for actually reliably triggering running out of Connections from failure 
>to close Connections, setting the number of Connections to the minimum 
>possible not to fubar on normal code and allocating lots of memory to delay 
>garbage collection of dead Connections would probably be best.  Even this 
>is not deterministic as some dead Connections do get finalized by the GC 
>and closed thereby.  Repeatedly invoking the test at high rate perhaps 
>would do it better.
>
>Incidentally, if using MySQL connection pool datasource, don't assume it is 
>pooling - it isn't as it only provides the framework for a pool 
>implementation supplied elsewhere.  And calling close() under these 
>circumstances does nothing of the sort.  That's how I managed to reliably 
>trigger out-of-connection errors on the Gadfly bridge.  Also, when using 
>the Apache connection pooling stuff with mySQL, I occasionally seem to get 
>a Connection from the pool that is closed already and it appears to be 
>necessary to get the Apache code to test the Connection before handing it 
>out - go figure....
>
>Regards,
>DH
>
>  
>

-- 
Bioinformatics Specialist
Institute for Biological Sciences
National Research Council of Canada
[T] 613-990-0561  [F] 613-952-9092
simon.foote at nrc-cnrc.gc.ca