In questo documento vengono trattate alcuni elementi sull'utilizzo di MySQL con un driver JDBC. Dopo una breve introduzione sull'uso delle connessioni JDBC vedremo alcune configurazioni che si presentano utilizzando architetture piu' complete, ed un poco piu' complesse, che prevedono l'utilizzo di firewall, strumenti di tracing, architetture j2ee con l'utilizzo di connection pool, validazione delle connessioni, ...; proxy, failover cluster, ...
Questo documento e' volutamente breve, con molti esempi e problemi tipici (eg. Firewall Blackout sintomo: le connessioni cadono per timeout). Spero che il risultato sia un documento tecnico pratico, il rischio e' che sia solo difficile ed inutile...

Volete leggere la Parte I? Non l'ho mai scritta! Pero' nei riferimenti vi sono diversi link utili.

L'accesso remoto in client/server alla base dati MySQL e' molto semplice e disponibile fin dalla prima release. E' il server MySQL stesso, senza utilizzo di alcun processo esterno che consente l'accesso in client/server. MySQL utilizza il solo protocollo TCP-IP, ed il server e' in attesa su un socket che, di default, risponde alla porta 3306.
Per MySQL vale la copia utente-indirizzo quindi tutte le abilitazioni debbono tener conto di questa importante caratteristica che consente un controllo molto selettivo sugli accessi.

Collegarsi a MySQL da un'applicazione Java e' semplice: basta utilizzare il driver JDBC! Il driver JDBC di MySQL (chiamato Connector/J) e' di tipo 4 (pure Java), risponde alle specifiche JDBC (fino alla versione JDBC 4.0) ed e' indipendente dalla piattaforma: quindi non vi sono altri prerequisiti.
E' sufficiente scaricare il file mysql-connector-java-5.1.18.tar.gz dal sito MySQL, porlo nel CLASSPATH ed utilizzarlo nell'applicazione con: jdbc:mysql://<hostname>[<:3306>]/<dbname>
Impostando il nome del server, l'eventuale porta (default 3306) ed il database: l'applicazione Java accede direttamente alla base dati MySQL!

Vediamo in dettaglio la sintassi:

jdbc:mysql://<hostname>[<:3306>]/<dbname>[?<property>=<value>[&<property>=<value>|...]]

Il significato dei parametri di connessione (hostname, post, dbname) e' banale... Meno nota e' la possibilita' di indicare piu' hostname (separati da ,) per la gestione del fail-over. Inoltre nella JDBC URL di MySQL possono essere specificate una o piu' property. Non sono di uso comune, anche perche' alcune possono essere impostate al momento della connessione (eg. user), ma alcune sono utili in casi particolari (eg. profileSQL, autoReconnectForPools). La tabella completa delle proprieta' supportate e' riportata su questo link.
Naturalmente per utilizzare una connessione JDBC server un programma scritto in Java. Un semplice programma di esempio si trova su: Ditelo con... i fiori!

E' importante ricordare che il server MySQL disconnette automaticamente le sessioni client non attive! Questo avviene con un timeout che e' tipicamente molto ampio. Il valore di default della variabile interactive_timeout e' infatti 28.800 secondi (ovvero 8 ore). Dal punto di vista dei "sintomi" la disconnessione da parte del server e' analoga a quella dovuta ad un problema di rete o ad un firewall.

I Firewall sono apparecchiature HW/SW che proteggono i sistemi da accessi non desiderati attraverso la rete.
La tipica configurazione di un Firewall per MySQL richiede la definizione di una sola semplice regola che consenta l'accesso dai client verso il DB server sulla porta su cui ascolta il database (eg. 3306).
A differenza di protocolli di rete utilizzati da altri database (eg. Oracle) MySQL non utilizza il Port Redirection quindi la semplice regola sulla porta 3306 e' sufficiente.
I firewall piu' recenti (Stateful Firewall) mantengono una cache delle connessioni TCP presenti. Se una connessione non viene utilizzata per troppo tempo il Firewall effettua un blackout (Firewall Blackout) e non accetta piu' comunicazioni provenienti dal "destinatario".

Il timeout del firewall provoca su MySQL la caduta della connessione. Sul server l'errore e' riportato sul file di log (eg. myserver.err):

120102  8:30:46 [Warning] Aborted connection 696969 to db: 'test' user: 'test' host: 'jb01.xenialab.it' (Got an error reading communication packets)

Mentre sul client risulta che il server abbia disconnesso la sessione:

Error: Communications link failure

The last packet successfully received from the server was 18069,607 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
SQLState:  08S01
ErrorCode: 0

Tale problema non si presenta con i firewall piu' semplici (o vecchi) che utilizzano unicamente il packet filtering basato sul numero di porta.
Per evitare il problema si puo' utilizzare il parametro di keepalive del TCP-IP definito a livello di sistema operativo. Naturalmente ogni OS/versione ha modalita' ed impostazioni differenti. Tipicamente i parametri del kernel da controllare/modificare sono: tcp_keepalive_time tcp_keepalive_intvl tcp_keepalive_probes (eg. per avere un probe ogni 5 minuti su Linux dare il comando immediato echo 300 > /proc/sys/net/ipv4/tcp_keepalive_time oppure una configurazione persistente impostare net.ipv4.tcp_keepalive_time = 300 nel file /etc/sysctl.conf)

