Soluzione per errori 503 “Service temporarily unavailable” su Apache

Un nostro cliente aveva un problema con Apache: al riavvio della loro web application (che gira su un application server separato), Apache sembrava “addormentarsi” per circa un minuto, restituendo una pagina d’errore 503 “Service temporarily unavailable”.
Controllando i log, abbiamo trovato la causa del problema:

[Thu Oct 11 09:35:18 2012] [error] (111)Connection refused: proxy: HTTP: attempt to connect to 192.168.0.10:48880 (192.168.0.10) failed
[Thu Oct 11 09:35:18 2012] [error] ap_proxy_connect_backend disabling worker for (192.168.0.10)
[Thu Oct 11 09:35:19 2012] [error] proxy: HTTP: disabled connection for (192.168.0.10)
[...]

Il modulo proxy di Apache, vedendo che il servizio era down, bloccava tutte le richieste per un minuto. Per risolvere è bastato aggiungere l’opzione retry=0 alla direttiva ProxyPass nel file di configurazione:

ProxyPass / http://192.168.0.10:48880/ retry=0 timeout=5
ProxyPassReverse / http://192.168.0.10:48880/

Per maggiori informazioni si faccia riferimento alla documentazione di Apache.

Backup incrementali su un server RSYNC centralizzato

Una delle soluzioni più storicamente affidabili per effettuare backup incrementali in una Intranet è costituita dall’utilizzo di server RSYNC, sia su piattaforma Linux che Windows. In questa script d’esempio si presuppone di avere un server RSYNC già funzionante, protetto da password di accesso, verso il quale effettuare un backup incrementale settimanale.

Per prima cosa si definirà la directory di cui effettuare il backup, ad esempio la home dell’utente:

SRC_DIR=/home/$USER

Successivamente si specificherà il file che conterrà eventuali file o directory da escludere (anche mediante wildcard), il nome del server centrale di backup e quindi la relativa password di accesso, in questo modo:

EXCLUDES=/path/to/exclude_file
BACKUP_SERVER=server_name
export RSYNC_PASSWORD=server_password

A questo punto si sceglieranno i nomi delle directory di destinazione (è da notare come il comando date in combinazione con il flag A restituirà il nome del giorno della settimana per esteso) e le opzioni di backup come illustrato di seguito:

DST_DIR=`date +%A`
OPTS="--force --ignore-errors --delete-excluded --exclude-from=$EXCLUDES --delete --backup --backup-dir=/$DST_DIR -a"

Prima di inviare il comando di backup vero e proprio, un’ulteriore serie di comandi verrà utilizzata per ripulire i backup incrementali relativi al giorno corrente della settimana precedente (se esistente) in questo modo:

mkdir $HOME/empty_dir
rsync --delete -a $HOME/empty_dir/ $BACKUP_SERVER::$USER/$DST_DIR/
rmdir $HOME/empty_dir

In pratica verrà creata localmente (e quindi alla fine rimossa) una directory vuota sulla base della quale il contenuto del giorno corrente verrà aggiornato (di conseguenza, essendo la directory vuota ed utilizzando il flag –delete, ciò significa che a tutti gli effetti la directory di destinazione verrà ripulita).

Finalmente siamo pronti ad avviare il backup con il comando rsync come specificato di seguito:

rsync $OPTS $SRC_DIR $BACKUP_SERVER::$USER/current

Al termine dell’esecuzione (e della settimana) si avrà, nel server centralizzato di backup, rispettivamente per ogni utente, una directory denominata current/ che conterrà il backup completo, mentre una directory per ogni giorno della settimana conterrà il relativo backup incrementale.

Creazione di archivi TAR cifrati

Sicuramente ognuno di noi avrà utilizzato per i propri backup e restore il comando tar(1) nelle sue più diverse forme, ad esempio con o senza compressione (flag –z). Ma quanti di voi erano a conoscenza di come fosse semplice generare degli archivi cifrati?

