Re: [Opaltoolkit-users] Hibernate database connection

[Luca C. <lcl...@uc...>]

Anthony Bretaudeau wrote:
> Hi!
> We've had a problem using MySQL database for storing opal2 jobs data 
> instead of the default HSQLDB.
> It turned out it was a timeout problem which is quite well described at 
> the following address: 
> http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/. 
> Briefly: mysql closes connection after an 8h timeout (which is quite 
> frequent for us, but not for servers where there is more activity).
> We solved it by using c3p0 to manage database connection.
> Here is what we have done to use it:
> 


Anthony,
I expect the dashboard to have some problem visualizing the exec time of 
your jobs.

Have a look at line 365:
http://opaltoolkit.svn.sourceforge.net/viewvc/opaltoolkit/tags/opal2-core-2.0.0/src/edu/sdsc/nbcr/opal/dashboard/persistence/DBManager.java?revision=381&view=markup

Unfortunately I could not find a way to make the query working using 
Hibernate SQL and I had to use the local SQL dialect.

Given that I think we should start using some connection manager since 
it is also recommended by the Hibernate guys.

Sincerely,
Luca

> -Added c3p0 package in $opal_sources/lib/ and modified build.xml 
> consequently
> -Created $opal_sources /etc/c3p0.properties and added " 
> c3p0.preferredTestQuery=SELECT 1; " in it.
> -In build.xml, after <include name="OpalState.hbm.xml"/> added <include 
> name="c3p0.properties"/>
> -In $OPAL_SOURCES/etc/hibernate-opal.cfg.xml, replaced:
> 
>         <!-- JDBC connection pool (use the built-in) -->
>         <!--<property name="connection.pool_size">1</property>-->
> 
> By these lines:
> 
>         <!-- JDBC connection pool (use c3p0) -->
>         <property name="c3p0.min_size">1</property>
>         <property name="c3p0.max_size">20</property>
>         <property name="c3p0.timeout">1800</property>
>         <property name="c3p0.max_statements">50</property>
>         <property name="c3p0.idle_test_period">1800</property>
>         <property 
> name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
> 
> Maybe it would be a good idea to include it in a future version of opal?
> Bye!
>