hibernate c3p0 connection breaks after mysql timeout

Question

i have a bizarre problem. i am using hibernate jpa with spring on tomcat and at the same time i need to reconnect the database when mysql times out.

c3p0 in jpa:

        <property name="hibernate.c3p0.acquireIncrement" value="100" />
        <property name="hibernate.c3p0.timeout" value="10" />
        <property name="hibernate.c3p0.idle_test_period" value="10" />
        <property name="hibernate.c3p0.max_size" value="1240"/>
        <property name="hibernate.c3p0.min_size" value="50"/>
        <property name="hibernate.c3p0.preferredTestQuery" value="select 1;"/>

would this help?::

        <property name="connection.autoReconnect" value="true"/>
        <property name="connection.autoReconnectForPools" value="true"/>
        <property name="connection.is-connection-validation-required" value="true"/>

some people suggest ?autoReconnect=true at the end of url, i dont like it and it did not work for me either.

anyone using jpa solved this problem?

ps: i also tried using <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/> and it didnt work.

someone please help, thanks in advance.

mindas · Accepted Answer · 2011-02-25 10:32:24Z

2

Reconnections in c3p0 are governed by these properties:

hibernate.c3p0.acquireRetryAttempts=...
hibernate.c3p0.acquireRetryDelay=...
hibernate.c3p0.breakAfterAcquireFailure=...

Have a look at this chapter in c3p0 manual for more details.

EDIT: updating with details as per question author's comment.

I use these settings on a real world life app which has two databases (one local/read-only and another remote/write-only) configured. These c3p0 settings are used for write-only db so if connection is lost, c3p0 would attempt to reconnect in the way I want. Otherwise, the defaults are (if I remember correctly) set so reconnect is done quite frequently and that sucks a lot of CPU power and it noticeably affects the frontend. I have nothing else to paste here unfortunately as these settings are not used in Tomcat context; I just have them in the old style .properties file.

I didn't get the part of your comment about "actual procedure". Reconnection is configured in the database pool settings. If you lose connection during live transaction, you will get an exception (most likely a subclass of SQLException or IOException) and if you know this is likely, you need to handle this yourself as no DB pool would know what do you want to do with it (retry? discard? postpone?).

edited Feb 25, 2011 at 10:32

answered Feb 24, 2011 at 21:39

mindas

26.8k15 gold badges103 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ikthiander Over a year ago

do you have any working example? the answer you posted only deals with the attempts of c3p0, not the actual procedure i guess. let me know if i am wrong by posting an answer. reading the documents to check other possibilities....

Collectives™ on Stack Overflow

hibernate c3p0 connection breaks after mysql timeout

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related