Per ottenere ciò ci viene in aiuto il pacchetto OpenSSL, ovvero il toolkit crittografico che implementa i protocolli Secure Socket Layer (SSL, versioni 2 e 3) e Transport Layer Security (TLS, versione 1). In particolare, openssl(1) è il relativo comando che implementa le diverse funzioni crittografiche della libreria crypto di OpenSSL dalla shell.
Detto ciò, è quindi possibile creare un archivio TAR cifrato (relativamente alla directory DIR) in questo modo:

# tar -zcvf - DIR |openssl des3 -salt -k password |dd of=DIR.3des

dove, in particolare, viene previsto l’utilizzo dello standard di cifratura Triple-DES e la scrittura del file cifrato avviene mediante dd(1). Analogamente, sarà possibile estrarre il nostro archivio segreto mediante la pipeline illustrata di seguito:

# dd if=DIR.3des |openssl des3 -d -k password |tar -zxvf -

Ovviamente sempre a condizione di ricordarsi la password ;-).

Sincronizzare il sistema con HTPDate

Illustriamo in quest’esempio un modo alternativo a NTP (Network Time Protocol) per sincronizzare il nostro sistema.
Questa diversa modalità di operare potrebbe esserci utile perché ci potremmo trovare, tipicamente in presenza di reti Corporate o comunque rigidamente filtrate, a dover sincronizzare Linux Box che consentono il traffico da e verso la nostra organizzazione solo mediante la porta 80, ovvero HTTP, o comunque non attraverso la porta 123, tipica del protocollo NTP.

A tal scopo, è possibile utilizzare il comando HTPDate(8), il quale può essere utilizzato sia in modalità interattiva sia in modalità di servizio (daemon mode). Ma come lavora?

HTPDate sfrutta una connessione Telnet sulla porta 80 per recuperare una pagina web da una lista di Web Server specificati in input, i quali tipicamente restituiscono, a fronte di una richiesta standard GET / HTTP/1.1, oltre alla pagina desiderata, diverse informazioni addizionali quali ad esempio:

Date: Tue, 24 Jun 2008 13:43:48 GMT

Compreso il funzionamento di base del programma, utilizzarlo risulta davvero abbastanza semplice. In particolare, mediante il comando:

# htpdate -d www.google.com yahoo.com www.kernel.org
www.google.com 24 Jun 2008 11:38:36 GMT (0.055) => 1645
www.google.com 24 Jun 2008 11:38:37 GMT (0.055) => 1645
www.yahoo.com 24 Jun 2008 11:38:37 GMT (0.118) => 1645
www.yahoo.com 24 Jun 2008 11:38:38 GMT (0.117) => 1645
www.kernel.org 24 Jun 2008 11:38:39 GMT (0.190) => 1645
www.kernel.org 24 Jun 2008 11:38:40 GMT (0.189) => 1645
#: 3, mean: 1645, average: 1645.000
Timezone: GMT+1 (CET,CEST)
Offset 1645.000 seconds

vengono effettuare due richieste per ognuno dei tre Server Web specificati, ognuno dei quali ci evidenzia un offset di 1645 secondi, ovvero poco più di 27 minuti di differenza tra l’orario definito sulla nostra Linux Box e quello restituito rispettivamente dai tre Server Web.

A questo punto, constatando che il server più veloce nella risposta (e quindi il più accurato) è stato quello di Google (0.055 il suo tempo di risposta), sarà possibile sincronizzare la nostra Linux Box mediante il comando:

# htpdate -s -l www.google.com

dove il flag -l specifica l’utilizzo del Syslog come standard output.

Il file di log /var/log/messages riporterà quindi la serie delle operazioni eseguite da HTPDate, in particolare l’interrogazione del Server Web www.google.com, il calcolo della media delle risposte (in questo caso le due query risultano identiche), il recupero del Timezone corrente ed infine l’effettiva sincronizzazione del tempo.

