Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why do I need to flush the connection pool each time I redeploy?

I have successfully made a connection to a remote MySQL server through Glassfish, however each time I make a change to the code or XHTML files, I need to open the administrator panel of Glassfish and flush the connection pool, otherwise I get the following error when I just refresh the page. Has anybody experienced this? I can post code or other information if it is needed.

HTTP Status 500 -

type Exception report

message

descriptionThe server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: WELD-000049 Unable to invoke [method] @PostConstruct public com.myapp.QuestionController.initialize() on com.myapp.QuestionController@4635bd2a

root cause

org.jboss.weld.exceptions.WeldException: WELD-000049 Unable to invoke [method] @PostConstruct public com.myapp.interfaces.QuestionController.initialize() on com.myapp.interfaces.QuestionController@4635bd2a

root cause

java.lang.reflect.InvocationTargetException

root cause

javax.ejb.EJBException

root cause

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start: Error Code: 0

root cause

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start: Error Code: 0

root cause

java.sql.SQLException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start:

root cause

javax.resource.spi.ResourceAllocationException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start:

root cause

com.sun.appserv.connectors.internal.api.PoolingException: java.lang.RuntimeException: Got exception during XAResource.start:

root cause

com.sun.appserv.connectors.internal.api.PoolingException: java.lang.RuntimeException: Got exception during XAResource.start:

root cause

java.lang.RuntimeException: Got exception during XAResource.start:

root cause

javax.transaction.xa.XAException: com.sun.appserv.connectors.internal.api.PoolingException: javax.resource.spi.LocalTransactionException: Communications link failure

The last packet successfully received from the server was 435�409 milliseconds ago. The last packet sent successfully to the server was 7 milliseconds ago.

Image of config

Persistence XML

<?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="SertifikatPU" transaction-type="JTA">
        <jta-data-source>jdbc/sertifikatdb</jta-data-source>
    </persistence-unit>
</persistence>

In the "Additional properties" in Glassfish connection pool settings I have just configured: servername, URL, user and password.


I HAVE SOLVED THIS PROBLEM

enter image description here

I made my own dual table, similar to the one in Oracle.

CREATE TABLE dual 
(
    x VARCHAR(1)
);

INSERT INTO dual(x) VALUES('y');
like image 965
LuckyLuke Avatar asked Oct 18 '11 16:10

LuckyLuke


People also ask

Should we close connection in connection pool?

Yes, certainly you need to close the pooled connection as well. It's actually a wrapper around the actual connection. It wil under the covers release the actual connection back to the pool.

What is connection pool issue?

The point of the connection pool is to keep a connection ready for the next call because creating connections is expensive. The connection objects maintained by the pool are backed by actual database connections, and the database is the one who closes that actual connection after the idle timeout period.

Why do we need connection thread pool?

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them. Connection pooling can greatly increase the performance of your Java application, while reducing overall resource usage.


2 Answers

Your root cause, PoolingException: javax.resource.spi.LocalTransactionException: Communications link failure is related to this Glassfish bug, which explains (in the comments tab at the bottom) that you may need to refresh your invalid connections.

The bug comment by Jagadish says to check your connection validation type. If it is set to "autocommit" (the default), the JDBC drivers may cache the prior connection validation data, and no actual database interaction will happen during future connection validations.

To resolve the problem, set connection-validation-method="table" and validation-table-name="any_table_you_know_exists" (replace any_table_you_know_exists with the name of any existing table). Doing this forces the connections to talk to the database instead of the cache; if the connection is invalid, it will be dropped and recreated. You may need to also specify is-connection-validation-required="true".

Articles to help with additional configuration:

  1. This article also explains the problem in detail.
  2. Jagadish's Oracle Blog Article on this topic has more info.
  3. Article explaining Glassfish JDBC Connection Validation in detail.

Text from Jagadish's blog:

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required=true
domain.resources.jdbc-connection-pool.DerbyPool.is-connection-validation-required = true

AS_INSTALL_ROOT/bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method=table
domain.resources.jdbc-connection-pool.DerbyPool.connection-validation-method = table

bin/asadmin set domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name=sys.systables
domain.resources.jdbc-connection-pool.DerbyPool.validation-table-name = sys.systables

Note that the sample code refers to sys.systables, which is a MS SQL table that is guaranteed to exist. For Oracle, refer to the guaranteed table dual. For MySQL, create a 1-column table solely for validation purposes; play it safe and pre-populate the table by inserting one row of data.

like image 142
JoshDM Avatar answered Oct 09 '22 15:10

JoshDM


Should you be using this driver?

com.mysql.jdbc.jdbc2.optional.MysqlXADataSource 

I see that you are using a different driver from the attached image ...

like image 25
r0ast3d Avatar answered Oct 09 '22 14:10

r0ast3d