SMsoft – informatica e dintorni

varie su OSX, GNU/Linux e l'Open Source

Tag Archives: MySQL

Sottrarre del tempo ad un campo TIME di MySQL

Ipotizziamo di avere un campo chiamato ora di tipo TIME di un database MySQL che si chiama annunci che contiene orari del tipo “13:40:00″ e che tale orario debba essere automaticamente decrementato di due ore per tutti i record. Ecco una query per poterlo fare:

UPDATE annunci SET ora=TIMEDIFF( ora, "02:00:00" )

enjoy

Simulare la clausola SQL IF su SQLite

Se siete abituati ad usare MySQL, sicuramente vi sarà capitato di utilizzare la clausola IF che permette di “popolare” un campo calcolato con un valore che varia in base ad un altro campo. In SQLite purtroppo questa clausola non esiste, ma c’è qualcosa di alternativo, ovvero CASE.
Prendiamo in considerazione una query MySQL che popola il campo calcolato b uguale al valore del campo a se la sua lunghezza è maggiore di 4, altrimenti lo pone vuoto:

SELECT IF( LENGHT( a ) > 4 , a , '') AS b FROM TABLE

l’omonima query in SQLite sarà:

SELECT CASE WHEN LENGTH(a) > 4 THEN a ELSE '' END AS b FROM TABLE

enjoy

Cerca e sostituisci una stringa in una tabella MySQL

Può capitare di dover aggiornare il contenuto una tabella di MySQL, sostituendo solo una eventuale porzione di testo di un campo.
In questo caso è possibile usare la query SQL

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Facciamo un esempio pratico: ipotizziamo di avere una tabella chiamata pagine che contiene degli indirizzi web e che vogliamo sostituire blog.smsoft.it con www.smsoft.it nel campo urls; la query da eseguire sarà:

update pagine set urls = replace(urls,'blog.smsoft.it','www.smsoft.it');

enjoy

Ottimizzare tutte le tabelle MySQL con una query

Per ottimizzare tutte le tabelle di tutti i database, è possibile eseguire la seguente query, avendo chiaramente gli opportuni privilegi su tutti i database:

SELECT CONCAT('OPTIMIZE TABLE ',table_schema, '.',table_name,';')
FROM information_schema.tables where table_type='BASE TABLE';

enjoy

Cambiare la password di root di MySQL (Debian way)

Abbiamo visto in un precedente articolo come modificare la password di root di MySQL nel caso non ce la ricordiamo più. Su Debian tale operazione è ancora più facile. Basta digitare per Lenny:

dpkg-reconfigure mysql-server-5.0

per Squeeze:

dpkg-reconfigure mysql-server-5.1

e specificare la nuova password di root quando richiesta.

enjoy

La dimensione del file ibdata di MySQL non diminuisce dopo cancellazione

Probabilmente vi sarà capitato di controllare la dimensione dei files ibdata nella cartella /var/lib/mysql e vederli crescere durante il popolamento di database InnoDB. Tutto normale, perché i dati dei database impostati con questo formato vengono salvati, di default, in questi files.
Se vi è capitato di cancellare anche dati dal database, avrete notato che la dimensione dei files non viene ridotta!?! Come mai?
Gli sviluppatori di MySQL assicurano che non è un BUG ma una scelta sistemistica che onestamente ha senso se il database ha cambiamenti di dimensione non esagerati, ma nel caso si debba cancellare un database molto grande che non viene sostituito da un altro con dimensioni analoghe, lo spazio su disco resterà occupato senza essere utilizzato…

A quanto pare l’unica soluzione al momento è attivare l’opzione innodb_file_per_table sotto la sezione [mysqld] del file di configurazione my.cnf, che praticamente crea dei files .idb per ogni tabella nella cartella del database e che il server MySQL provvederà a cancellare nel caso il database venga rimosso con il classico “DROP DATABASE ….”.
ATTENZIONE: Ci sono delle procedure più veloci, ma onestamente la seguente mi sembra quella più sicura per non perdere i dati.

Bisogna quindi procedere come segue, avendo i privilegi di root:

Creare il backup di tutti i database con il seguente comando:

mysqldump -R -q -p --all-databases > /tmp/all.sql

Inserire l’opzione innodb_file_per_table nel file my.cnf (su Debian si trova in /etc/mysql/my.cnf ) per avere qualcosa del tipo:

[mysqld]
...
innodb_file_per_table

Fermare il servizio MySQL e rimuovere i files nella cartella di MySQL (su Debian /var/lib/mysql/ ma potrebbe variare per altre distribuzioni)

/etc/init.d/mysql stop
rm -fr /var/lib/mysql/*

Installare i database di default di MySQL

/usr/bin/mysql_install_db

Modificare il proprietario dei files appena creati in modo che l’utente mysql possa accedervi senza limitazioni

chown -R mysql.mysql /var/lib/mysql

Avviare MySQL e poi importare il backup effettuato prima

/etc/init.d/mysql start
mysql < /tmp/all.sql

da notare che non viene richiesta la password di connessione, perché con mysql_install_db l’utente root viene impostato senza password
Riavviare infine MySQL

/etc/init.d/mysql restart

enjoy

Duplicare un database MySQL da riga di comando

E’ possibile duplicare (clonare) un database MySQL da riga di comando effettuando un DUMP e poi ripristinandolo nel nuovo database.
Come sicuramente sapete, Linux consente di concatenare i comandi e pertanto basterà semplicemente eseguire:

mysqldump  -uuser -ppass [-h host_db] db_name | \
 mysql -uuser1 -ppass1 [-h host_db1] db_name1

dove:
user: username utente DB di cui fare il dump
pass: password utente DB di cui fare il dump
db_name: DB di cui fare il dump
[-h host_db]: parametro opzionale se si vuole usare un server “esterno”
user1: username utente DB in cui reimportare il dump
pass1: password utente DB in cui reimportare il dump
db_name1: DB in cui reimportare il dump
[-h host_db1]: parametro opzionale se si vuole usare un server “esterno”

Chiaramente il database di destinazione deve essere già esistente, altrimenti lo si può creare con:

mysqladmin create db_name1 -uuser1 -ppass1 [-h host_db1]

enjoy

Page optimized by WP Minify WordPress Plugin