Trigger su SELECT

I trigger sono aggeggi da usare con cautela. Se abusati, possono portare la vostra applicazione in ginocchio. Tuttavia, sono anche fantastici strumenti per raffinare il controllo in situazioni difficili. Secondo lo standard SQL, i trigger possono essere associati solo alle operazioni di modifica (INSERT, DELETE, UPDATE). E i SELECT, allora? Non sarebbe bello se si potesse instaurare un trigger anche per ogni SELECT?

Supponiamo di avere una tabella books e che vogliamo creare una lista personalizzata degli accessi solo per quella tabella. Ecco come si può fare:

  1. Create una tabella db_counter che conterrà il numero degli accessi, e una tabella db_log che conterrà i dettagli;
  2. Create una funzione che contenga tutte le operazioni che volete eseguire per ogni SELECT. Questa funzione deve restituire un intero positivo;
  3. Create una vista aggiornabile sulla vostra tabella, aggiungendo una clausola WHERE che usa la funzione appena creata.

Andiamo a incominciare:


-- ----------------------------------------------------
-- La tabella originale. Quella per cui vogliamo il log
-- La facciamo semplice per non appesantire l'esempio
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title VARCHAR(40),
pages INT,
price DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id)
-- ,FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- Il contatore
-- Contiene solo una riga.
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- L'elenco di dettaglio.
-- Ogni riga contiene informazioni sull'orario ,
-- sull'operazione fatta e l'utente.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

Queste ultime due tabelle contengono le informazioni che ci servono. Prima di continuare, qualche ritocco per essere sicuri che la tabella del contatore possa contenere solo una riga:


CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;
CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

E ora alcuni trigger per registrare le operazioni di modifica nella tabella books. (Sarebbe buffo che registriamo gli accessi in lettura e trascuriamo quelli in scrittura!)


CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');
CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');
CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

Siamo pronti per creare la funzione:


delimiter //
drop function if exists get_counter //
create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //
delimiter ;

Infine, creiamo la vista aggiornabile per la tabella books.


CREATE VIEW mybooks as
SELECT * from books WHERE get_counter() > 0;

Ora, usando mybooks invece di books, ogni accesso a books verrà registrato. Vediamo in azione il nostro meccanismo (notate che usiamo sempre la vista, non la tabella).


gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)
gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 2 |
+----+---------+
1 row in set (0.00 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert |
| 2005-10-24 11:14:27 | gmax@localhost | insert |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

Dopo due inserimenti, il contatore è a due, e la tabella dei dettagli ha due record. Proviamo con il nostro obiettivo principale, cioè una SELECT:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert |
| 2005-10-24 11:17:23 | gmax@localhost | insert |
| 2005-10-24 11:18:08 | gmax@localhost | select |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 3 |
+----+---------+
1 row in set (0.00 sec)

CVD

Dov’è il NULL?

Nell’esaminare dati creati da altri, o dati creati da noi stessi in tempi lontani, spesso accade di volerne ottimizzare la struttura, per migliorare l’efficienza. Un caso frequente è la ricerca di campi di chiavi esterne con valori NULL, per poter normalizzare le dipendenze da altre tabelle.

Ma comunque i campi NULL possono, quando usati in maniera irragionevole, influenzare negativamente la precisione o l’efficienza di una query. Quindi succede spesso che un programmatore si chieda: “in quali campi di questa tabella c’è almeno un NULL?”

Se la tabella è piccola, basta uno sguardo.


mysql> select * from persone;
+----+--------+---------+--------+
| id | nome | cognome | id_dep |
+----+--------+---------+--------+
| 1 | Fabio | Rossi | NULL |
| 2 | Mario | Bianchi | NULL |
| 3 | Carlo | Verdi | NULL |
| 4 | Gino | Neri | NULL |
| 5 | Ugo | Rossi | 2 |
| 6 | Piero | Verdi | 1 |
| 7 | Fulvio | Neri | NULL |
| 8 | NULL | Rossi | NULL |
| 9 | NULL | Bianchi | NULL |
+----+--------+---------+--------+
9 rows in set (0.02 sec)

Ma se la tabella ha decine di colonne e centinaia di record, il compito di esaminarla a mano è proibitivo. Tuttavia, possiamo agevolarci il compito con qualche query ad hoc. Sappiamo che la funzione COUNT restituisce un conteggio dell’elemento richiesto. Nella tabella sopra una query


mysql> SELECT count(id_dep) FROM persone;

restituisce 2. Questo ci dice che il conteggio salta i valori NULL. Se diamo alla funzione l’elemento ‘*’, però, ci viene restituito un conteggio dei record, a prescindere dal contenuto. Verifichiamo questo fatto.


mysql> select * from tuttinull;
+------+------+
| id | nome |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.01 sec)

