Visualizzazione post con etichetta MySql. Mostra tutti i post
Visualizzazione post con etichetta MySql. Mostra tutti i post

venerdì 25 novembre 2011

MySql creare backUp con data usando mysqldump su Windows

Per effettuare un back up di un db con una data (in questo caso con l'indicazione di giorno e ora) su windows si può creare un .bat di questo tipo:  

set giorno=%date:~0,2%
set mese=%date:~3,2%
set anno=%date:~6,4%
set ora=%time:~0,2%_%time:~3,2%_%time:~6,2%
set miadata=%giorno%_%mese%_%anno%_Ore_%ora%
mysqldump -uroot -proot test>c:\test_%miadata%.sql



Il risultato sarà un file di back up di questo tipo:test_25_11_2011_Ore_10_18_34.sql

giovedì 22 settembre 2011

Mysql dump e restore

Per effettuare dump db da linea di comando

mysqldump -h[IP_SERVER] -u[UTENZA] -p[PASSWORD] [NOME_DB]> [PERCORSO_E_NOME_FILE]

Per il restore 

mysql  -h[IP_SERVER] -u[UTENZA] -p[PASSWORD] [NOME_DB]<[PERCORSO_E_NOME_FILE]








mercoledì 14 settembre 2011

File lbdata1 di Mysql


Quando si usa lo storage engine di MySql InnoDb (che ricordiamo è lo SE che consente il supporto da parte di MySql delle transazioni ACID) le impostazioni di default prevedono che tutti i dati delle tabelle siano salvati su questo file lbdata1.
Per trovare il file bisogna andare sulla datadir di Mysql, che possiamo cercare così:


mysql> show variables like '%datadir%'\G
*************************** 1. row ***************************
Variable_name: datadir
        Value: C:\Documents and Settings\All Users\Dati applicazioni\MySQL\MySQL Server 5.5\Data\
1 row in set (0.00 sec)
 










Nella data_dir ci sono tante directory quanti sono i database presenti sul server.
Per i database che utilizzano innodb come storage engine all’interno delle directory del db di default sono presenti i file .frm con le informazioni sulla struttura delle tabelle, a livello della datadir è invece presente il file lbdata1 che contiene sia i dati che gli indici delle tabelle.

La crescita incontrollata di dimensioni del file può portare a problemi di gestione da parte del sistema operativo.
E’ possibile disabilitare questa impostazione e fare in modo che i file siano generati per ogni tabella.
Per fare ciò è sufficiente modificare il  file my.ini presente sotto

[DIR_INSTALLAZIONE_MYSQL]\MySQL\MySQL Server 5.5

Inserendo nella sezione [mysqld] la variabile innodb_file_per_table

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

innodb_file_per_table
 



In questo modo tutte le nuove tabelle create saranno salvate all’interno della directory specifica del database una per ogni tabella.
I file specifici hanno l’estensione .ibd.


lunedì 12 settembre 2011

Indici non unique non presenti nell'information_schema di MySql

La tabella key_column_usage di MySql ci consente di vedere per ogni tabella quali sono le sue Primary Keys, i suoi indici unique e le Foreign Key, tuttavia mancano gli indici non unique.
Ho visto qui  che c'è un bug segnalato dal 2005 in merito a questa mancanza, ma non è stato mai risolto ad oggi.
L'unico modo per ottenere questa informazione è eseguire la seguente query:

show indexes from [NOME_TABELLA] where non_unique=1




mercoledì 7 settembre 2011

Trovare tutte le tabelle che hanno FK verso un'altra tabella

Alle volte è utile conoscere quante tabelle hanno FK verso una tabella X, soprattutto se è necessario per qualche motivo effettuare una delete di un record dalla tabella X e se nel db è applicata la regola dell'integrità referenziale.
Per ottenere questa informazione si può interrogare il db information_schema effettuando la seguente query

select table_schema as 'Db',table_name as 'Tabella Referenziante',referenced_table_name as 'Tabella Referenziata',
column_name as 'Colonna Regerenziante',referenced_column_name as 'Colonna Referenziata'
 from key_column_usage where constraint_schema='[NOME_DB]' and referenced_table_name='[TABELLA_X]'
 and referenced_column_name='[PK_TABELLA_X]';





mercoledì 31 agosto 2011

Formattare date con MySql



In MySql le date hanno questo formato: AAAA-MM-GG, mentre i datetime AAAA-MM-GG OO:MM:SS (i millisecondi non sono ancora gestiti, nemmeno dal campo timestamp)

Sono due le funzioni per giocare con le date in MySql:

  • DATE_FORMAT che serve per formattare la data dal formato di MySql a quello che vogliamo noi;
  • STR_TO_DATE che serve ad eseguire l’operazione contraria, ossia data la nostra stringa con la data rappresentata secondo le nostre esigenze la converte nel formato che piace a MySql.


DATE_FORMAT (data da formattare,pattern)

La funzione prense in input la data o il timestamp in formato MySql e restituisce un varchar con la formattazione desiderata esplicitata nel pattern.
Il pattern è una stringa con caratteri speciali preceduti dal %.

Vediamo la lista completa di questi caratteri speciali, presa dalla documentazione ufficiale di MySql:

           
CARATTERE
DESCRIZIONE
%a
Giorno della settimana abbreviato (Sun..Sat)
%b
Giorno del mese abbreviato (Jan..Dec)

%c
Mese numerico (0..12)
%D
Giorno del mese con suffisso inglese (0th, 1st, 2nd, 3rd, …)

%d
Giorno del mese numerico (00..31)
%e
Giorno del mese numerico (0…31)
%f
Microsecondo (000000..999999)
%H
Ora (00,…23)
%h
Ora (01,….12)
%I
Ora (01,….12)
%i
Minuti (00,..59)
%j
Giorno dell’anno (00,…366)
%k
Ora (00,….23)

%l
Ora (1,…12)
%M
Nome del mese (January..December)
%m
Nome del mese (00,..12)
%p
AM o PM
%r
Hh:mm:ss seguito da AM o PM
%S
Secondi (00,..59)
%s
Secondi (00,..59)
%T
Orario (hh:mm:ss con le 24 ore)
%U
Settimana (00..53) dove Domenica è il primo giorno della settimana
%u
Settimana (00..53) dove Lunedì è il primo giorno della settimana
%V
Settimana (01..53) dove Domenica è il primo giorno della settimana, è usato assieme alla %X
%v
Settimana (01..53) dove Domenica è il primo giorno della settimana, è usato assieme alla %x
%W
Giorno della settimana (Sunday…Saturday)
%w
Giorno della settimana (0= Sunday,….6=Saturday)
%X
Anno per la settimana dove Domenica è il primo giorno  della settimana, numerico, 4 cifre usato con %V
%x
Anno per la settimana dove Domenica è il primo giorno  della settimana, numerico, 4 cifre usato con %v
%Y
Anno, numerico, 4 cifre
%y
Anno, numerico 2 cifre



Esempi:

select DATE_FORMAT(current_timestamp,'%d-%m-%Y %h:%i:%s') as 'data formattata';

Produce '31-08-2011 12:13:07'

select DATE_FORMAT(current_timestamp,'%d-%m-%Y %r') as 'data formattata';

Produce '31-08-2011 12:14:21 PM'


STR_TO_DATE (stringa_da_formattare,pattern)
  
Questa funzione torna una data in formato mysql data la nostra stringa e specificato il pattern di decifrazione della stessa.
Le regole per il pattern fanno riferimento alla tabella prima descritta.

Esempi:

select STR_TO_DATE('31/08/2011','%d/%m/%Y');

Produce '2011-08-31'


select STR_TO_DATE('31/08/2011 Ora:12 Minuto:23 Secondo:50 PM','%d/%m/%Y Ora:%h Minuto:%i Secondo:%s %p');

Produce: '2011-08-31 12:23:50'

martedì 2 agosto 2011

MySql replace con RegExp


MySql purtroppo non possiede una funzione di tipo replace abbinata ad una regular expression, come altri db.
Mi è capitato di dover effettuare una bonifica su una tabella che aveva un campo di tipo text con una serie di indirizzi mail separati dal carattere ;.

La modifica consisteva nel rimpiazzare tutti gli indirizzi di un certo dominio (poniamo ad esempio @xyz ) con una mail precisa, lasciando ovviamente inalterati gli altri indirizzi

Pensavo quindi di utilizzare una funzione del tipo replace(VALORE_CAMPO,’.*@xyz’,’mioindirizzo@xyz’), utilizzando quindi una regular expression per fare il match.
Purtroppo invece la replace di Mysql non suppporta regular expression.

Ho quindi dovuto realizzare il seguente work around con 3 stored procedures:

  1. La stored sprocedure split che effettua lo split dei valori in base al ;, inserendo i record risultanti su tabella (MySql non dispone di una funzione split);
  2. La stored procedure sp_lavoraMessaggio che dato in input l’id Primary Key della tabella dei messaggi da modificare chiama la procedura split sul campo degli indirizzi mail, quindi sulla tabella creata da split effettua le replace del caso e poi recupera tutti i dati dalla tabella di appoggio per effettuare l’update nel campo text della tabella da bonificare;
  3. La stored procedure cleanMail che legge dalla tabella da bonificare tutte le occorrenze da modificare e quindi per ogni occorrenza richiama la stored procedure del punto 2

Di seguito posto il codice delle 3 stored procedures:


Split


DELIMITER $$

DROP PROCEDURE IF EXISTS `split` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `split`(input text, delim VARCHAR(10))
begin
/** SCRIVE I DATI SPLITTATI SU UNA TABELLA DI APPOGGIO, CHE OGNI VOLTA DROPPA E RICREA **/
declare foundPos tinyint unsigned;
declare tmpTxt text;
declare delimLen tinyint unsigned;
declare element text;

drop  table if exists tmpValues;
create  table tmpValues
(
`valori` text not null
) engine = MyIsam;

set delimLen = length(delim);
set tmpTxt = input;

set foundPos = instr(tmpTxt,delim);
while foundPos <> 0 do
set element = substring(tmpTxt, 1, foundPos-1);
set tmpTxt = replace(tmpTxt, concat(element,delim), '');


insert into tmpValues (`valori`) values ( element);

set foundPos = instr(tmpTxt,delim);
end while;

if tmpTxt <> '' then
insert into tmpValues (`valori`) values (tmpTxt);
end if;


END $$

DELIMITER ;


sp_lavoraMessaggio





DELIMITER $$

DROP PROCEDURE IF EXISTS `db`.`sp_lavoraMessaggio` $$
CREATE PROCEDURE `db`.`sp_lavoraMessaggio` (in idMessaggio int)
BEGIN
declare listaDestinatariAppo text;
declare listaDestinatariTable text;
declare listaDestinatari text;
DECLARE doneCursorValori INT DEFAULT 0;
declare cursorValori cursor for select concat(valori,';') from tmpvalues;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneCursorValori = 1;
set listaDestinatari="";
select destinatari into listaDestinatariTable from messaggio_email where id_messaggio=idMessaggio;
call split(listaDestinatariTable,';');
update tmpValues set valori=’mioindirizzo@xyz.it'
where valori like '%@xyz%';
open cursorValori;
/** QUI SCORRE I VALORI DALLA TABELLA SPLITTATA E LI ACCODA ALLA NUOVA LISTA DESTINATARI **/
repeat
fetch cursorValori into listaDestinatariAppo;
if not doneCursorValori then
set listaDestinatari=concat(listaDestinatari,listaDestinatariAppo);
end if;
until doneCursorValori end repeat;
close cursorValori;
/** A QUESTO PUNTO EFFETTUA UPDATE, TOGLIENDO L’ULTIMO ; ALLA LISTA PRECEDENTEMENTE OTTENUTA **/
update messaggio_email
set destinatari=substring(listaDestinatari,1,char_length(listaDestinatari)-1)
where id_messaggio=idMessaggio;

END $$

DELIMITER ;


cleanMail


DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_cleanMail` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_cleaniMail`()
BEGIN
declare idMessaggio int;
declare cont int;
DECLARE doneIdMessaggi INT DEFAULT 0;
/**        RECUPERO MESSAGGI DA BONIFICARE **/
declare  cursoreMessaggi  cursor for select id_messaggio from messaggio_email where data_invio is null;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneIdMessaggi = 1;
set cont=0;
open cursoreMessaggi;
repeat

fetch cursoreMessaggi into idMessaggio;
if not doneIdMessaggi then

call sp_lavoraMessaggio(idMessaggio);
set cont=cont+1;
end if;
UNTIL doneIdMessaggi END REPEAT;
close cursoreMessaggi;
select concat('Procedura terminata con successo, messaggi lavorati: ',cont);
END $$

DELIMITER ;



mercoledì 27 luglio 2011

MySql modificare un trigger

In questo post vediamo una caratteristica (piuttosto scomoda) di Mysql, ossia il fatto che un trigger , una volta creato, non è  più modificabile.
Praticamente non esiste una istruzione del tipo “alter trigger”e bisogna necessariamente cancellarlo (drop trigger [nome_trigger]) e ricrearlo.
Vediamo un esempio completo

NOTA: In Mysql , quando si lancia uno script per creare una tabella oppure una stored procedure oppure un trigger, il carattere “;” è utilizzato come delimitatore standard.
Praticamente se si lancia uno script al primo ;  Mysql ritiene chiuso lo stesso script; per questo motivo usando le Stored Procedure oppure i trigger da riga di comando è necessario cambiare i delimitatori.

Data una semplice tabella anagrafica con questa struttura:


CREATE TABLE  `actionbazaar`.`anagrafica` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(45) NOT NULL,
  `cognome` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 


Vogliamo realizzare un trigger che, per ogni delete sulla tabella anagrafica vada a scrivere il record di cancellazione su una  tabella così definita.




CREATE TABLE  `actionbazaar`.`anagrafica_clone` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(45) NOT NULL,
  `cognome` varchar(45) NOT NULL,
  `cancellazione` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 












Il nostro trigger sarà definito in questo modo:


delimiter $$
create trigger tr_anagrafica_canc
after delete on anagrafica
for each row
begin
insert into actionbazaar.anagrafica_clone(nome,cognome)
values(old.nome,old.cognome);
end $$
delimiter ;

 







Per “ritrovare” la sintassi di creazione del trigger lanciare il comando show triggers,
nel nostro caso avremo


mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: tr_anagrafica_canc
               Event: DELETE
               Table: anagrafica
           Statement: begin
insert into actionbazaar.anagrafica_clone(nome,cognome)
values(old.nome,old.cognome);
end
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)
 




