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 Sun, 06 Jan 2013 18:21:23 GMT
Hi Kevin No to Worry after a bit more research and reading both the 
OpenJPA and DBCP configuration options. I now have the following 
persistence.xml file which is now providing a connection according to my 
tests. I will put the changes live tonight and see how this behaves, 
thanks for your help and suggestions.

<properties>
       <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.ConnectionDriverName" 
value="org.apache.commons.dbcp.BasicDataSource"/>
       <property name="openjpa.ConnectionProperties" 
value="DriverClassName=com.mysql.jdbc.Driver,
        Url=${test.jdbc.url}, Username=autotrial, 
Password=cass_autotrial,MaxActive=10,MaxIdle=5,MinIdle=2,MaxWait=60000"/>
       <property name="openjpa.ConnectionFactoryProperties" 
value="PrettyPrint=true, PrettyPrintLineLength=80, PrintParameters=true"/>
     </properties>

Thanks David

On 05/01/13 13:57, David Beer wrote:
> Hi Kevin
>
> Thanks for the suggestions. I am trying to add DBCP to help with the 
> connection pooling, but keep getting and error No Suitable Driver 
> found. Here is my persistence.xml properties section.
>
> <properties>
>       <property name="javax.persistence.jdbc.driver" 
> value="org.apache.commons.dbcp.BasicDataSource"/>
>       <property name="javax.persistence.jdbc.url" 
> value="${test.jdbc.url}"/>
>       <property name="javax.persistence.jdbc.password" 
> value="cass_autotrial"/>
>       <!--<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.ConnectionProperties" 
> value="DriverClassName=com.mysql.jdbc.Driver"/>
>       <property name="openjpa.ConnectionProperties" 
> value="MaxActive=5,MaxIdle=2,MinIdle=1,MaxWait=60000"/>
>       <!--<property name="openjpa.ConnectionFactoryProperties" 
> value="autoReconnect=true"/>-->
>       <property name="openjpa.ConnectionFactoryProperties" 
> value="PrettyPrint=true, PrettyPrintLineLength=80, PrintParameters=true"/>
> </properties>
>
> Thanks
>
> David
>
> On 04/01/13 23:10, Kevin Sutter wrote:
>> Thanks for the information, David.  It looks like you are doing 
>> nothing out of the ordinary.  Since it looks like MySQL is managing 
>> the connections, OpenJPA is at their mercy. OpenJPA does not perform 
>> any retry logic on normal interactions with the database.  After we 
>> get a connection, we'll try the requested operation and expect it to 
>> work.  If it doesn't work, we report the error.  Any type of retry 
>> logic is left up to the application or the connection manager.
>>
>> I have a couple of suggestions...  You could try the DBCP connection 
>> pooling via my previous reply.  Based on your configuration, you 
>> should be able to configure this by specifying the following property.
>>
>> <property name="javax.persistence.jdbc.driver" 
>> value="org.apache.commons.dbcp.BasicDataSource"/>
>>
>> You might have to eventually adjust other parameters for this 
>> connection pool (max size, wait times, etc).  The use of a Connection 
>> Pool such as DBCP provides performance benefits as well (vs relying 
>> on the database mechanism for doling out connections).
>>
>> I'm not a Jetty expert, but it seems that Jetty relies on DBCP as 
>> well.  So, this would be consistent usage pattern.
>>
>> The other thing you could try is to increase your wait_timeout to 
>> something larger than your expected down time.  For example, if the 
>> expected lag time between requests could be up to a week, set your 
>> wait_timeout to one month.  MySQL lets you set this wait_timeout up 
>> to one year in length.  Although extreme, I think this would get you 
>> around your immediate problem.
>>
>> I am also questioning whether the autoReconnect is getting processed 
>> as expected.  Besides the MySQL documentation hinting that this 
>> setting should not be relied on, I'm wondering whether it's even 
>> getting set on the connection.  Have you verified this via a SQL or 
>> MySQL trace?  My suspicions are due to the mixing of the 
>> javax.persistence.* properties and the corresponding 
>> openjpa.Connection* properties and whether they are meshing 
>> correctly.  Hopefully, they are, but it's something that popped out 
>> at me while looking at your configuration.
>>
>> To force this property, you could set it directly on your url:
>>
>> <property name="javax.persistence.jdbc.url" 
>> value="jdbc:mysql://localhost:3306/cass_autotrial?autoReconnect=true"/>
>>
>> Hope this helps!
>> Kevin
>>
>> On Fri, Jan 4, 2013 at 3:57 PM, David Beer <david.m.beer@gmail.com 
>> <mailto:david.m.beer@gmail.com>> wrote:
>>
>>     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"
>>     <http://java.sun.com/xml/ns/persistence>
>>     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>     <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"
>>     <http://java.sun.com/xml/ns/persistencehttp://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