[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>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi!<br>
We've had a problem using MySQL database for storing opal2 jobs data
instead of the default HSQLDB.<br>
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).<br>
We solved it by using c3p0 to manage database connection.<br>
Here is what we have done to use it:<br>
<br>
-Added c3p0 package in $opal_sources/lib/ and modified build.xml
consequently<br>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	--></style>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	--></style>-Created
$opal_sources /etc/c3p0.properties
and added " c3p0.preferredTestQuery=SELECT
1;
" in it.<br>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	--&gt;</style>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	--></style>-In
build.xml, after &lt;include
name="OpalState.hbm.xml"/&gt; added &lt;include
name="c3p0.properties"/&gt;<br>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	-</style>-In
$OPAL_SOURCES/etc/hibernate-opal.cfg.xml, replaced:<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;!-- JDBC
connection pool (use the built-in) --&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;!--&lt;property
name="connection.pool_size"&gt;1&lt;/property&gt;--&gt;
<br>
<br>
By these lines:<br>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.0  (Linux)">
<style type="text/css">
	<!--
		@page { margin: 2cm }
		P { margin-bottom: 0.21cm }
	-cd</style><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;!-- JDBC connection pool (use c3p0) --&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name="c3p0.min_size"&gt;1&lt;/property&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name="c3p0.max_size"&gt;20&lt;/property&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name="c3p0.timeout"&gt;1800&lt;/property&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name="c3p0.max_statements"&gt;50&lt;/property&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property
name="c3p0.idle_test_period"&gt;1800&lt;/property&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property
name="connection.provider_class"&gt;org.hibernate.connection.C3P0ConnectionProvider&lt;/property&gt;<br>
<br>
Maybe it would be a good idea to include it in a future version of opal?<br>
Bye!<br>
<br>
</body>
</html>