JBoss + Hibernate + MySQL sem perder ligações

Existem essencialmente duas formas distintas de usar Hibernate com MySQL (ou qualquer outra base-de-dados, para este ponto em concreto é indiferente), definindo todas as propriedades da ligação no próprio ficheiro de configuração do Hibernate, ou dizendo apenas que se pretende usar uma datasource JNDI pré-existente.

A primeira opção é a escolha típica numa aplicação stand-alone, mas a opção de utilizar uma datasource JNDI é a que faz mais sentido para application servers. Portanto, o ficheiro de configuração do Hibernate há-de ter, parcialmente, este aspecto:

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.datasource">
java:my_app_name
</property>
<property name="hibernate.setup">true</property>
<property name="hibernate.dialect">
org.hibernate.dialect.MySQLInnoDBDialect
</property>
<property name="transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="current_session_context_class">
thread
</property>
</session-factory>
</hibernate-configuration>

Depois basta definir a datasource com o nome JNDI correspondente, por exemplo assim:

<datasources>
<local-tx-datasource>
<jndi-name>my_app_name</jndi-name>
<connection-url>jdbc:mysql://host_or_ip/db_name</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>username</user-name>
<password>password</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
</local-tx-datasource>
</datasources>

Agora que está tudo montado como manda a documentação, sugiro uma experiência:

1. Ligar servidor BD.
2. Ligar application server.
3. Testar aplicação que se liga à base de dados (funciona)
4. Reiniciar servidor BD (ou esperar 8 horas).
5. Testar aplicação que se liga à base de dados (não funciona!)

Então… tanto trabalho para abstrair a ligação a base de dados e um simples restart da base de dados deixa as ligações geridas pelo Hibernate todas inválidas? E, pior, não há recuperação automática?

Pois… parece que não. E acrescentar o parâmetro autoReconnect=true ao URL JDBC também não resolve, aliás, a utilização desse parâmetro até é desaconselhada, como se pode ver pelo que diz no manual:

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don’t handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. — MySQL 5.0 Reference Manual

A solução que encontrei para MySQL (e que também funciona para MS SQL Server), é acrescentar a seguinte linha ao ficheiro de configuração da datasource:

<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

Esta opção faz mesmo aquilo que parece fazer, ou seja, antes de usar uma ligação, a query ali especificada é executada como forma de verificar se a ligação é usável. Se não ocorrer nenhum erro, a instrução SQL que realmente se pretende executar é enviada pela mesma ligação, se houver um erro, a ligação é descartada e é criada uma nova.

Claro que isto tem um impacto sério na performance uma vez que se está a duplicar o número de queries, o que pode ser particularmente grave se o acesso à base de dados for feito através de um canal de elevada latência (rede, por exemplo).

Aparentemente existem soluções melhores para outras bases de dados, como o seguinte comentário parece sugerir:

 

Whilst you can use the old “select 1 from dual” trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful.

JBoss provides a special connection validator which should be used for Oracle:

org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker

This makes use of the proprietary ping() method on the Oracle JDBC Connection class, and uses the driver’s underlying networking code to determine if the connection is still alive. — stackoverflow.com

Alguém conhece uma alternativa semelhante para MySQL?

MySQL Clone database schema

Neste artigo descobri uma forma de criar tabelas que dá muito jeito:

CREATE TABLE newtable LIKE mytable;

Pensei que devia existir algo equivalente para copiar as definições de uma base de dados inteira, mas aparentemente não existe.
A forma mais fácil de conseguir o mesmo resultado é provavelmente usar o mysqldump com a seguinte opção:

--no-data

No entanto, já há bastante tempo que queria experimentar os stored procedures do MySQL, por isso aproveitei a oportunidade para escrever o meu primeiro:

delimiter //
DROP PROCEDURE IF EXISTS copySchema //
CREATE PROCEDURE copySchema(IN schemaname CHAR(64))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_a CHAR(64) default 'testing';
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = schemaname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO table_a;
IF NOT done THEN
SELECT CONCAT('CREATE TABLE ', table_a, ' LIKE ', schemaname, '.', table_a) into @sqlstr;
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END
//
delimiter ;

CALL mydb.copySchema('mysql');

Referências:

• Create Procedure
• Cursors
• Prepared Statements

MySQL sliding window archive

O objectivo
Já aqui falei sobre logs centralizados. O objectivo que se pretende atingir é concentrar todos os logs de um datacenter num único sítio para consulta fácil e arquivo histórico. Mais concretamente, é importante:

• Guardar todos os logs durante pelo menos 5 anos.
• Manter os últimos meses rapidamente acessíveis e passíveis de serem consultados de forma flexível e rápida.
• Garantir que em caso de avaria grave não se perdem mais do que 1 ou dois dias de logs.

Noutro post falei da possibilidade de usar o Archive storage engine como forma de reduzir o espaço ocupado e, simultaneamente, dificultar o encobrimento de acções ilegítimas através da eliminação de logs. 

Os problemas
Ao tentar usar o Archive storage engine como repositório de todos os logs, tentanto tirar partido da compresão e da durabilidade inerentes, surgem alguns problemas, nomeadamente:

• A compressão/descompressão de dados, associada à inexistência de índices torna as consultas com critérios complexos muito mais demoradas do que em MyISAM.
• O recurso a partições para acelerar as consultas (que são sempre limitadas a um determinado intervalo de tempo, e por isso prestam-se a partition pruning) é uma boa ideia, mas há limites…

A solução
A conjugação dos requisitos anteriores aponta quase directamente para uma solução heterogénea que combine o melhor de dois mundos: a rapidez de acesso e a flexibilidade do storage engine MyISAM, com a durabilidade e compressão do Archive.
A solução final tem os seguintes componentes:

• Uma base de dados em que os logs são guardados em tabelas MyISAM. As tabelas são particionadas por intervalo, usando a data do evento como chave de particionamento. Cada partição irá conter um dia dados, ou seja uma média de 300.000 registos. As tabelas são criadas vazias com duas partições, uma para o dia actual e outra para o dia seguinte.
• Uma base de dados em que os logs são guardados em tabelas Archive, sem recurso a partições, porque são conhecidos vários problemas com essa combinação, e já tive oportunidade de experimentar alguns deles. As tabelas são criadas vazias.
• Um script executado diariamente que verifica a existência de dados na base de dados MyISAM com mais de 60 dias e, caso existam, os copia para a base de dados Archive, eliminando de seguida a partição correspondente (e os dados nela contidos) da base de dados MyISAM.

Desta forma mantém-se sempre 2 meses de logs que são muito rápidos de consultar, graças ao uso de partições e do storage engine MyISAM é fácil e rápido fazer backup, e assegura-se o armazenamento a longo prazo com taxas de ocupação adequadas.