manifoldcf-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rüdiger Kurz <k...@vnc-online.de>
Subject Re: Health Check of database connections
Date Fri, 22 May 2015 20:37:44 GMT
Hi,

for application management reason I can not switch to PostgreSQL (If I 
understood you right, you have done the shutdown in the middle of a 
crawl manually to test the reconnect capability of PostgreSQL). I have 
to stay with MySQL/MariaDB and I need support the ability of 
reconnection if the database was down.

If you prefer refreshing the jdbc connection, that would be fine as 
well. Reflecting the stackoverflow link [2] from your initial post, I 
just can add that there is Connection#isValid(int) method since Java 6. 
Not quite sure, but maybe this can do the trick for a generic jdbc 
reconnect implementation.

I'm not really familiar with the individual implementation of the Mcf 
connection pool. So it is more easy for me to replace the complete 
pooling mechanism with a high performance library [1], instead dealing 
with refreshing the jdbc connection deep inside.

 From my experience I can say that many problems with DB connections 
disappear when using connection pools like: C3P0, Tomcat, HikariCP or 
BoneCP, ...

HikariCP is new to me as well, I just stumbled over it when struggling 
with this issue. According to its documentation [4] HikariCP can deal 
with Apache Derby, Firebird, IBM DB2, H2, HSQLDB, MariaDB & MySQL 
(MariaDB), MySQL (Connector/J), MS SQL Server, Oracle, PostgreSQL 
(pgjdbc-ng), PostgreSQL (PostgreSQL), SyBase. Beyond and independent 
this project seems to be really interesting.

-Rüdiger

[1] https://github.com/brettwooldridge/HikariCP/wiki/Down-the-Rabbit-Hole

[2] 
http://stackoverflow.com/questions/14320231/is-there-a-safe-way-to-tell-if-a-jdbc-connection-is-still-ok

[3] 
http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#isValid(int)

[4] 
https://github.com/brettwooldridge/HikariCP#popular-datasource-class-names


Am 22.05.2015 um 21:42 schrieb Karl Wright:
> Hi,
>
> I am out of town until Monday.
>
> However, can you describe what this integration is supposed to
> accomplish? Mcf has a connection pool already that meets its needs.
>
> If there is a way in jdbc to refresh a generic jdbc connection, I am
> happy to integrate that. So far I haven't found any generic way to do
> it though.
>
> As for tests, you are correct that our test is intended to exercise the
> functionality in mcf, not in the jdbc drivers.
>
> Karl
>
> From: Rüdiger Kurz
> Sent: 5/22/2015 1:47 PM
> To: user@manifoldcf.apache.org
> Subject: Re: Health Check of database connections
> Hi Karl,
>
> I've prototyped the integration of the HikariCP JDBC connection pool
> [1]. For details you can have a look at the attached file
> ConnectionFactory.java.
>
> There are two additional dependencies needed in order to get this run:
>
>>    <dependency>
>>        <groupId>com.zaxxer</groupId>
>>        <artifactId>HikariCP-java6</artifactId>
>>        <version>2.3.8</version>
>>        <scope>compile</scope>
>>    </dependency>
>>    <dependency>
>>        <groupId>org.javassist</groupId>
>>        <artifactId>javassist</artifactId>
>>        <version>3.18.1-GA</version>
>>        <scope>runtime</scope>
>>    </dependency>
>
> To get this well done, I would like to change the initialization of the
> data source into the following style:
>
>>    HikariConfig config = new HikariConfig("some/path/hikari.properties");
>>    HikariDataSource ds = new HikariDataSource(config);
>
> At least I would like to make sure this core changes do work stable. Do
> you see any gaps or issues with this approach? Which test essential
> cases do I need to run?
>
> BTW: I think the test case RSSFlakyHSQLDBIT switches between an
> "exception"-mode and a "non-exception"-mode, but does not turn off the
> database and restarting it. So all the connections within the pool will
> be alive anyways. Very likely I've missed something important, so please
> correct me, if my assumption is wrong. I'm new to ManifoldCF ;-)
>
> -Rüdiger
>
> [1] https://github.com/brettwooldridge/HikariCP
>
>
> On Thu, May 21, 2015 at 1:47 PM, Karl Wright <daddywri@gmail.com> wrote:
>
>> I should also mention that I've done *precisely* the test you
>> describe against PostgreSQL -- shutting the database down in the
>> middle of a crawl, and restarting it.  Everything works fine in that
>> case -- the PostgreSQL JDBC driver seems quite able to reestablish
>> connections that have gone stale.  It's just MySQL that doesn't do it
>> properly, apparently.
>>
>> Karl
>>
>>
>> On Thu, May 21, 2015 at 6:49 AM, Karl Wright <daddywri@gmail.com>
>> wrote:
>>
>>> The test is:
>>>
>>> RSSFlakyHSQLDBIT
>>>
>>> It's one of the RSS connector tests, and simulates database
>>> interruption by way of a replacing the HSQLDB database instance with
>>> one that generates database errors when told to.
>>>
>>> Karl
>>>
>>>
>>> On Thu, May 21, 2015 at 6:02 AM, <Ruediger.Kurz@deutschebahn.com>
>>> wrote:
>>>
>>>> Hi Karl,
>>>>
>>>> could you please point us to the test case class, that checks the
>>>> reconnect capabilty?
>>>>
>>>> Thanks in advance!
>>>> -Rüdiger
 >>>>
 >>>>
 >>>> On Thu, May 20, 2015 at 01:29 PM, Karl Wright <daddywri@gmail.com>
 >>>> wrote:
>>>>
 >>>>> Hi Markus,
>>>>>
>>>>> This is not a general problem with ManifoldCF, because we even have tests
 >>>>> that exercise this functionality.  Probably the issue is that some
>>>>> JDBC drivers are more resilient than others.  I have not researched what
the
 >>>>> MySQL driver does in this case, but I wouldn't be surprised if the
 >>>>> pooled JDBC connections for MySQL were not resurrectable after
 >>>>> that sequence of activity.
>>>>>
 >>>>> If there is a general JDBC method we could call to verify that
 >>>>> the connection is alive or dead, it would be easy to discard
 >>>>> the dead ones and recreate them.  But this indicates that no
 >>>>> such general functionality yet exists:
>>>>>
>>>>> http://stackoverflow.com/questions/14320231/is-there-a-safe-way-to-tell-if-a-jdbc-connection-is-still-ok
>>>>>
>>>>> Karl
>>>>>
>>>>>
>>>>> On Wed, May 20, 2015 at 9:05 AM, Markus Schuch <markus_schuch@web.de>
wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>>  it seems that manifoldcf can not re-establish the database connection,
>>>>>>  when the database connection was lost.
>>>>>>  For example on MySQL manifoldcf stops working when we shutdown and
start
>>>>>>  the database the database.
>>>>>>
>>>>>>  "Database exception: SQLException doing query (08): Could not send
query:
>>>>>>  Last packet not finished"
>>>>>>
>>>>>>  Only a restart of manifoldcf fixes that error.
>>>>>>
>>>>>>  Is it possible to configure manifoldcf to be more resillient on
database
>>>>>>  connection losses?
>>>>>>  E.g. doing a connection health check in the connection pool before
giving
>>>>>>  connections away?
>>>>>>
>>>>>>  Thanks in advance,
>>>>>>  Markus

Mime
View raw message