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.b...@gmail.com
<mailto:david.m.b...@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.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