Jun 24 13:21:55 seti htpdate: www.google.com
24 Jun 2008 11:49:20 GMT (0.076) => 1645
[...]
Jun 24 13:21:56 seti htpdate: Setting 1645.000 seconds
Jun 24 13:21:56 seti htpdate: Set: Tue Jun 24 13:49:21 2008

Il nostro sistema è quindi aggiornato!

La seconda modalità operativa di HTPDate consente di lanciare il comando in daemon mode, in modo tale da avere il nostro sistema costantemente sincronizzato ad intervalli regolari. Ad esempio mediante il comando:

# htpdate -D www.google.com www.yahoo.com www.kernel.org

verrà mantenuta la sincronizzazione con la lista dei Server Web indicati, nell’ordine specificato da HTPDate. Altre interessanti opzioni, come l’intervallo di polling per la sincronizzazione, la possibilità di specificare utente e gruppo per l’esecuzione del comando in modalità daemon mode (ad esempio nobody) piuttosto che l’opportunità di scegliere una porta arbitraria per l’esecuzione del programma (ad esempio la porta 8080 se fossimo in presenza di reti il cui accesso web sia consentito solo tramite Proxy Server definiti su quella porta) sono nella pagina man.

Riallocazione di un settore illeggibile o danneggiato in un disco con partizioni LVM

Nelle più recenti distribuzioni Linux è presente smartd(8), una potente utility per il monitoraggio dei dischi ATA, IDE e SCSI-3.

SMART (Self-Monitoring, Analysis and Reporting Technology) è una tecnologia sviluppata per monitorare l’affidabilità degli hard disk, eseguire dei test di funzionamento e quindi tentare di prevedere eventuali failure.

Talvolta ci sarà capitato di rilevare errori riportati dal kernel, dal demone smartd o tramite sistemi di altra natura, del tipo:

Apr 17 16:20:31 seti kernel: hde: dma_intr: status=0x51 { DriveReady SeekComplete Error }
Apr 17 16:20:31 seti kernel: hde: dma_intr: error=0x40 { UncorrectableError }, LBAsect=6305975, sector=6305901

Questo genere di errori sono tipicamente corrispondenti ad errori presenti in lettura o scrittura sul nostro hard disk.
Inoltre tale errori, in ambiente SMART, corrispondono a messaggi di log del tipo:

Apr 18 09:05:03 seti smartd[2253]: Device: /dev/hde, 1 Currently unreadable (pending) sectors

che inequivocabilmente ci informano circa l’impossibilità di accedere in lettura o in scrittura ad un settore (danneggiato) del disco.

Arrivati però a questo punto, cosa possiamo fare?

Nel nostro esempio, uno dei casi più complessi, il settore danneggiato si trova in una partizione LVM; sarà quindi nostro obiettivo cercare di individuare il relativo blocco fisico danneggiato e quindi riparare, se possibile, l’errore.
In generale è bene ricordare che il settore è la minima unità gestibile contenente dati mentre il blocco è un multiplo della dimensione del settore del disco stesso; allo stesso tempo il contenuto di un file può essere distribuito su uno o più blocchi di dati.

Cominciamo con un semplice test eseguito sul disco sul quale sono presenti gli errori sopraesposti:

# smartctl -t long /dev/hde

In questo caso attraverso l’utility smartctl(8) sarà eseguito un test approfondito, dalla durata di circa mezz’ora, sul device /dev/hde.

Al termine dello stesso, sarà possibile recuperare i risultati del test appena terminato, in questo modo:

# smartctl -l selftest /dev/hde
[...]
=== START OF READ SMART DATA SECTION ===
SMART Self-test log structure revision number 1
Num  Test_Description  Status                   Remaining  LBA_of_first_error
# 1  Extended offline  Completed: read failure  40%        6305975

Come si può notare dall’output restituito, il test appena eseguito si è interrotto al 40% avendo individuato un errore nell’indirizzamento di blocco logico (Logical Block Addressing – LBA) corrispondente al settore numero 6305975.
Andiamo a controllare come è partizionato il nostro disco fisso:

