Hi Kevin
Thanks for the reply. This is a standard JEE application running in the
jetty application server. I am running one Servlet that recieves a
request, processes the request during which the application connects to
one of two databases and queries information. Then acts upon the
information and processes some more. After each transaction I close the
EntityManager.
I have two different databases, one which has information submitted from
the website and the other one that keeps track of the information
processed. Below is a copy of my persistence.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd";>
<persistence-unit name="AutoTrialPU" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<class>com.copperarrow.utils.autotrial.model.entities.SNSMessage</class>
<class>com.copperarrow.utils.autotrial.model.entities.VeiligCustomer</class>
<class>com.copperarrow.utils.autotrial.model.LicenseKey</class>
<class>com.copperarrow.utils.autotrial.model.Customer</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<shared-cache-mode>NONE</shared-cache-mode>
<properties>
<property name="javax.persistence.jdbc.url"
value="jdbc:mysql://localhost:3306/cass_autotrial"/>
<property name="javax.persistence.jdbc.password" value="xxxxxx"/>
<property name="javax.persistence.jdbc.driver"
value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.user" value="autotrial"/>
<property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(ForeignKeys=true)"/>
<property name="openjpa.Log" value="DefaultLevel=WARN,
Runtime=INFO, Tool=INFO, SQL=TRACE"/>
<property name="openjpa.jdbc.DBDictionary"
value="org.apache.openjpa.jdbc.sql.MySQLDictionary"/>
<property name="openjpa.ConnectionFactoryProperties"
value="autoReconnect=true"/>
<property name="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=80, PrintParameters=true"/>
</properties>
</persistence-unit>
<persistence-unit name="DrupalPU" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<shared-cache-mode>NONE</shared-cache-mode>
<properties>
<property name="javax.persistence.jdbc.url"
value="jdbc:mysql://localhost:3306/cass_drupal7"/>
<property name="javax.persistence.jdbc.password" value="xxxxxx"/>
<property name="javax.persistence.jdbc.driver"
value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.user" value="cadrupal-user"/>
<property name="openjpa.Log" value="DefaultLevel=WARN,
Runtime=INFO, Tool=INFO, SQL=TRACE"/>
<property name="openjpa.jdbc.DBDictionary"
value="org.apache.openjpa.jdbc.sql.MySQLDictionary"/>
<property name="openjpa.ConnectionFactoryProperties"
value="autoReconnect=true"/>
</properties>
</persistence-unit>
</persistence>
The DrupalPU just connects to the database and then I use a Criteria
Query to query the necessary data. The other one uses JPA persistent
classes to and simply queries and updates the database. An example
transaction processes is below:
EntityManager em = emf.createEntityManager();
try {
em.getTransaction().begin();
Query query = em.createNamedQuery("SNSMessage.findbyID");
query.setParameter("id", messageID);
List<SNSMessage> snsMessage = (List<SNSMessage>)
query.getResultList();
if (snsMessage != null && !snsMessage.isEmpty()) {
processed = snsMessage.get(0).isProcessed();
}
em.getTransaction().commit();
} catch (Exception ex) {
Logger.getLogger(getClass().getName()).log(Level.SEVERE,
"Problem executin query find by id", ex);
em.getTransaction().rollback();
} finally {
em.close();
}
Thanks
David
On 04/01/13 19:13, Kevin Sutter wrote:
Hi David,
This sounds strange or, at least, unique. Normal, default processing
by OpenJPA is to only get a database connection when it's needed and
then release it as soon as possible (flush or commit). There are
other options [1] available to extend the life of the connection to
either the transaction or until the EM closes, but you would have to
set that explicitly. Are you using either of these?
Or, is there some other processing in your application that is
accidentally holding onto the connection? Via OpenJPA APIs, there is
a means of obtaining the connection object, but again that's not
normal processing. Normally, apps would just like OpenJPA handle the
connection management processing. One of the benefits of JPA...
Are you using OpenJPA in a JSE environment, or as part of an
application server? Just wondering if there's something else holding
onto connections outside of OpenJPA's control. Like some connection
management or connection pooling utility? By default, OpenJPA will
use DBCP for connection pooling in the JSE environment [2]. There
have been some issues relating to DBCP and stale connections in the
pool, but I thought those were cleared up in the past... You could
try disabling the DBCP support and see if that resolve it as a quick test.
Bottom line is that I think OpenJPA is just the messenger in this
case. Some connection was given to OpenJPA that was stale and we just
reported the error. Hopefully, this note gives you a few areas to
check on. Let us know what you find out.
Kevin
[1]
http://openjpa.apache.org/builds/latest/docs/docbook/manual.html#ref_guide_dbsetup_retain
[2]
http://openjpa.apache.org/builds/latest/docs/docbook/manual.html#ref_guide_integration_dbcp
On Fri, Jan 4, 2013 at 6:39 AM, David Beer
<david.m.b...@googlemail.com <mailto:david.m.b...@googlemail.com>> wrote:
Hi All
I periodically get an issue where the connection to my MySQL
Database timesout. I have set the value <property
name="openjpa.ConnectionFactoryProperties"
value="autoReconnect=true"/> in the persistence.xml file and this
works most of the time but if there is a long time between the
connection like a few days it timesout. Is this a case of
increasing the timeout value in MySQL or is this a setting in
OpenJpa I need to change?
My stack trace is as follows:
02-Jan-2013 01:44:27
com.copperarrow.utils.autotrial.SNSRecieverServlet messageProcessed
SEVERE: Problem executin query find by id
<openjpa-2.2.0-r422266:1244990 fatal general error>
org.apache.openjpa.persistence.PersistenceException: The last
packet successfully received from the server was 187,404,662
milliseconds ago. The last packet sent successfully to the server
was 187,404,662 milliseconds ago. is longer than the server
configured value of 'wait_timeout'. You should consider either
expiring and/or testing connection validity before use in your
application, increasing the server configured values for client
timeouts, or using the Connector/J connection property
'autoReconnect=true' to avoid this problem.
at
org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4918)
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4878)
My MySQL Settings are as follows for default timeout.
mysql> SHOW GLOBAL VARIABLES LIKE "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
Thanks
David