Dobbiamo quindi salvarci queste informazioni, effettuare la drop trigger tr_anagrafica_canc e successivamente ricrearlo con le opportune modifiche.

mercoledì 13 luglio 2011

Gestire campo TIMESTAMP con MySQL

Al campo TIMESTAMP di MySql è possibile definire, in fase di creazione della tabella, la clausola DEFAULT CURRENT_TIMESTAMP.
Con questa specifica ogni volta che si effettua una insert sulla tabella il valore preso sarà il timestamp corrente (aggiornato al secondo, purtroppo anche con la versione 5.5 MySql non gestisce i millisecondi), e non è ovviamente necessario specificare il campo nella insert.
E' possibile anche specificare la clausola ON UPDATE CURRENT_TIMESTAMP, in modo che ad ogni update (di qualsiasi campo della tabella) automaticamente sia inserito il timestamp corrente.
Si noti che se si utilizzano i tool per creare la tabella e si specifica soltanto che sia not null il timestamp di default prende tutte e due le clausole DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Il classico errore che si fa è specificare il campo timestamp come not null via tool, valorizzarlo nella insert da applicazione e poi "fare affidamento" su questo campo per avere informazioni su quando è stato inserito il record.
Per quanto prima descritto invece ogni volta che si effettuerà un update su quella tabella avremo il campo timestamp modificato, se volevamo un campo che tenesse traccia solo dell'inserimento avremmo dovuto definirlo come DEFAULT CURRENT_TIMESTAMP e basta.
Per gestire al meglio l'esigenza di avere un campo con il timestamp di inserimento ed uno con quello di modifica è consigliato dallo stesso manuale di certificazione MySql 5.0 il seguente "trucco".
Si crea una tabella in questo modo:


CREATE TABLE `test`.`testTimestamp` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `valore` varchar(100),
  `ts_insert` TIMESTAMP NOT NULL default 0,
  `ts_update` TIMESTAMP NOT NULL on update current_timestamp,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
 






Si noti che il campo ts_insert, che dovrà tenere traccia dell’inserimento, è stato definito not null default 0, mentre il ts_update not null on update current_timestamp.

A questo punto in fase di inserimento dobbiamo scrivere



insert into testtimestamp(valore,ts_insert) values ('valore di test',current_timestamp);
 


Il risultato sarà il seguente:



mysql> select * from testtimestamp \G
*************************** 1. row ***************************
       id: 2
   valore: valore di test
ts_insert: 2011-07-13 12:40:36
ts_update: 0000-00-00 00:00:00
1 row in set (0.00 sec)
 


Come si può notare è stato valorizzato soltanto il campo timestamp  ts_insert.

A questo punto eseguendo un update:


update testtimestamp set valore='valore di test updated' where id=2;
 


Avremo ottenuto il nostro risultato. Infatti rieseguendo la query sulla tabella avremo:


mysql> select * from testtimestamp \G
*************************** 1. row ***************************
       id: 2
   valore: valore di test updated
ts_insert: 2011-07-13 12:40:36
ts_update: 2011-07-13 12:47:36
1 row in set (0.00 sec)
 


Come possiamo notare non è stato modificato il ts di inserimento ma solo quello di update.