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
>>>
>>>
>>>
>>
>>
>
|