You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by David Beer <da...@googlemail.com> on 2013/01/04 13:39:56 UTC
MySQL Timeout issue
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
Re: MySQL Timeout issue
Posted by David Beer <da...@gmail.com>.
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
>> <ma...@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
>>> <ma...@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
>>>
>>>
>>>
>>
>>
>
Re: MySQL Timeout issue
Posted by David Beer <da...@gmail.com>.
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
> <ma...@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
>> <ma...@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
>>
>>
>>
>
>
Re: MySQL Timeout issue
Posted by Kevin Sutter <kw...@gmail.com>.
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 <da...@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 <da...@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
>>
>>
>>
>
>
Re: MySQL Timeout issue
Posted by David Beer <da...@gmail.com>.
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 <ma...@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
>
>
>
Re: MySQL Timeout issue
Posted by Mikael Kurula <al...@gmail.com>.
Hi!
Just a short comment. It's not unique in the sense that I also had at least one instance of the same problem a few weeks ago.
Mikael
On 2013.4.1, at 21: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 <da...@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
>>
>>
>>
Re: MySQL Timeout issue
Posted by Kevin Sutter <kw...@gmail.com>.
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 <da...@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
>
>
>