NULL e non più NULL

Un NULL in SQL è un valore speciale che indica l’assenza di valore. Per esempio, se un campo “figli” contiene 0 (zero), significa che non ci sono figli, mentre se il campo contiene NULL significa che non sappiamo se ci siano figli o meno. Un campo “età” a 0 indica una persona appena nata, mentre lo stesso campo a NULL indica che la persona non ha dichiarato l’età.

Stabilita questa differenza semantica, occorre anche sapere che in SQL il campo NULL è virale, nel senso che trasforma in NULL tutto ciò con cui viene a contatto.


mysql> SELECT 1 + NULL, 1 * NULL, 1 / NULL, 1 - NULL;
+----------+----------+----------+----------+
| 1 + NULL | 1 * NULL | 1 / NULL | 1 - NULL |
+----------+----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+----------+----------+----------+
1 row in set (0.03 sec)

Esiste una sintassi speciale per confrontare i campi con un valore NULL. Non si può usare l’operatore “=”, ma si usa la sintassi IS [NOT] NULL.


mysql> SELECT * FROM prova;
+------+------+
| i | a |
+------+------+
| 1 | a |
| 2 | NULL |
| 3 | b |
+------+------+
3 rows in set (0.03 sec)
mysql> SELECT * FROM prova where a = NULL;
Empty set (0.02 sec)

Notate che la sintassi “a = NULL” non sortisce i risultati voluti. Usando il gergo appropriato, però, otteniamo quel che ci serve.


mysql> SELECT * FROM prova where a IS NULL;
+------+------+
| i | a |
+------+------+
| 2 | NULL |
+------+------+
1 row in set (0.38 sec)


mysql> SELECT * FROM prova where a IS NOT NULL;
+------+------+
| i | a |
+------+------+
| 1 | a |
| 3 | b |
+------+------+
2 rows in set (0.02 sec)

Fin qui, è SQL standard. Potrebbe sembrare strano a chi è digiuno del linguaggio, ma per gli abitués del database è pane quotidiano. Però MySQL ha alcune estensioni nel trattamento del NULL che mettono in difficoltà anche gli esperti di altri dialetti SQL.

La prima anomalia si ha nei campi AUTO_INCREMENT e TIMESTAMP. Una tabella può avere un campo dichiarato AUTO_INCREMENT, il che significa che, in assenza di un valore esplicito, il campo assume il primo valore disponibile al di sopra del valore più alto già presente nella tabella. Molto comodo per creare un campo con valore univoco, ma la sorpresa arriva quando si considera un codice come questo:


mysql> CREATE TABLE prova (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a CHAR(10)
);
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM prova;
+---+------+
| i | a |
+---+------+
| 1 | abc |
| 2 | abc |
+---+------+
2 rows in set (0.01 sec)

Benché la colonna “i” sia dichiarata “NOT NULL”, noi ci abbiamo inserito deliberatamente un campo NULL, e il risultato è che MySQL ha creato per noi un campo con un valore crescente. Avremmo ottenuto lo stesso risultato con questa sintassi:


INSERT INTO prova (a) VALUES ('abc');

Infatti, NULL è l’assenza di valore, pertanto l’omissione del campo “i” dalla lista dei campi fa scattare il meccanismo di AUTO_INCREMENT. Si noti che io posso anche inserire un valore esplicito


mysql> INSERT INTO prova VALUES (100, 'abc');
Query OK, 1 row affected (0.01 sec)

Se questo valore è più alto del massimo valore presente nella tabella, verrà alterato il meccanismo di generazione del numero incrementato.


mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM prova;
+-----+------+
| i | a |
+-----+------+
| 1 | abc |
| 2 | abc |
| 100 | abc |
| 101 | abc |
+-----+------+
4 rows in set (0.01 sec)

Dopo che è stato inserito il numero 100, l’inserimento successivo prende il valore 101. Se invece inseriamo un numero inferiore al massimo, il meccanismo di generazione non viene alterato, come mostrato dalla sequenza che segue.


