DBCP: Validar las conexiones del pool

MySQL cierra las conexiones que han estado idle por más de un determinado tiempo (default 8 hs.), por lo tanto cuando no se accede a la aplicación por un tiempo, las conexiones de su pool quedan idle y luego MySQL las cierra. Luego, cuando la aplicación intenta usar alguna conexión del pool, tira la exception Broken pipe.

Validación de conexiones

Para evitarlo, se pueden agregar un par de parámetros al data source de la aplicación, que está configurado con DBCP.

validationQuery

Para validar una conexión es necesario setear este parámetro, que tiene que ser una query SQL que devuelva 1 o más filas, que depende del motor de base de datos al que se esté accediendo. En el caso de MySQL una query trivial es SELECT 1 (en Oracle por ej. sería SELECT 1 FROM DUAL).

testOnBorrow

El parámetro testOnBorrow provoca que se verifique que la conexión esté abierta cuando se solicite al pool, antes de usarla. Si la conexión no está abierta el pool la descarta e intenta obtener otra.
La siguiente porción de configuración, es para un data source configurado en Spring. La presento simplemente para ilustrar el seteo de los parámetros de DBCP.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
 <property name="driverClassName" value="${jdbc.driverClassName}"/>
 <property name="url" value="${jdbc.url}"/>
 <property name="username" value="${jdbc.username}"/>
 <property name="password" value="${jdbc.password}"/>
 <property name="validationQuery">
  <value>SELECT 1</value>
 </property>
 <property name="testOnBorrow">
  <value>true</value>
 </property>
</bean>

testWhileIdle

El parámetro testWhileIdle valida las conexiones cuando éstas se encuentran Idle.

<property name="validationQuery">
 <value>SELECT 1</value>
</property>
<property name="testWhileIdle">
 <value>true</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
 <value>60000</value>
</property>
<property name="numTestsPerEvictionRun">
 <value>3</value>
</property>
<property name="minEvictableIdleTimeMillis">
 <value>7200000</value>
</property>

Links

http://commons.apache.org/dbcp/
http://commons.apache.org/dbcp/configuration.html
post en mailing list de dbpc

SQL: información que no está en una relación usando LEFT JOIN

Dada la siguiente estructura, dos tablas de entidad y una de relación (sintaxis de MySQL):

create table alumno (id integer, nombre varchar(255)) primary key (id);
create table materia (id integer, nombre varchar(255)) primary key (id);
create table inscripcion(materia_id integer, alumno_id integer) primary key (materia_id, alumno_id);

Queremos conocer las materias en las cuales no hay inscripto ningún alumno.

Inscribimos a Emiliano en Matemática, a Luis en Lengua, y a Martin en ninguna materia. Por lo tanto nadie está inscripto en Gimnasia

insert into alumno (id, nombre) values (1, 'Emiliano');
insert into alumno (id, nombre) values (2, 'Luis');
insert into alumno (id, nombre) values (3, 'Martin');

insert into materia (id, nombre) values (1, 'Matemática');
insert into materia (id, nombre) values (2, 'Lengua');
insert into materia (id, nombre) values (3, 'Gimnasia');

insert into inscripcion (materia_id, alumno_id) values (1, 1);
insert into inscripcion (materia_id, alumno_id) values (2, 2);

Ejecutamos la query

SELECT m.nombre
FROM materia m
   LEFT JOIN inscripcion i ON m.id = i.materia_id
WHERE i.materia_id is null;

y resulta en

+-----------+
| nombre    |
+-----------+
| Gimnasia  |
+-----------+