Questa tabella ha quattro record, con NULL in tutti i campi.


mysql> select count(*) from tuttinull;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)

Il conteggio, come annunciato, è riferito ai record. Quindi possiamo sfruttare questo fatto per vedere quali campi contengono un NULL, esaminando il conteggio dei singoli campi, e comparandolo al conteggio totale:


mysql> select count(*), count(nome), count(cognome), count(id_dep) from persone;
+----------+-------------+----------------+---------------+
| count(*) | count(nome) | count(cognome) | count(id_dep) |
+----------+-------------+----------------+---------------+
| 9 | 7 | 9 | 2 |
+----------+-------------+----------------+---------------+
1 row in set (0.02 sec)

Questo ci dice che ci sono 7 nomi su 9 record, 9 cognomi su 9, e 2 id_dep su 9. Ancora meglio, possiamo chiedere al database di fare il calcolo per noi, e darci direttamente il numero di record mancanti:


mysql> select count(*),
-> count(nome) as nome,
-> count(*) - count(nome) as `NULL nome`,
-> count(cognome) as cognome,
-> count(*) - count(cognome) as `NULL cognome`,
-> count(id_dep) as id_dep,
-> count(*) - count(id_dep) as `NULL id_dep`
-> from persone;
+----------+------+-----------+---------+--------------+--------+-------------+
| count(*) | nome | NULL nome | cognome | NULL cognome | id_dep | NULL id_dep |
+----------+------+-----------+---------+--------------+--------+-------------+
| 9 | 7 | 2 | 9 | 0 | 2 | 7 |
+----------+------+-----------+---------+--------------+--------+-------------+
1 row in set (0.02 sec)

Questo metodo funziona, ma se abbiamo una tabella con tanti campi, non è più un metodo efficiente. È possibile però ottenere lo stesso risultato con un approccio programmatico, come questo esempio in Perl dimostra:


#!/usr/bin/perl -w
use strict;
use DBI;


my $dbh = DBI->connect('dbi:mysql:test', 'utente','password')
or die "problema di connessione\n";
my $colonne = $dbh->selectcol_arrayref(qq{describe persone})
or die "colonne non trovate";


my $query = qq{select count(*), }
. join(",", map {qq{count(`$_`) as `$_`,
count(*) - count(`$_`) as `NULL $_`} } @$colonne)
. qq{ from persone};


my $sth = $dbh->prepare($query);
$sth->execute() or die "problema di esecuzione\n";


my $row = $sth->fetchrow_hashref;
printf "%-28s => %s \n", $_, $row->{$_} for @{$sth->{NAME}};
$sth->finish();

Col risultato:


count(*) => 9
id => 9
NULL id => 0
nome => 7
NULL nome => 2
cognome => 9
NULL cognome => 0
id_dep => 2
NULL id_dep => 7

Come incrementare un campo con chiave univoca

La domanda viene da uno scorato frequentatore di una mail list: Ho una tabella InnoDB con una chiave univoca sul campo numerico ID. Devo incrementare il campo di cinque unità, ma i miei tentativi non hanno dato frutto. Infatti, se provo a usare la formula SET ID = ID + 5, ottengo un errore di violazione di chiave. Questa è l’istruzione che ho tentato


BEGIN;
UPDATE nometabella SET ID = ID + 1;
COMMIT;

Credevo che InnoDB avrebbe contenuto l’errore per ogni singola violazione, e aggiornato tutta la tabella, e solo dopo avrebbe considerato che in realtà non c’è conflitto perché dopo che tutti i record sono stati aggiornati si ritorna a valori univoci. La mia tabella ha 12000 record. Non vorrei essere costretto a usare 12.000 istruzioni di UPDATE. Cosa posso fare?

Questa è stata la mia risposta:

InnoDB fa il suo mestiere, fermandosi al primo errore. Il meccanismo transazionale fa sì che la serie di istruzioni venga interrotta al primo errore, dando la possibilità al programmatore di chiamare un ROLLBACK. Quindi, al primo record, ci sarà un conflitto di valori che renderà impossibile l’aggiornamento.

Tuttavia, esiste una soluzione elegante, senza dover inviare 12.000 richieste. Basta chiedere l’aggiornamento con ordinamento decrescente:


UPDATE nometabella SET ID = ID + 1 ORDER BY ID DESC;

In questo modo, il motore aggiornerà la tabella in ordine decrescente di ID, garantendo che non ci saranno conflitti. Se invece si volesse decrementare il valore, basta usare l’ordinamento crescente (omettendo la clausola DESC).

Ordinare per condizioni trovate

FN mi ha proposto un problema interessante. Deve ricercare un archivio usando tre condizioni unite dall’operatore OR, e gli serve che il risultato sia ordinato in modo che i record che rispondono alla prima condizione vengano per primi, poi quelli della seconda condizione, e infine quelli della terza.

