openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Beer <david.m.b...@gmail.com>
Subject Re: MySQL Timeout issue
Date Fri, 04 Jan 2013 21:57:14 GMT
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.beer@googlemail.com <mailto:david.m.beer@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
>
>
>


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message