# fdisk -lu /dev/hde
Disk /dev/hde: 61.4 GB, 61492838400 bytes
255 heads, 63 sectors/track, 7476 cylinders, total 120103200 sectors
Units = sectors of 1 * 512 = 512 bytes
Device      Boot    Start         End      Blocks   Id  System
/dev/hde1   *          63      208844      104391   83  Linux
/dev/hde2          208845   120101939    59946547+  8e  Linux LVM

La prima cosa che facilmente si nota è che l’indirizzo logico del blocco si riferisce ad un settore presente nella partizione Linux LVM /dev/hde2.

A questo punto però si deve fare attenzione: l’LBA dell’errore restituito in precedenza, 6305975, costituisce il valore assoluto di indirizzamento logico della memoria di massa complessiva.

A noi invece, visto che si sta andando a lavorare all’interno di una singola partizione, occorrerà determinare l’offset corrispondente: di fatto sottrarremo dal valore dell’indirizzo riportato in precedenza il valore dell’indirizzo dal quale inizia la partizione LVM interessata.

Quindi, nel nostro esempio, l’offset cercato sarà:

6305975 – 208845 = 6097130			(FS block offset)

Inoltre, ricordando che ogni partizione LVM verrà poi organizzata in Physical Extent (PE) della stessa grandezza, ed essendo in presenza di allocazione lineare, il secondo passo consisterà nel determinare la dimensione (qui di seguito espressa in KB), di ogni PE tramite il comando:

# part=/dev/hde2; pvdisplay -c $part | awk -F: '{print $8}'
32768

Considerando che il physical block size del sistema corrisponde a 512 bytes (cioè metà KB), si avrà che la dimensione di ogni PE sarà uguale a:

32768 * 2 = 65536 				(PE physical size)

Quindi, per determinare in quale PE si trovi il blocco logico rovinato, sarà sufficiente dividere l’FS block offset precedentemente calcolato per la dimensione di ogni PE appena ottenuta:

