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: [email protected]
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 <[email protected]> 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 <[email protected]>
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, <[email protected]>
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 <[email protected]>
>>>> 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 <[email protected]> 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
