Re: [Opaltoolkit-users] Hibernate database connection

[Anthony B. <ant...@ir...>]

Oh yes, the exec time has a problem. I hadn't noticed that.
Is this the only SQL request using local SQL dialect? In this case, 
something like "unix_timestamp( last_update_time - start_time_time ) as 
average" should work for MySQL (copying the rest of the request from 
PostgreSQL). If I have time, I'll try to test it.
Thanks
Anthony

Luca Clementi a écrit :
> 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!
>>
>