6097130 / 65536 = 93.034 -> 93 		(#PE)

Sappiamo perciò che il blocco logico in questione risiede sul 93-esimo PE.
Di fatto, attraverso l’output del comando:

# lvdisplay --maps |egrep 'Physical|LV Name|Type'
LV Name                	/dev/VolGroup00/LogVol00
   Type                	linear
   Physical volume	/dev/hde2
   Physical extents	0 to 1800
LV Name                	/dev/VolGroup00/LogVol01
   Type                	linear
   Physical volume    	/dev/hde2
   Physical extents    	1801 to 1828

possiamo conseguentemente determinare che il PE considerato appartiene al primo Logical Volume (LV) chiamato /dev/VolGroup00/LogVol00 del Volume Group (VG) /dev/VolGroup00.

Visto che ora si andrà ad intervenire scendendo dal livello di partizione LVM a livello di LV, ci occorrerà anche sapere l’offset iniziale di quest’ultimo, ottenuto sommando il blocco logico di partenza del VG e tutti i blocchi logici relativi ai PE degli LV precedenti. Tramite il comando:

# grep pe_start $(grep -l $part /etc/lvm/backup/*)
pe_start = 384

sarà quindi prima possibile determinare il blocco logico di partenza del VG; poi, osservando che il nostro LV è di fatto il primo LV del VG, si determina che il primo PE di /dev/VolGroup00/LogVol00 è uguale a 0 (mentre se ad esempio avessimo dovuto lavorare sul secondo LV sarebbe stato uguale a 1801). Quindi risulterà:

384 + (0 * 65536) = 384				(LV offset)

Finalmente sarà possibile determinare il blocco fisico danneggiato del file-system secondo la formula:

((FS block offset) - (LV offset)) / ((PE physical size) / (physical block size))

ovvero:

((6097130 - 384) / (65536 / 512)) = (6096746 / 128) = 47630.828 -> 47630

Nel calcolo è da notare come il valore 128 corrisponda al numero di blocchi fisici presenti in ogni PE.

Siamo finalmente arrivati!

Testiamo ora che effettivamente il blocco fisico #47630 sia danneggiato attraverso il comando dd(1) e l’opzione ‘skip’ che consente di leggere l’input saltando i primi 47630 blocchi fisici (ovvero tentando di leggere esattamente il blocco fisico danneggiato):

# dd if=/dev/VolGroup00/LogVol00 of=/tmp/block-47630 bs=65536 count=1 skip=47630
dd: reading `/dev/VolGroup00/LogVol00': Input/output error
0+0 records in
0+0 records out
0 bytes (0 B) copied, 18.6989 seconds, 0.0 kB/s

Non ci resta altro da fare che formattare il blocco fisico danneggiato (‘scrivendo zeri’ per una lunghezza pari alla dimensione del PE physical size) attraverso il comando dd(1) e l’opzione ‘seek’ che consente di scrivere in output saltando i primi 47630 blocchi fisici (ovvero accedendo esattamente al blocco fisico danneggiato):

dd if=/dev/zero of=/dev/VolGroup00/LogVol00 count=1 bs=65536 seek=47630

ed il gioco (si fa per dire) è fatto!

Possiamo infine, come verifica, dopo aver ripetuto il testo iniziale attraverso il comando smartctl -t long /dev/hde interrogare il relativo log ottenendo il seguente risultato:

# smartctl -l selftest /dev/hde
[...]
=== START OF READ SMART DATA SECTION ===
SMART Self-test log structure revision number 1
Num  Test_Description  Status                   Remaining  LBA_of_first_error
# 1  Extended offline  Completed without error  00%        -
# 2  Extended offline  Completed: read failure  40%        6305975

che ci mostra come quest’ultimo test (Num #1) sia stato portato a termine senza errori, e che quindi l’errore del nostro hard disk sia stato efficacemente corretto.

Quando “ENUM” fa i capricci

Il tipo “ENUM” consente di definire una lista di valori da attribuire a un campo, una soluzione molto comoda quando i valori sono pochi e vogliamo evitare la creazione di una tabella di supporto. Vediamo però un esempio in cui le cose non vanno come dovrebbero.


mysql> CREATE TABLE test_enum (ID INT, strano ENUM('0','1'));
Query OK, 0 rows affected (0.38 sec)

Abbiamo creato una tabella con un campo ENUM che accetta i valori ‘0’ e ‘1’. Fin qui, tutto bene.


mysql> INSERT INTO test_enum VALUES (1,1);
Query OK, 1 row affected (0.28 sec)

Il primo inserimento dovrebbe fare quel che ci aspettiamo, cioè mettere ‘1’ nel campo ID e ‘1’ nel campo strano. Controlliamo:


mysql> SELECT * FROM test_enum;
+------+--------+
| ID | strano |
+------+--------+
| 1 | 0 |
+------+--------+
1 row in set (0.00 sec)

Uhm, no. Questo non è quel che volevamo. Come mai il campo strano ha uno zero mentre noi abbiamo inserito 1? Il motivo è che i valori di enum devono essere valori testuali e quindi andrebbero messi fra virgolette.

Va bene, ma cosa succede se invece mettiamo un numero? Succede che MySQL cerca di venirci incontro, interpretando il numero come “il valore che sta alla posizione indicata da quel numero”. Nel nostro caso, si trattava di 1, che identifica la prima posizione, che nel nostro caso era ‘0’. Ecco spiegato l’arcano. Questo meccanismo si rivela comodo quando i valori hanno una grafia dubbia, e quindi per evitare errori possiamo citarli per posizione invece che per nome:


nano ENUM('Dotto', 'Gongolo', 'Eolo', 'Brontolo', 'Mammolo', 'Pisolo', 'Cucciolo');

Ora l’istruzione


INSERT INTO nani set nano = 1

farà sì che il campo nano contenga ‘Dotto’, cioè il primo valore della serie.

Un tranello simile ci attende quando vogliamo utilizzare il valore di default di un campo. Sapete che ogni campo di una tabella MySQL può avere un valore di default, da usare quando si omette quella colonna in un inserimento.

Guardate la creazione di questa tabella, in cui mettiamo un valore di default per un campo ENUM.


mysql> CREATE TABLE test_enum2 (ID int, strano enum('0','1') NOT NULL DEFAULT 1 );
Query OK, 0 rows affected (0.01 sec)

Ora, se inseriamo un record che non contiene un valore per il campo “strano”, ci potremmo aspettare che tale campo assuma il valore ‘1’.


mysql> INSERT INTO test_enum2 (ID) VALUES (10);
Query OK, 1 row affected (0.00 sec)

Invece, anche questa volta, ci troviamo il valore ‘0’.


mysql> SELECT * FROM test_enum2;
+------+--------+
| ID | strano |
+------+--------+
| 10 | 0 |
+------+--------+
1 row in set (0.00 sec)

La spiegazione è la stessa. Anche per la definizione del valore di default, indicando un numero senza virgolette si intende la posizione nella lista, non il valore stesso.

Trovare ed eliminare duplicati

La duplicazione di dati non dovrebbe mai avvenire in un database bene organizzato, ma talvolta, quando si ha a che fare con dati ereditati da applicazioni poco accurate, può accadere di ritrovarsi con una tabella che contiene record duplicati.

In questa poco piacevole situazione, ci sono tre tipi di operazioni che si vorrebbero eseguire:

  • Elencare i record senza duplicati;
  • Elencare i valori duplicati;
  • Eliminare i duplicati.

Prendiamo ad esempio una tabella con qualche record duplicato (per esigenze di spazio, la facciamo corta, ma supponete di avere svariate migliaia di record).


mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 5 | aaa | xxx |
| 6 | ddd | zzz |
| 7 | aaa | xxx |
| 8 | bbb | yyy |
| 9 | ccc | www |
+---+------+-----+
9 rows in set (0.18 sec)

La prima esigenza, vedere i record senza doppi, viene risolta facilmente usando la clausola DISTINCT:


mysql> SELECT DISTINCT a, b FROM condoppi;
+------+-----+
| a | b |
+------+-----+
| aaa | xxx |
| aaa | yyy |
| bbb | yyy |
| ccc | www |
| ddd | zzz |
+------+-----+
5 rows in set (0.12 sec)

Facilissimo. Basta premettere DISTINCT alla lista dei campi per avere un elenco pulito, senza duplicati.

Notate che se avessi inserito fra i campi anche la chiave primaria (campo “i”), il DBMS mi avrebbe stampato tutti i record, perché ovviamente sono differenziati grazie al campo univoco. La clausola DISTINCT è anche utile per avere un elenco dei valori distinti (appunto) per una data colonna. Per la seconda esigenza, vedere quali valori sono doppiati, non c’è formula magica immediata, ma bisogna sforzarsi un po’ di più.

Il metodo consiste nel raggruppare i valori con un conteggio, e considerare solo quelli maggiori di 1. Si noti la clausola HAVING che (a differenza di WHERE) viene valutata dopo l’operazone di raggruppamento.


mysql> SELECT a, b COUNT(*) AS quanti
FROM condoppi
GROUP BY a, b
HAVING quanti > 1;
+------+-----+--------+
| a | b | quanti |
+------+-----+--------+
| aaa | xxx | 2 |
| bbb | yyy | 2 |
| ccc | www | 2 |
+------+-----+--------+
3 rows in set (0.39 sec)

Così possiamo vedere quali sono i valori (o le combinazioni di valori) che sono registrati più di una volta.

Infine, se vogliamo cancellare dalla tabella i valori doppi, esiste un metodo molto semplice e pratico: aggiungere un indice univoco alla tabella, ma usando la clausola IGNORE, per far sì che il DBMS non si fermi al primo errore di record duplicato, ma vada avanti, di fatto saltando l’inserimento dei record con valori già inseriti.


mysql> ALTER IGNORE TABLE condoppi ADD UNIQUE KEY (a, b) ;
Query OK, 9 rows affected (0.54 sec)
Records: 9 Duplicati: 4 Avvertimenti: 0
mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 6 | ddd | zzz |
+---+------+-----+
5 rows in set (0.02 sec)

Aggiornare più condizioni con una sola query

PW scrive che vorrebbe trovare un modo di eseguire diversi UPDATE su una tabella usando una sola query. Nella pratica, si trova ad avere record di prodotti di diverse aziende, che vuole aggiornare in maniera omogenea, ma senza dover ripetere la stessa query diverse volte.

Per esempio, vorrebbe cambiare lo sconto offerta sui libri di informatica, di finanze, di attualità, con quantità differenti per ogni categoria. Una possibilità è la seguente:

UPDATE libri
SET sconto =
CASE
WHEN sezione = 'informatica' THEN 12
WHEN sezione = 'attualita' THEN 10
WHEN sezione = 'finanze' THEN 8
ELSE sconto
END
WHERE id_editore IN (5,6,8,11);

Questa query agisce solo sui libri degli editori considerati (clausola WHERE) e al loro interno modifica il campo sconto solo nelle sezioni indicate. Per le altre sezioni, il valore viene lasciato uguale, grazie alla clausola ELSE della funzione CASE.

Comporre query di questo tipo può essere più difficile che comporre una query per ogni condizione, ma talora si guadagna in efficienza, ed è bene sapere che ci sono alternative!

Passaggio efficiente di record tra client e server

In un protocollo client-server, quando viene eseguita una query, il server ha due modi per inviare i record trovati al client: uno alla volta o tutti insieme. Il modo effettivamente usato dipende dalle richieste del client.

Programmando MySQL con un linguaggio evoluto come Perl o PHP, la differenza non si coglie, perché il modo di ricezione attivato per default è il “tutti insieme”, cioè il server invia i record in un unico array che viene quindi trasferito alla memoria del client.

Chi si è preso la briga di guardare dietro le quinte del driver usato da Perl e PHP, però, ha visto che questi linguaggi si appoggiano alla libreria di funzioni scritta in C, che ha la capacità di differenziare il tipo di invio dei record. La libreria delle API (Application Programming Interface) di MySQL ha due funzioni per questo compito: mysql_use_result e mysql_store_result.

mysql_store_result è il comportamento più comodo per il programmatore, perché trasporta tutti i record ritrovati in una sola mandata. In questo modo il programma ha a disposizione due funzionalità che di solito si danno per scotate, ma non lo sono: il numero di record ritrovati e la possibilità di saltare avanti e indietro nella lista dei record.

mysql_use_result invece istruisce il server perché invii i record uno alla volta. Questo comportamento, che a prima vista potrebbe sembrare illogico e indesiderabile, trova fondamento nei casi in cui si debbano leggere grandi quantità di record, quando è meglio prendere i record uno per uno, farci quel che ci si deve fare (per esempio stamparlo o inviarlo a un file) e passare a quello successivo.

Poiché mandare i record tutti insieme richiede il costo aggiuntivo dell’occupazione di memoria per un array (la cui creazione richiede più tempo della semplice occupazione dello spazio di un singolo record) quando si manipolano grandi quantità di record (parlo di decine di MB), allora la gestione uno-alla-volta si dimostra più veloce.

Per contro, mysql_use_result ha lo svantaggio di non poter dire quanti record sono stati trovati finché tutti sono stati trasferiti dal server al client.

Comunque, per quantità di record limitate (dipende anche dalle risorse della macchina) che coprono la maggior parte delle applicazioni, mysql_store_result è il metodo da preferirsi.

Ma, mi sento dire, se l’uso di queste funzioni si stabilisce in base al presunto numero di record e il programmatore non può conoscere il numero prima dell’esecuzione della query, com’è possibile prendere una decisione?

Potrei dire che i programmatori bravi sanno a priori se la query che stanno per eseguire è di quelle da tremila record o da tre milioni, ma a parte quelli che hanno queste intuizioni difficilmente trasferibili ad altri, se il vostro database ha una gran quantità di record e avete paura che un risultato ve ne porti qualche milione sul client, esaurendo tutta la memoria se usate mysql_store_result, potete sempre sondare il database con una query di conteggio.

Per esempio, prima di eseguire


SELECT autore, titolo FROM libridiamazon WHERE autore LIKE "A%";

potete sondare il database con questa:


SELECT COUNT(*) FROM libridiamazon WHERE autore LIKE "A%";

Se il conteggio è nell’ordine delle migliaia, usate mysql_store_result. Se invece si va oltre i diecimila, forse è il caso di scegliere mysql_use_result. Volete sapere come gestire questo doppio comportamento da Perl e PHP?

Lista record con numero progressivo

Questa richiesta è arrivata su un forum di programmazione: Come faccio a elencare i risultati di una query con un numero progressivo per ogni record?

Ci sono diverse risposte a questa domanda. La più immediata sarebbe di implementare il conteggio nell’ambito dell’applicazione che riceve i dati. Questa soluzione, però, richiede dettagli diversi in ogni linguaggio e potrebbe non essere quel che si cerca.

Invece, esiste un modo per far contare i record a MySQL, in modo da avere un risultato che sia lo stesso, a prescindere dal linguaggio usato.

Forse non tutti sanno che MySQL consente di usare delle variabili “globali” che sono attive per tutta la durata di una connessione. Per esempio, un uso frequente di questa funzionalità si ha per ovviare alla mancanza di subquery nelle versioni inferiori alla 4.1:


mysql> SELECT @massimo := max(stipendio) from paghe;
mysql> SELECT nome, cognome, stipendio from paghe where stipendio = @massimo;

In maniera simile, possiamo usare una variabile per creare un contatore. Poiché ogni variabile ha valore NULL finché non le viene assegnato esplicitamente qualcosa, è necessario eseguire due query per poter raggiungere lo scopo. Prima, vediamo la query senza contatore.


mysql> SELECT nome,stipendio, genere FROM persone;
+--------+-----------+--------+
| nome | stipendio | genere |
+--------+-----------+--------+
| John | 5000 | m |
| Mario | 6000 | m |
| Frank | 5000 | m |
| Otto | 6000 | m |
| Susan | 5500 | f |
| Martin | 5500 | m |
| Mary | 5500 | f |
| Bill | 5000 | m |
| June | 6000 | f |
+--------+-----------+--------+
9 rows in set (0.01 sec)

Poi, eseguiamo le due query. Innanzitutto inizializziamo il contatore.


mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Infine, eseguiamo la query includendo il contatore con una formula per incrementarlo.


mysql> SELECT @N := @N +1 AS numero, nome,stipendio,genere FROM persone;
+--------+--------+-----------+--------+
| numero | nome | stipendio | genere |
+--------+--------+-----------+--------+
| 1 | John | 5000 | m |
| 2 | Mario | 6000 | m |
| 3 | Frank | 5000 | m |
| 4 | Otto | 6000 | m |
| 5 | Susan | 5500 | f |
| 6 | Martin | 5500 | m |
| 7 | Mary | 5500 | f |
| 8 | Bill | 5000 | m |
| 9 | June | 6000 | f |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Notate che ogni variabile in una query può essere calcolata una volta sola per ogni record.

Infine, se adottate una soluzione simile, ricordatevi di inizializzare il contatore alla query successiva, altrimenti i vostri record avranno un conteggio in continua crescita!