openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Sutter <kwsut...@gmail.com>
Subject Re: MySQL Timeout issue
Date Fri, 04 Jan 2013 23:10:31 GMT
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> 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>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