Bug 1028216

Description of problem:

EAP 6.2 ignores the check-valid-connection-sql tag in the definition of data sources, the statement is never executed.


Version-Release number of selected component (if applicable):

# rpm -q jbossas-product-eap ironjacamar
jbossas-product-eap-7.3.0-4.Final_redhat_10.1.ep6.el6.noarch
ironjacamar-1.0.19-1.Final_redhat_2.ep6.el6.noarch

These correspond to EAP 6.2 ER7.

How reproducible:

Always.

Steps to Reproduce:

1. Install PostgreSQL and create a "test" database and a "test" user with password "test123". I think that this will be the same with any database, but only tested with PostgreSQL.

Enable the query log adding the following to /var/lib/pgsql/data/postgresql.conf:

---8<---
log_statement = 'all'
--->8---

2. Add a datasource and driver to standalone.xml like these:

---8<---
<datasource jndi-name="java:/test" pool-name="test" enabled="true" use-ccm="false">
  <connection-url>jdbc:postgresql://localhost/test</connection-url>
  <driver>postgresql</driver>
  <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
  <pool>
    <min-pool-size>75</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <prefill>true</prefill>
  </pool>
  <security>
    <user-name>test</user-name>
    <password>test123</password>
  </security>
  <statement>
    <prepared-statement-cache-size>100</prepared-statement-cache-size>
    <share-prepared-statements/>
  </statement>
  <validation>
    <check-valid-connection-sql>select 1</check-valid-connection-sql>
  </validation>
</datasource>

<drivers>
  <driver name="postgresql" module="org.postgresql">
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
  </driver>
</drivers>
--->8---

This also needs a module definition like this:

---8<---
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="org.postgresql">
  <resources>
    <resource-root path="postgresql.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>
--->8---

2. Create a simple application that uses the data source. I used a test.jsp page with the following content:

---8<---
<%@ page contentType="text/plain" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>

<%
  Context ctx = new InitialContext();
  DataSource ds = (DataSource) ctx.lookup("java:/test");
  ctx.close();

  Connection con = ds.getConnection();
  Statement stm = con.createStatement();
  ResultSet rs = stm.executeQuery("select 2");
  while (rs.next()) {}
  stm.close();
  con.close();
%>

Done.
--->8---

Actual results:

When executing the JSP only the actual query (select 2) is executed, and this is what the database query log displays:

LOG:  execute <unnamed>: select 2


Expected results:

The database query log should display first the check query and then the real query:

LOG:  execute <unnamed>: select 1
LOG:  execute <unnamed>: select 2


Additional info:

I tested it with JBoss AS 7.1.1 and it works correctly.

I tested it with EAP 6.1 and it doesn't work correctly.

A side effect of this is that when the database goes down all connections will fail, but they will not be discarded because they are never checked and the PostgreSQL exception sorter always returns false, so after a database outage the application never recovers.