From users-return-10540-apmail-openjpa-users-archive=openjpa.apache.org@openjpa.apache.org Sun Jan 6 18:21:59 2013 Return-Path: X-Original-To: apmail-openjpa-users-archive@minotaur.apache.org Delivered-To: apmail-openjpa-users-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9F21CE0DD for ; Sun, 6 Jan 2013 18:21:59 +0000 (UTC) Received: (qmail 43320 invoked by uid 500); 6 Jan 2013 18:21:58 -0000 Delivered-To: apmail-openjpa-users-archive@openjpa.apache.org Received: (qmail 43248 invoked by uid 500); 6 Jan 2013 18:21:58 -0000 Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: users@openjpa.apache.org Delivered-To: mailing list users@openjpa.apache.org Received: (qmail 43232 invoked by uid 99); 6 Jan 2013 18:21:57 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Jan 2013 18:21:57 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of david.m.beer@gmail.com designates 209.85.215.179 as permitted sender) Received: from [209.85.215.179] (HELO mail-ea0-f179.google.com) (209.85.215.179) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 06 Jan 2013 18:21:51 +0000 Received: by mail-ea0-f179.google.com with SMTP id i12so7385146eaa.38 for ; Sun, 06 Jan 2013 10:21:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:message-id:date:from:reply-to:user-agent:mime-version:to :cc:subject:references:in-reply-to:content-type; bh=/eD5OpiXbAUkUOO+ITpEguuOa4gPp0vY8UwAXkldOs0=; b=r2uPDvRWMelDWHKq+2Oo5+LrZ/PDvYS2F4TMt1wxbdWai7ETSoUeHDENmktYWmZa0W Xb31+ehOlmo08fDaaJQy2CK/AmnJqzlB3KhqRgXS/WFEgBaUupFGyRbOC5nNNWvVs4O9 hoRHgEn81HZtR0KA7aiCmCpkYn6iHyeUNQS05ERcUalqXMAHUH9yFYvV3CeE6pR6AwlX C6A+UVZqoA8W+jHg9K82fdWG0p7Fd9tjtIdoxJwKeQyCu9to63CGzkjz/P7/ZiX210ox HZnr0N8poWrAlAlLcg6/S6wusrlM/AL6VjMLbVzzBe7Y+u8U2Gng7CKGJmQe0JfvW6Yg /+uA== X-Received: by 10.14.177.1 with SMTP id c1mr160284945eem.8.1357496489642; Sun, 06 Jan 2013 10:21:29 -0800 (PST) Received: from [192.168.1.8] (client-81-107-134-122.midd.adsl.virginmedia.com. [81.107.134.122]) by mx.google.com with ESMTPS id f6sm125843009eeo.7.2013.01.06.10.21.25 (version=SSLv3 cipher=OTHER); Sun, 06 Jan 2013 10:21:28 -0800 (PST) Message-ID: <50E9C0A3.4050203@gmail.com> Date: Sun, 06 Jan 2013 18:21:23 +0000 From: David Beer Reply-To: david.m.beer@gmail.com User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/17.0 Thunderbird/17.0 MIME-Version: 1.0 To: users@openjpa.apache.org CC: Kevin Sutter Subject: Re: MySQL Timeout issue References: <50E6CD9C.6060904@googlemail.com> <50E7503A.5070007@gmail.com> <50E83135.5070005@gmail.com> In-Reply-To: <50E83135.5070005@gmail.com> Content-Type: multipart/alternative; boundary="------------060300040709090405010101" X-Virus-Checked: Checked by ClamAV on apache.org --------------060300040709090405010101 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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. 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. > > > value="org.apache.commons.dbcp.BasicDataSource"/> > value="${test.jdbc.url}"/> > value="cass_autotrial"/> > > > value="buildSchema(ForeignKeys=true)"/> > > value="org.apache.openjpa.jdbc.sql.MySQLDictionary"/> > value="DriverClassName=com.mysql.jdbc.Driver"/> > value="MaxActive=5,MaxIdle=2,MinIdle=1,MaxWait=60000"/> > > value="PrettyPrint=true, PrettyPrintLineLength=80, PrintParameters=true"/> > > > 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. >> >> > 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: >> >> > value="jdbc:mysql://localhost:3306/cass_autotrial?autoReconnect=true"/> >> >> Hope this helps! >> Kevin >> >> On Fri, Jan 4, 2013 at 3:57 PM, David Beer > > 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: >> >> >> > 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" >> > >> > transaction-type="RESOURCE_LOCAL"> >> org.apache.openjpa.persistence.PersistenceProviderImpl >> com.copperarrow.utils.autotrial.model.entities.SNSMessage >> com.copperarrow.utils.autotrial.model.entities.VeiligCustomer >> com.copperarrow.utils.autotrial.model.LicenseKey >> com.copperarrow.utils.autotrial.model.Customer >> true >> NONE >> >> > value="jdbc:mysql://localhost:3306/cass_autotrial"/> >> > value="xxxxxx"/> >> > value="com.mysql.jdbc.Driver"/> >> > value="autotrial"/> >> > value="buildSchema(ForeignKeys=true)"/> >> >> > value="org.apache.openjpa.jdbc.sql.MySQLDictionary"/> >> >> > value="autoReconnect=true"/> >> > value="PrettyPrint=true, PrettyPrintLineLength=80, >> PrintParameters=true"/> >> >> >> > transaction-type="RESOURCE_LOCAL"> >> org.apache.openjpa.persistence.PersistenceProviderImpl >> true >> NONE >> >> > value="jdbc:mysql://localhost:3306/cass_drupal7"/> >> > value="xxxxxx"/> >> > value="com.mysql.jdbc.Driver"/> >> > value="cadrupal-user"/> >> >> > value="org.apache.openjpa.jdbc.sql.MySQLDictionary"/> >> >> > value="autoReconnect=true"/> >> >> >> >> >> 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 = (List) >> 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 >>> >> > wrote: >>> >>> Hi All >>> >>> I periodically get an issue where the connection to my MySQL >>> Database timesout. I have set the value >> 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 >>> >>> 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 >>> >>> >>> >> >> > --------------060300040709090405010101--