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.