Lui si è arenato alla semplice query senza ordinamento.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE
nome LIKE 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| John | 5550 | 1955-04-02 |
| Maria | 5700 | 1979-12-11 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.00 sec)

Così a prima vista io ho tirato fuori una soluzione che sembra fare al caso suo. Basta ordinare con una funzione CASE che assegna un valore a ogni condizione.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000
ORDER BY
CASE
WHEN nome LIKE 'M%' THEN 1
WHEN DDN < '1960-01-01' THEN 2 WHEN stipendio > 6000 THEN 3
END;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| Maria | 5700 | 1979-12-11 |
| John | 5550 | 1955-04-02 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.01 sec)

Il brutto di questo approccio, mi fa gentilmente notare FN, è che le condizioni vengono valutate due volte, e anche se questo avviene solo per i record trovati (la clausola ORDER BY viene eseguita dopo che i record sono stati filtrati) è sempre uno spreco e un rallentamento.

Pertanto, concentrandomi sull’efficienza, ho trovato un’altra soluzione:


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
UNION
SELECT nome, stipendio, DDN
FROM impiegati
WHERE DDN < '1960-01-01' UNION SELECT nome, stipendio, DDN FROM impiegati WHERE stipendio > 6000 ;

Questa query risolve il problema dell’inefficienza dell’operatore OR con campi diversi, e inoltre risolve anche il problema principale dell’ordinamento. Benché teoricamente l’ordine del risultato di una query sia imprevedibile, il modo in cui MySQL organizza i dati in una UNION fa sì che l’ordinamento sia quello voluto. Si ricordi che una UNION senza la clausola ALL elimina i record duplicati, per cui se un record viene ritrovato grazie alla prima condizione, verrà scartato quando sono valutate la seconda e la terza.
Risultato ottenuto, e ottimizzato allo stesso tempo!

Creare un mysqldump con LIMIT

Potrebbe essere necessario a volte creare un campione dei dati di uno o più database, per esempio per poter chiedere assistenza a un consulente o per poter dare assistenza a un utente.

In questi casi, il classico strumento mysqldump non aiuta, perché fra le sue varie utilità non c’è quella di impostare una clausola LIMIT. È vero che si può filtrare con WHERE, ma questo è valido solo se si vogliono estrarre i dati di una tabella alla volta e anche in questo caso la clausola WHERE dovrebbe essere cambiata volta per volta.

Esistono alternative e la più spiccia è quella di creare un programma di estrazione in Perl, per poter prendere con un colpo solo la struttura e N record di tutte le tabelle di tutti i database.

Il semplice script che segue è una semplificazione (ma perfettamente funzionante) di uno strumento analogo che viene usato in StarData per acquisire campioni di dati dagli utenti.

Per limitare la portata dell’output, qualora non si volesse fornire la lista di tutti i database, è sufficiente eseguire questo script con i privilegi di un utente abilitato solo alla visione di alcuni database.


#!/usr/bin/perl -w
use strict;
use DBI;


my $host = 'localhost';
my $user = 'gmax'; # cambiare
my $password = ''; # cambiare
my $port = "3306";
my $limit = 1;
my $db = "test";
my $DSN = "DBI:mysql:$db;host=$host;port=$port;"
. "mysql_read_default_file=$ENV{HOME}/.my.cnf";


my $dbh =
DBI->connect( $DSN, $user, $password, { RaiseError => 1,
PrintError => 1 } )
or die "$DBI::errstr\n";


for my $db (@{$dbh->selectcol_arrayref("SHOW DATABASES})")
{
print STDERR "# ------ DATABASE $db\n";
print "# ------ DATABASE $db\n";
print "CREATE DATABASE IF NOT EXISTS $db;\n";
print "USE $db;\n";
for my $table (@{$dbh->selectcol_arrayref("SHOW TABLES FROM `$db`")})
{
print "# TABELLA ($db) $table\n";
print STDERR "# TABELLA ($db) $table\n";
my $sth = $dbh->prepare(qq{SHOW CREATE TABLE `$db`.`$table`});
$sth->execute ;
my ( $null, $create ) = $sth->fetchrow_array;
$sth->finish;
$create =~ s/(?<=CREATE TABLE)/ IF NOT EXISTS /; print " $create;\n"; $sth = $dbh->prepare(
"SELECT * FROM `$db`.`$table` LIMIT $limit");
$sth->execute;
while ( my $rec = $sth->fetchrow_arrayref )
{
print qq{INSERT INTO `$db`.`$table` (};
print join ",", map { "`$_`" } @{ $sth->NAME} };
print ") VALUES (";
print join ( ",", map( { $dbh->quote($_) } @$rec ) );
print ");\n";
}
}
}

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.