mysql> INSERT INTO prova VALUES (50, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM prova;
+-----+------+
| i | a |
+-----+------+
| 1 | abc |
| 2 | abc |
| 100 | abc |
| 101 | abc |
| 50 | abc |
| 102 | abc |
+-----+------+
6 rows in set (0.01 sec)

Un meccanismo simile esiste per i campi TIMESTAMP che, quando ricevono un NULL, generano il valore della data e ora attuale.


mysql> INSERT INTO prova VALUES (50, 'abc');
mysql> CREATE TABLE provaora (
a char(10),
ts TIMESTAMP
);
mysql> INSERT INTO provaora VALUES ('abc', NULL);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO provaora (a) VALUES ('abc');
Query OK, 1 row affected (0.01 sec)


mysql> SELECT * FROM provaora;
+------+---------------------+
| a | ts |
+------+---------------------+
| abc | 2004-02-06 17:48:38 |
| abc | 2004-02-06 17:48:39 |
+------+---------------------+
2 rows in set (0.01 sec)

(Ho esitato un attimo fra i due inserimenti, per dar modo al sistema di differenziare i due record, che altrimenti avrebbero avuto lo stesso timestamp).

Anche nei campi TIMESTAMP si può inserire un valore esplicito, ma a differenza di quanto avviene per i campi AUTO_INCREMENT, questo fatto non ha influenza sulle successive generazioni di orario. Ogni valore NULL (o assenza di valore) passato a quel campo causerà la registrazione dell’orario attuale.

Ci sono altri sottili trabocchetti legati ai NULL, ma ne parleremo in un’altra occasione.

Quando la password non funziona

Se avete installato il server di una delle ultime versioni di MySQL per farci qualche prova, avrete notato che non potete accedervi facilmente da applicazioni che invece funzionavano bene con le versioni 3.23.x e 4.0.x.

Può esservi capitato di installare il server, seguire le istruzioni e creare uno o più utenti, e vedere che dal classico client mysql (il monitor a linea di comando) tutto sembra andar bene. Ma quando volete accedervi da una procedura in PHP, Perl, Delphi, o anche dal comodo MySQL Control Center, il server risponde con un messaggio criptico, negando l’accesso per una qualche incompatibilità protocollare.

Il motivo è che, a partire dalla versione 4.1, MySQL utilizza un diverso protocollo di autenticazione. In pratica, viene utilizzata una password di 160 bit invece del vecchio formato a 64 bit. Quindi, se avete creato un utente con il client a linea di comando, la password viene memorizzata nel nuovo formato. Il problema è che le vecchie applicazioni utilizzano ancora le librerie che trattano password a 64 bit. Quindi, al momento del collegamento, il server chiede al client qualcosa a 160 bit che il client non è in grado di capire.

Come risolvere questo problema? Se le vostre applicazioni sono scritte in C, allora basta ricompilarle utilizzando le librerie fornite con la versione installata di fresco. Ma se usate un’applicazione che si basa su driver diversi, allora dovete ricorrere a un sotterfugio alternativo.

Il sistema di riconoscimento degli utenti mantiene la compatibilità con il formato precedente, per cui se una password è registrata con il vecchio formato a 64 bit, il server userà il protocollo che è ancora usato dalle vostre applicazioni.

Il problema sorge dal fatto che nel server 4.1 e 5.0 la funzione password usa il formato a 160 bit, che non e’ riconosciuta dai client delle vecchie versioni.

Il problema è presto risolto. Collegatevi al server con il client mysql appena installato, e cambiate la password in questo modo:


mysql> SET PASSWORD=OLD_PASSWORD("LaTuaPassword");

Ciò fatto, il vecchio client riconoscerà la password e funzionerà come prima.

Ma questo è solo un palliativo. La cosa migliore da fare, una volta che vi siate sincerati che tutto il resto funziona, è aggiornare la libreria del client, così da poter utilizzare le password a 160 bit, che sono molto più sicure.

Troppe righe in modifica

GT mi ha proposto un problema semplice, ma che può dare dei mal di testa a chi non trova una spiegazione in fretta.

GT ha questa tabella


mysql> select * from luoghi;
+----------+----------+-----------+
| id_luogo | luogo | provincia |
+----------+----------+-----------+
| 41 | Ragusa | RG |
| 42 | Vasto | CH |
| 43 | Macerata | MC |
| 44 | Asola | MN |
| 45 | Palermo | PA |
| 49 | Prato | PO |
| 51 | Trento | TN |
| 37 | Firenze | FI |
| 38 | Piombino | LI |
| 50 | Borsano | VA |
+----------+----------+-----------+
10 rows in set (0.00 sec)

Il caso scaturisce dall’esecuzione dell’istruzione


REPLACE INTO luoghi SELECT * from vecchi_luoghi;

La tabella luoghi è una normalissima tabella con una chiave primaria e alcuni campi. La tabella contiene già dei record e GT ha inserito dei record prendendoli da vecchi dati. Alcuni di quelli già esistevano e per questo ha usato REPLACE invece di INSERT.

Il risultato dell’operazione è corretto. GT si ritrova nella tabella tutti i record che ci dovrebbero essere ed il loro valore è quello voluto.


mysql> select * from luoghi;
+----------+----------+-----------+
| id_luogo | luogo | provincia |
+----------+----------+-----------+
| 41 | Ragusa | RG |
| 42 | Vasto | CH |
| 43 | Macerata | MC |
| 44 | Asola | MN |
| 45 | Palermo | PA |
| 49 | Prato | PO |
| 51 | Trento | TN |
| 30 | Bologna | BO |
| 37 | Firenze | FI |
| 38 | Piombino | LI |
| 2 | Milano | MI |
| 1 | Parma | PR |
| 50 | Borsano | VA |
+----------+----------+-----------+
13 rows in set (0.00 sec)

Però, la faccenda nasce dal fatto che GT ha inserito 3 righe, e MySQL gli ha segnalato "9 rows affected"

GT è preoccupato per questa incongruenza e teme che si tratti di un bug, ma la spiegazione è semplice e rassicurante al tempo stesso. REPLACE è in realtà una scorciatoia che comprende due comandi: prima un DELETE, usando la chiave primaria, e poi un INSERT.

Tre delle righe inserite nella tabella esistevano già. Quindi REPLACE le ha cancellate ( = operazione su 3 righe ) e poi le ha inserite (altre 3 operazioni). Infine c’erano tre righe nuove, per le quali il comando DELETE ha dato risultato negativo (= nessuna riga interessata all’operazione) e infine l’inserimento ha prodotto 3 righe.

Il totale è 9 (3 cancellate + 6 inserite). Il dubbio è risolto e tutti possono andare a dormire tranquilli.

Manipolare date e orari

Una delle “spine” di molti linguaggi di programmazione è la gestione delle date. Le date sono elementi sfuggenti e difficili da classificare, perché hanno significati diversi in diverse culture ed epoche storiche, perché la loro rappresentazione non è standardizzata, perché “12/2/2004” vuol dire 12 febbraio in Italia e 2 dicembre in America. Ce n’è a sufficienza per scoraggiare anche i programmatori più pazienti.

In MySQL, per ovviare a molti dei problemi sopra esposti, il formato delle date è standardizzato: “AAAA-MM-GG”, quindi il 12 febbraio si scrive “2004-02-12”, mentre il 2 dicembre è “2004-12-02”.

Può sembrare un aspetto negativo, specialmente per chi non è abituato a vedere le date in questo formato “alieno”, ma per un programmatore tale formato risolve diversi problemi, a parte il trattamento dei dati in modo univoco, c’è di buono che le date in questo formato si possono ordinare naturalmente, senza modifiche aggiuntive. Chiunque abbia provato a ordinare in sequenza temporale date in fromato “GG/MM/AAAA” sa di cosa sto parlando.

Ma gli aspetti positivi non finiscono qui. MySQL ha una caterva di funzioni per la gestione delle date. Quella più usata è DATE_FORMAT, che controlla il formato di date e orari.


mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2004-04-21 17:30:18 |
+---------------------+


mysql> select date_format(now(),"%d-%b-%Y");
+-------------------------------+
| date_format(now(),"%d-%b-%Y") |
+-------------------------------+
| 21-Apr-2004 |
+-------------------------------+


mysql> select date_format(now(),"%M-%Y");
+----------------------------+
| date_format(now(),"%M-%Y") |
+----------------------------+
| April-2004 |
+----------------------------+


mysql> select date_format(now(),"%M, %d %Y");
+--------------------------------+
| date_format(now(),"%M, %d %Y") |
+--------------------------------+
| April, 21 2004 |
+--------------------------------+


mysql> select date_format(now(),"%M, %d %Y %h:%i%p");
+----------------------------------------+
| date_format(now(),"%M, %d %Y %h:%i%p") |
+----------------------------------------+
| April, 21 2004 05:32PM |
+----------------------------------------+


mysql> select date_format(now(),"%d-%b-%Y %H:%i");
+-------------------------------------+
| date_format(now(),"%d-%b-%Y %H:%i") |
+-------------------------------------+
| 21-Apr-2004 17:34 |
+-------------------------------------+

Le funzioni DATE_ADD e DATE_SUB possono aggiungere o sottrarre giorni, mesi o anni a una data, e restituire il risultato dell’operazione. Per esempio:


mysql> select curdate(), date_add(curdate(), interval 3 month);
+------------+---------------------------------------+
| curdate() | date_add(curdate(), interval 3 month) |
+------------+---------------------------------------+
| 2004-04-21 | 2004-07-21 |
+------------+---------------------------------------+

Ci sono poi funzioni per calcolare i giorni della settimana, la settimana dell’anno, il trimestre, le differenze fra due periodi, e tanto altro ancora. Questa è una sezione del manuale che merita di essere letta con attenzione nella sua interezza, perché ci può risparmiare parecchi fastidi la prossima volta che dobbiamo manipolare qualche data nei nostri programmi.

Nascondere i parametri di connessione con Perl DBI

In un’applicazione Perl, usando il noto modulo DBI, la connessione a un database avviene tramite un’istruzione come questa:


my $dbh = DBI->connect("DBI:mysql:test",
"utente", "password") or die "messaggio di errore";

Il primo parametro è obbligatorio, ed è composto da “dbi”, l’identificativo del modulo, seguito dal driver del database, nel nostro caso “mysql” e il nome del database, in questo esempio “test”. Per un database che si rispetti, occorre anche specificare il nome dell’utente e la password.

Niente da dire se questo script sta al sicuro nella vostra directory, protetta da occhi indiscreti.

Ma cosa succede se dovete distribuirlo a qualcuno per dimostrare qualcosa? Bisogna ricordarsi di cancellare nome utente e password, per evitare il fastidio di doverli poi cambiare nel database se sbadatamente abbiamo distribuito utente e password con l’esempio.

Oppure, se abbiamo un’applicazione CGI, nonostante le protezioni di sistema, il nostro script potrebbe essere sempre alla mercè degli occhi indiscreti di altri utenti che hanno accesso concorrente alla stessa applicazione.

Fortunatamente, esiste una soluzione che nasconde l’accesso alle variabili di connessione, prendendole da un file di configurazione, che può essere tenuto in una directory accessibile solo all’utente che deve eseguire l’applicazione, ma invisibile agli altri.

Funziona così: nella directory dell’utente interessato si costruisce un file di nome “.my.cnf” che contenga queste righe:


[mysql]
user=nomeutente
password=ilmiosegreto

Il file va protetto con gli opportuni privilegi ( chmod 600 .my.cnf) in modo che non possa essere letto da altri utenti. Quindi, si modifica l’istruzione di connessione così:


my $dbh = DBI->connect("DBI:mysql:test"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
undef, undef) or die "messaggio di errore";

L’opzione mysql_read_default_file indica il file da cui prendere le opzioni di connessione. Notate che al posto di nome utente e password ci sono due valori nulli (undef). Il nome del file, in questo caso, viene preso dalla variabile d’ambiente “$HOME” (nei sistemi Unix), ma si può anche indicare il nome espressamente. Questo accorgimento fa sì che lo script possa essere eseguito da più utenti, ciascuno con il suo nome utente e password, senza modificare nemmeno una riga.

L’indice fantasma

Forse non tutti sanno che, per ogni query, MySQL utilizza un solo indice per ogni tabella. Ciò significa che in certi casi il motore risponde più lentamente di quanto ci aspettiamo.

Per esempio, supponiamo di voler elencare tutti gli impiegati che appartengano a un dipartimento specifico o che lavorino in una data località. La ricerca solo per dipartimento (clausola WHERE nel campo id_dip) usa un indice, ed esegue il suo compito efficacemente.

Lo stesso accade se vogliamo cercare per località (indice id_loc). Ma quando proviamo a combinare le due ricerche, MySQL ci annuncia che non riesce a utilizzare nessun indice.


mysql> explain SELECT * FROM persone WHERE id_dip = 3 OR id_loc = 3;
+-------------+------+----------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+------+----------------+------+---------+------+------+-------------+
| persone | ALL | id_dip, id_loc | NULL | NULL | NULL | 2200 | Using where |
+-------------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Se la nostra tabella ha milioni di record, questo tipo di ricerca significa avere un “full table scan”, cioè una lettura sequenziale di tutta la tabella. Il fatto è che quando MySQL analizza la query, tenta di trovare un indice che possa soddisfare entrambe le condizione in WHERE e, siccome gli indici possibili risolvono solo metà delle condizioni, costringendo a una lettura sequenziale per risolvere la seconda parte, il motore decide che è più conveniente abbandonare gli indici del tutto ed eseguire la lettura completa già in partenza.

Ma non ci scoraggiamo. Esiste una semplice soluzione che, tenendo conto della limitazione di un indice per ogni tabella, risolve l’impasse felicemente.


mysql> explain SELECT * FROM persone WHERE id_dip = 3
UNION
SELECT * FROM persone WHERE id_loc = 3;
+---------+------+---------------+---------+---------+-------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+---------+---------+-------+------+-------------+
| persone | ref | id_dip | id_dip | 4 | const | 134 | Using where |
| persone | ref | id_loc | id_loc | 4 | const | 21 | Using where |
+---------+------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Usando una UNION, le query diventano due, consentendo a MySQL di usare un indice per ciascuna. Il risultato è che non viene più effettuata una lettura sequenziale della tabella, sostituita da due letture a indice. Per tabelle di grosse dimensioni il guadagno in termini di efficienza può talvolta misurarsi in minuti!

Supporto completo (outsourcing) dei database MySQL e relativi server

Il servizio di outsourcing di database di Stardata è un impegno completo nella cura, amministrazione, monitoraggio ed assistenza di database MySQL.
Stardata ha una lunga e consolidata esperienza nel trattamento dati, con diversi clienti di classe Enterprise e può liberare le aziende dall’onerosa incombenza di gestire i database MySQL.
Il nostro servizio di outsourcing dei database è una soluzione sicura per i problemi di gestione e manutenzione dei database.
Il servizio di Stardata si applica alla cura di database singoli e replicazioni complesse, instaurando sistemi di monitoraggio e controllo per garantire una piena efficienza dei database continua nel tempo.
Abbiamo soluzioni personalizzate per ogni tipo di azienda. Contattateci!

Analisi delle prestazioni e tuning del database

Comprende una analisi dell’installazione e delle ottimizzazioni del sistema operativo e del server. Seguono una analisi della struttura dati, con normalizzazione (quando occorra) ed una analisi delle query più pesanti o più frequenti per migliorarne le prestazioni.
La durata dipende dalla complessità della struttura dati e può variare da uno a cinque giorni: oltre questo termine, vengono rilasciate le linee guida ed il cliente potrà – se vorrà – continuare l’analisi in maniera indipendente oppure continuare con il supporto di StarData s.r.l.

Un esempio di questa proposta:
La società XYZ gestisce un portale web ad alto traffico: si lamenta del fatto che le prestazioni del DB, all’aumentare del numero di record, sono peggiorate in maniera considerevole.
Anziché migrare su hardware più potente – che non serve risolve nel breve/medio periodo nel tempo, ma solo nel brevissimo, viene commissionato uno studio che analizza la configurazione del server, configurazione del database e dei parametri ad esso associati (spazio per la cache, memoria riservata ecc.), analisi delle query lente, ecc. suggerendo le tecniche migliori per poter incrementare la velocità di accesso ai dati.

Seminario introduttivo su MySQL

È una giornata di presentazione del prodotto, a vari livelli tecnici, indirizzata a far conoscere i vantaggi di MySQL, i possibili utilizzi, gli eventuali benefici apportabili all’azienda. La presentazione è rivolta sia ai quadri aziendali sia ai tecnici. È integrata da dimostrazioni pratiche, casi reali e analisi di prestazioni.
Lo scopo del seminario è quello di mostrare le potenzialità del prodotto e mettendo la dirigenza nella posizione di poter decidere i passi successivi verso la pianificazione della soluzione aziendale.

Un esempio di questa proposta:
La società XYZ decide di valutare altre piattaforme DB oltre a quella presente in azienda: incarica quindi i propri DBA di provare e valutare le altre soluzioni. Anziché perdere diversi giorni nella fase di test di MySQL, di ricerca delle informazioni nella documentazione ufficiale viene richiesto un seminario nel quale si presentano le necessità aziendali e viene valutato come e se MySQL può essere una valida scelta, risparmiando così giorni di prove e test non sempre affidabili, in quanto la conoscenza della piattaforma è appena agli inizi e spesso non sono noti workaround o semplici ottimizzazioni.

Consulenza personalizzata per sistemi Linux e MySQL

Le esigenze dell’azienda possono essere tali da richiedere un intervento mirato alla risoluzione di un particolare problema o alla realizzazione di uno studio che non rientra nei casi normali. I nostri esperti possono affrontare problemi a breve e a lungo termine.
Se avete un sistema già avviato, che richiede solo la risoluzione di un problema specifico per essere perfetto, noi possiamo fornirvi soluzioni adeguate.