La configurazione di default del keepalive su un Linux e':

[root@XenialabDB ~]# sysctl -a | grep keep
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

Generalmente si ritiene piu' adatta ad un DB Server la configurazione:

net.ipv4.tcp_keepalive_intvl = 6
net.ipv4.tcp_keepalive_probes = 10
net.ipv4.tcp_keepalive_time = 300

Le impostazioni di default del driver JDBC di MySQL utilizzano il keepalive (dalla versione Connector/J 5.0.7 e' impostato a true il parametro tcpKeepAlive). La descrizione completa di tutti i parametri utilizzabili si trova sul sito ufficiale.

Come fare ad determinare la causa di un problema di connessione a MySQL? Naturalmente e' sempre colpa della rete: ma bisogna anche dimostrarlo! (NdE ovviamente e' una battuta, ma sono sempre forti le rivalita' tra DBA e sistemisti di rete). Gli strumenti sono molti (eg. Log sul Firewall, trace con un datascope, snoop, Wireshark,...) ma e' possibile utilizzare anche il trace di MySQL.
Sul client e' possibile configurando la proprieta' profileSQL. Sul server si puo' dare il comando set global general_log='ON'. Nei relativi log si vedera' il dump di tutti i messaggi scambiati...
Risolto il problema non dimenticate di togliere i trace. E' molto dettagliato e consuma in fretta un intero disco!

Sebbene sia possibile scrivere applicazioni Java che accedono direttamente via JDBC ai dati, questo non e' l'unico modo di accedere ai DB. Java e' un linguaggio object-oriented e le piu' recenti modalita' di programmazione con tale linguaggio sfruttano appeno tale possibilita'. Risulta quindi comune non utilizzare direttamente un driver JDBC ma altre strati intermedi come Hibernate e C3P0. Una descrizione piu' introduttiva si trova su questo documento, nel seguito vediamo diversi esempi pratici su: Hibernate e C3P0, Tomcat, JBOSS, ...

Hibernate consente di sviluppare classi persistenti con tutte le proprieta' piu' tipiche (eg. association, inheritance, polymorphism, composition, collections, ...) associate a tabelle di un database relazionale. Le query possono essere scritte in SQL nativo oppure nella forma portabile (HQL). L'associazione tra tabelle ed entita' viene realizzata con un file

Benche' presente, la gestione del pooling di risorse di Hibernate e' piuttosto rudimentale e quindi si preferiscono altri strumenti per la gestione dei pool di sessioni come il c3p0. c3p0 e' una semplice libreria per rendere Enterprise i normali driver JDBC realizzando le estensioni previste dalle specifiche jdbc3. In particolare fornisce il supporto per: Connection Pooling e Supporto dello schema javax.sql.DataSource.

Vista la teoria ecco un esempio:

Tomcat e' un Servlet/JSP container, tra tutti sicuramente il piu' noto e diffuso. Tomcat viene distribuito con una licenza Open molto libera (Apache).

Ecco un esempio di configurazione (il file e' server.xml e la risorsa va posta nel contesto dell'applicazione):

<Resource auth="Container"
          type="javax.sql.DataSource"
          name="jdbc/gporder"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost/mydb"
          maxActive="10"
          maxIdle="5"
          validationQuery="/* ping */ SELECT 1"
          testOnBorrow="true"
          testWhileIdle="true"
          timeBetweenEvictionRunsMillis="10000"
          minEvictableIdleTimeMillis="60000"
          username="..." password="..."/>

JBoss e' un EJB container.

Ecco un esempio:

Altro diffuso application server:

grails-app/conf/spring/Resource.groovy
beans = {
  dataSource(BasicDataSource) {
    //run the evictor every 30 minutes and evict any connections older than 30 minutes.
    minEvictableIdleTimeMillis=1800000
    timeBetweenEvictionRunsMillis=1800000
    numTestsPerEvictionRun=3
    //test the connection while its idle, before borrow and return it
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
    validationQuery="/* ping */ SELECT 1"
  }
} 

L'utilizzo dei connection pool permette di ridurre il numero di connessioni dinamiche alla base dati, riducendo la latenza dell'esecuzione dell'SQL. Ma anche ogni connessione mantenuta aperta verso la base dati ha un overhead (memory, CPU, context switches, ...) sia sul database che sul client.

E' quindi molto importante dimensionare in modo adeguato i valori minimi e massimi del numero di connessioni mantenute da un Connection Pool. La documentazione ufficiale riporta i principi a cui attenersi che si possono riassumere in una parola: PROVATE!

Durante i test [NdA o nel monitoraggio dell'ambiente di produzione come purtroppo avviene spesso] le variabili da misurare sono i thread aperti, i thread attivi e l'apertura di connesioni. Durante il test lo stato del DB puo' essere controllato con SHOW GLOBAL STATUS e SHOW PROCESSLIST o, molto meglio, vanno campionati i relativi dati.

Per validare una connessione JDBC verso MySQL e' generalmente sufficiente una semplice query come SELECT 1. Se si utilizzano connessioni in load-balancing o la rete e' protetta da un firewall stateful una sola selezione non e' sufficiente poiche' vanno validate tutte la connessioni interne del pool (e non una sola) ed il controllo va ripetuto per tenere attive tutte le sessioni verificate dal firewall. Per fare questo si puo' utilizzare un HINT riconosciuto dal driver JDBC di MySQL: /* ping */ SELECT 1
E' molto importante utilizzare esattamente la stringa indicata senza ulteriori spazi, maiuscole, ... cosi' come descritto nella documentazione ufficiale.

Vi fidate? Io mai!
Cosi' mi sono armato di sniffer, interpreti SQL ed un database di test ed ho fatto qualche prova. Se tutto e' utilizzato correttamente la selezione con l'HINT di validazione viene "tradotta" dal driver JDBC in una richiesta di ping del protocollo di rete MySQL per tutte le connessioni in load balancing. Il server risponde immediatamente al ping e non ha alcun overhead poiche' la risposta avviene a livello di protocollo e non richiede l'esecuzione di una query. In effetti nessuna query viene trasmessa al server e, neanche abilitando il log al livello piu' elevato. Ma vediamo i dettagli...

Comando	Note	Server Log
set global general_log='ON'	Abilitazione del log	111231 20:25:19 651 Query set global general_log='ON'
/* ping */ select 1	HINT di validazione corretto	Il server non riceve alcuna query e nulla viene registrato sul log
/* Ping */ select 2	HINT errato	111231 20:26:25 651 Query /* Ping */ select 2

Analizzando quanto viene trasmesso in rete si nota un comportamento molto differente. Quando viene utilizzato l'HINT non viene trasmessa una query ma un particolare messaggio del protocollo di rete MySQL che corrisponde al comando Ping (14). Quando non viene utilizato l'HINT di validazione, o viene utilizzato un HINT errato, la query viene trasmessa integralmente (comando 3: Query) al server che la esegue e restituisce la risposta al client. Ecco il dettaglio dei pacchetti trasmessi in rete:

L'utilizzo della validazione richiede alcune accortezze... vediamo alcuni possibili errori e condizioni particolari.
Il primo, gia' riportato, e' quello di utilizzare la corretta sintassi per il comando di keepalive. Deve essere esattamente /* ping */
E' importante effettuare le prove con un proprio programma Java oppure con un interprete che non modifichi la sintassi delle query. Ad esempio Squirrel (tutorial) funziona correttamente (anche se invia, prima e dopo la query alcuni comandi MySQL di SET per la gestione del buffer) mentre Oracle SQLDeveloper analizza la query e... non trasmette i commenti! In questo modo l'HINT non viene riconosciuto dal driver che effettua una normale query verso il server.
L'HINT di validate e' specifico del driver JDBC, se si utilizza un altro tipo di connessione l'HINT viene trasmesso al server MySQL (che lo ignora tranquillamente). Ad esempio con phpMyAdmin la query raggiunge il server e viene correttamente raccolta dal log:

111231 21:22:40   660 Connect   root@localhost on 
                  660 Query     SET CHARACTER SET 'utf8'
                  660 Query     SET collation_connection = 'utf8_general_ci'
...
111231 21:22:53   661 Connect   root@localhost on 
                  661 Query     SET CHARACTER SET 'utf8'
                  661 Query     SET collation_connection = 'utf8_general_ci'
                  661 Query     /* ping */ select 1
                  661 Init DB   mysql
                  661 Query     SHOW MASTER LOGS
                  661 Query     SHOW TABLES
                  661 Quit
111231 21:22:53   661 Connect   root@localhost on 
                  661 Query     SET CHARACTER SET 'utf8'
                  661 Query     SET collation_connection = 'utf8_general_ci'
                  661 Query     /* Ping */ select 2
                  661 Init DB   mysql
...

Sull'argomento si trovano parecchie informazioni su web.
Sull'utilizzo dei database con il JDBC vi sono alcuni bellissimi documenti (sono in ordine "cronologico": i primi due documenti sono JDK 1.0 e sono quasi piu' vecchi di MySQL, sono del 1996):

• Developing Custom and Standard Interfaces to Oracle RDBMS in Java
• Using Java Standard Interfaces to Oracle
• Hello World ditelo con... i fiori (esempio JDBC)
• Oracle JDBC Drivers (NdE documento recente, completo ed abbastanza semplice)
• Connessione ad Oracle attraverso un Firewall

Li ho definiti bellissimi poiche' li ho scritti io, in realta'... insomma giudicate voi!