Re: [Opaltoolkit-users] Hibernate database connection

[Sriram K. <sr...@sd...>]

Hello Anthony,

I have committed your changes into SVN, and also added configuration  
and jars for c3p0 and mysql. I tested everything and it seems to be  
working fine. Please check out the latest version from SVN and let me  
know if there are any issues.

This will be part of the next Opal release that we are planning for  
end of this month. Thanks again for your contribution!

Cheers,
Sriram

On Sep 2, 2009, at 5:28 AM, Anthony Bretaudeau wrote:

> Hi!
> After testing, I've managed to make job exec time visualization work  
> with the following code (replace line 389 with it in src/edu/sdsc/ 
> nbcr/opal/dashboard/persistence/DBManager.java):
>
>         }else if  
> (dialect.equals("org.hibernate.dialect.MySQLDialect")){
>             //this is MySQL
>             query = "select jobInfo.start_time_date as date, " +
>                 //number of day
>                 " avg( ( last_update_date - start_time_date ) *  
> 86400 + " +
>                 //plus number of seconds (epoch returns seconds!)
>                 " ( time_to_sec(last_update_time) -  
> time_to_sec(start_time_time) )) as average " + queryTail;
>         }
>
> Feel free to include this in next release if it can help other users.
> Thanks.
> Anthony
>
> Anthony Bretaudeau a écrit :
>>
>> 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!
>>>>
>>>>
>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports  
>> 2008 30-Day
>> trial. Simplify your report design, integration and deployment -  
>> and focus on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Opaltoolkit-users mailing list
>> Opa...@li...
>> https://lists.sourceforge.net/lists/listinfo/opaltoolkit-users
>>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008  
> 30-Day
> trial. Simplify your report design, integration and deployment - and  
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july_______________________________________________
> Opaltoolkit-users mailing list
> Opa...@li...
> https://lists.sourceforge.net/lists/listinfo/opaltoolkit-users