Re: [Opaltoolkit-users] Hibernate database connection

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
 <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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): 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }else if (dialect.equals("org.hibernate.dialect.MySQLDialect")){ 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //this is MySQL 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; query = "select jobInfo.start_time_date as date, " + 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //number of day 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " avg( ( last_update_date - start_time_date ) * 86400 +
" + 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //plus number of seconds (epoch returns seconds!) 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " ( time_to_sec(last_update_time) -
time_to_sec(start_time_time) )) as average " + queryTail; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } 
 
Feel free to include this in next release if it can help other users. 
Thanks. 
Anthony 
 
Anthony Bretaudeau a &eacute;crit&nbsp;:
<blockquote cite="mid:4A9...@ir..." type="cite">
 <pre wrap="">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 &eacute;crit :
  </pre>
  <blockquote type="cite">
    <pre wrap="">Anthony Bretaudeau wrote:
    </pre>
    <blockquote type="cite">
      <pre wrap="">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: 
<a class="moz-txt-link-freetext" href="http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/">http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/</a>. 
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:

      </pre>
    </blockquote>
    <pre wrap="">
Anthony,
I expect the dashboard to have some problem visualizing the exec time 
of your jobs.

Have a look at line 365:
<a class="moz-txt-link-freetext" href="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">http://opaltoolkit.svn.sourceforge.net/viewvc/opaltoolkit/tags/opal2-core-2.0.0/src/edu/sdsc/nbcr/opal/dashboard/persistence/DBManager.java?revision=381&amp;view=markup</a> 

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

    </pre>
    <blockquote type="cite">
      <pre wrap="">-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 &lt;include name="OpalState.hbm.xml"/&gt; added 
&lt;include name="c3p0.properties"/&gt;
-In $OPAL_SOURCES/etc/hibernate-opal.cfg.xml, replaced:

        &lt;!-- JDBC connection pool (use the built-in) --&gt;
        &lt;!--&lt;property name="connection.pool_size"&gt;1&lt;/property&gt;--&gt;

By these lines:

        &lt;!-- JDBC connection pool (use c3p0) --&gt;
        &lt;property name="c3p0.min_size"&gt;1&lt;/property&gt;
        &lt;property name="c3p0.max_size"&gt;20&lt;/property&gt;
        &lt;property name="c3p0.timeout"&gt;1800&lt;/property&gt;
        &lt;property name="c3p0.max_statements"&gt;50&lt;/property&gt;
        &lt;property name="c3p0.idle_test_period"&gt;1800&lt;/property&gt;
        &lt;property 
name="connection.provider_class"&gt;org.hibernate.connection.C3P0ConnectionProvider&lt;/property&gt; 

Maybe it would be a good idea to include it in a future version of opal?
Bye!

      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->
------------------------------------------------------------------------------
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.  <a class="moz-txt-link-freetext" href="http://p.sf.net/sfu/bobj-july">http://p.sf.net/sfu/bobj-july</a>
_______________________________________________
Opaltoolkit-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Opa...@li...">Opa...@li...</a>
<a class="moz-txt-link-freetext" href="https://lists.sourceforge.net/lists/listinfo/opaltoolkit-users">https://lists.sourceforge.net/lists/listinfo/opaltoolkit-users</a>
  </pre>
</blockquote>
</body>
</html>