Esportare ed importare un database Mysql grande

L'altro giorno ho avuto la necessità di migrare un database mysql da un server ad un altro.
Normalmente, se i dati non sono troppi, è possibile compiere questa operazione esportando ed importando dei dump da phpmyadmin, se però il database è grosso diversi giga e contiene file blob per gli allegati, allora questa procedura non è utilizzabile.
Per fortuna però, è possibile esportare una copia esatta del database da riga di comando, salvando già direttamente il file sul nuovo server.
Vediamo subito come fare.

Come esportare un database Mysql grande da riga di comando

Per poter clonare un database mysql, facendo un dump puntuale dei dati, basta seguire i seguenti passaggi:

1. Aprire il prompt dei comandi di dos (se si è su windows)
2. Posizionarsi nel percorso in cui è presente l'eseguibile di mysql con il comando:
   cd C:\wamp64\bin\mysql\mysql8.0.16\bin
   Dove ovviamente va indicato il path del proprio mysql
3. Digitare il seguente comando e premere invio:
   mysqldump -u nome-utente -p nome-db > C:\percorso\file.sql 

Verrà chiesto di inserire la password dell'utente sopra indicato, e dopo aver premuto invio occorrerà aspettare pazientemente che l'operazione vada a termine, anche se a video apparentemente non succede niente.
Quando la copia del database mysql sarà terminata, il cmd restituirà semplicemente la possibilità di digitare altri comandi, senza un vero e proprio messaggio di conferma copia avvenuta, se però andrete a vedere nel percorso indicato, troverete il file con estensione .sql contenente la copia del database mysql.

Come importare un database Mysql grande da riga di comando

Per importare un database mysql grosso, dopo averlo esportato con la comoda funzione mysqldump sarà necessario importarlo nel nuovo server con il seguente comando:

mysqldump -u nome-utente -p test > C:\percorso\file.sql

Come nel caso dell'import, il prompt dei comandi risulterà bloccato fino a che l'operazione non sarà terminata (e a seconda del peso potrebbero volerci anche molti minuti), ma una volta finito, il database mysql sul nuovo server sarà identico a quello del server di provenienza e potrete sfogliarlo e fare le verifiche del caso con phpmyadmin.

Questa era la procedura corretta per duplicare un database mysql pesante.
Non provate invece a copiare brutalmente i file contenuti nella cartella data di mysql, perchè la cosa potrebbe non funzionare correttamente anche se prima stoppate mysql.

Da ora in poi l'export e l'import di database mysql grandi non sarà più un problema, basta solo avere una buona banda ed un po' di pazienza.

Rifiuto persistente del computer di destinazione (Mysql)

L'altro giorno, dopo aver reinstallato da capo wamp su un nuovo computer sono incappato in un problema relativo a mysql che non mi era mai capitato prima.
Praticamente, nonostante la mia app php fosse rimasta invariata e nonostante avessi installato e configurato correttamente il database, il sito a cui cercavo di accedere mi dava il seguente errore:
Warning: mysqli::__construct(): (HY000/2002): Impossibile stabilire la connessione. Rifiuto persistente del computer di destinazione.

Dopo aver cercato un po' online, ho trovato la soluzione a questo errore di connessione a mysql, ed ora lo condividerò in questo breve post per i posteri.

Come risolvere l'errore mysql su wamp "Rifiuto persistente del computer di destinazione"

Per chi non lo sapesse, wamp sta per windows apache mysql e php, ed il problema riscontrato che generava questo fatidico messaggio di impossibilità di connessione al database, era legato proprio ad uno di questi strumenti.

In prima battuta, e lo scrivo per i posteri perchè potrebbe essere un metodo di risoluzione per l'errore mysql "Rifiuto persistente del computer di destinazione", ho provato a seguire la seguente procedura:

1. Stoppare mysql (banalmente, tasto sinistro del mouse sull'icona di wamp, poi stop all services)
2. Andare nella cartella C:\wamp64\bin\mysql\mysql8.0.18\data (dove il percorso ovviamente cambia in base a dove avete installato wamp ed alla versione di mysql) e cancellare i file (prima farne un backup di sicurezza): auto.cnf, ib_logfile0 e ib_logfile1.
3. Riavviare wamp (tasto sinistro restart all services)

Questa soluzione risolve la problematica tipica legata a questo genere d'errore.

Nel mio caso però, il problema era un altro, dato che con phpmyadmin la connessione al db funzionava mentre con il mio script php no.
L'indizio principale lo dava la latenza prima dell'errore, che in questo caso può stare a significare che la porta usata da mysql era bloccata dal firewall (nel mio caso windows firewall).

Quindi, per risolvere il terribile errore "Rifiuto persistente del computer di destinazione" ci sono due strade.
La prima consiste nell'aprire la porta usata da mysql (3306 o 3308) andando ad agire sulle regole del firewall.
La seconda invece è usare una porta aperta, ovvero nel mio caso, cambiare la porta usata da mysql da 3306 a 3308.

Come faccio a sapere che la porta 3308 va e la 3306 no? Semplice, come dicevo poco sopra, phpmyadmin funzionava, mentre il mio script php no, ed andando a vedere le impostazioni del primo strumento ho scoperto che questo usava proprio la 3308, mentre il php usava di default la 3306.

Ora quindi, o si risolve agendo direttamente sui file di configurazione di mysql e del php.ini di wamp, oppure più banalmente tramite script php, con una sintassi del genere:

$mysqli=new mysqli($server.":".$porta,$user,$pwd,$db);

Dove, rispetto al solito, si va ad aggiungere la porta (in questo caso la 3308) dopo l'indirizzo del server, con i canonici due punti davanti.
Tutto qui, fatta questa modifica sono riuscito di nuovo a far funzionare la connessione a mysql dal mio script php sotto windows wamp, e quindi niente più rifiuto persistente.

Qui di seguito un video tutorial di quanto appena spiegato:
https://youtu.be/x4fXhSVxhSU

Come aggiornare Mysql su Wamp

Se avete installato wamp da un po' di tempo, probabilmente alcuni prodotti risulteranno un po' datati e potrebbe essere necessario un aggiornamento.
Tra le operazioni più delicate da fare in questo caso, c'è proprio l'aggiornamento di mysql, ovvero del database che contiene tutte le tabelle con i dati di tutti i siti web presenti sulla macchina wamp.

Questa delicata operazione però, fortunatamente è abbastanza semplice da fare, a patto che non si esageri troppo nel passaggio da una versione ad un'altra.
Nella mia ultima esperienza ad esempio, sono passato da mysql versione 5.7.14 alla versione 8.0.16, un bel salto in effetti, ma per fortuna fino alla 7.x la cosa risulta ancora fattibile.
Quando si compiono queste operazioni però, è tassativo poi controllare tutto il codice php, le viste e le varie query sql, in modo tale da non trovarsi davanti a qualche funzione o sintassi deprecata.

Vediamo dunque come compiere questo aggiornamento in maniera safe.

Come aggiornare Mysql su Wamp

Per poter aggiornare mysql su wamp bisogna seguire i seguenti passaggi:

1. Scaricare l'ultima versione di mysql dal sito ufficiale (https://www.mysql.com/it/), in formato zip
2. Estrarre lo zip appena scaricato nella cartella c:/wamp/bin/mysql/mysqlX.x.x (o wamp64, insomma il percorso dove avete installato wamp)
3. Rinominare la cartella dell'ultima versione in modo tale che rispetti la sintassi utilizzata nella versione precedente
4. Stoppare wamp in modo tale che nessuno possa scrivere sul database
5. Entrare nel prompt di dos ed andando nella cartella bin dove c'è mysql digitare il comando: mysqld.exe --initialize-insecure
   Questo genererà la giusta cartella data nel nuovo mysql
6. Copiare le cartelle con i nomi dei database che si vuole migrare presenti nella cartella data dell'ultima versione attiva di mysql, nella cartella del nuovo mysql
7. Copiare il file my.ini presente nell'ultima versione attiva di mysql dentro la cartella del nuovo
8. Cercare dentro il file my.ini tutte le occorrenze alla vecchia versione e rinominarle con il percorso della nuova, come ad esempio: c:/wamp64/bin/mysql/mysql8.0.16/data
9. Copiare il file wampserver.conf presente nell'ultima versione attiva di mysql dentro la cartella del nuovo
10. Verificare che i percorsi che sono presenti dentro il file wampserver.conf siano corretti per il nuovo mysql
11. Riavviate wamp 
12. Cliccare con il tasto sinistro sull'icona di wamp, poi cliccare su mysql e poi su version, e selezionare l'ultima versione appena installata

E' tutto.
Se wamp non dovesse ripartire al primo colpo, provate un paio di riavvii, e se finalmente diventerà verde la sua icona, allora avrete aggiornato il mysql correttamente e non vi resterà altro che iniziare a testare tutte le vostre applicazioni.

Nel caso anche a voi venga fuori l'errore "Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.x", che di fatto vi impedirà di far partire il nuovo mysql, dovrete svuotare i file di log presenti nella cartella data di mysql (ib_logfile0 e ib_logfile1).
Potete provare a cancellare questi file a mano, che di fatto vi siete portati dietro dalla precedente versione, e vedere se al rilancio di mysql tutto parte senza alcun problema.

MYSQL: cancellare record su più tabelle con i trigger

Tempo fa avevo spiegato come utilizzare i trigger in mysql, sia da codice sql, sia utilizzando l'interfaccia grafica di phpmyadmin.
Oggi invece vediamo un esempio concreto su una delle casistiche più ricorrenti, il delete after delete.

Quando capita di dover cancellare i record presenti in delle tabelle figlie, soprattutto se si sta sviluppando una nuova applicazione e si fanno molte prove, a volte può risultare scomodo andare ad eliminare a mano tutti i record di sottotabelle per evitare di lasciare righe orfane nel database mysql.
Ed è proprio in questi casi che torna utile saper utilizzare al meglio i trigger mysql.

Vediamo subito con un esempio pratico come ripulire in automatico il nostro database.

Come cancellare automaticamente record su più tabelle con i trigger di MYSQL

La sintassi sql da utilizzare per rimuovere automaticamente da una tabella mysql tutti i record legati ad una tabella non più esistente è la seguente:

CREATE TRIGGER `nome-trigger` AFTER DELETE ON `tabella-padre` 
FOR EACH ROW 
DELETE FROM tabella-figlia WHERE tabella-figlia.id-chiave=old.id;

La sintassi sopra indicata si spiega da sola, ma vediamo comunque di andare ad analizzare cosa ho appena scritto.
Il comando sql CREATE TRIGGER serve ovviamente a creare il nostro trigger, al quale bisogna dare un nome, bisogna indicare un'azione (in questo caso dopo l'operazione di cancellazione, ovvero AFTER DELETE), e bisogna dire qual'è la tabella dalla quale parte il comando.

C'è poi una sintassi che serve per indicare che l'operazione sql che andremo a fare sarà effettuata su tutte le righe della tabella bersaglio, ovvero: FOR EACH ROW.

Infine basta indicare la sintassi mysql desiderata per compiere l'azione, in questo caso la DELETE, mettendo nel WHERE un legame tra un id chiave della tabella bersaglio, con l'id della tabella padre, indicata con la sintassi old.

Tutto molto semplice no?
Da ora in poi quindi, cancellando una riga della tabella padre, verranno rimosse in automatico dal database tutte le righe della tabella figlia che rispondono ai requisiti indicati nel nostro trigger.

Importare un dump mysql da riga di comando

Oggi mi sono trovato a dover superare un antipatico problema durante l'importazione di un file dump su un database mysql.
Praticamente avevo questo file sql contenente tutte le tabelle ed i dati del mio nuovo database mysql, ma quando andavo ad importare tramite la comodissima funzionalità del phpmyadmin mi dava il seguente errore: "Stai probabilmente cercando di caricare sul server un file troppo grande" (You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit).
Vediamo subito come risolvere questo problema con un metodo alternativo.

Come importare un dump mysql da riga di comando

La prima cosa che si fa in questi casi, quando si ha questo genere d'errore, è di andare ad aumentare la dimensione massima dei file caricabili tramite php, nel php.ini per l'appunto.
Tuttavia, stanco di andare a dover modificare parametri di configurazione solo per gestire delle eccezioni, ho deciso di risolvere questo problema di file troppo grossi per essere importati in mysql, scegliendo un altro approccio.
Ho infatti scelto di provare a bypassare questo problema passando da riga di comando, tramite prompt dei comandi di dos (si, siamo sotto windows).

Ecco dunque cosa bisogna fare per superare i limiti delle configurazioni del php o del phpmyadmin:

1. Aprire il prompt di dos
2. digitare:  mysql -u username -p nomedatabase < dump.sql
3. Premere Invio

Ovviamente, occorre sostituire username con il nome di un utente in grado di compiere queste operazioni (idealmente un amministratore), mettere il giusto nome del database bersaglio (da creare quindi prima) ed inserire il path completo del file dump contenente tutte le query d'importazione dati.

Grazie a questo semplice script dos, che lancia direttamente il comando mysql (bisogna dunque essere posizionati nel giusto percorso del binario di mysql, o averlo mappato come variabile d'ambiente) passandogli già tutti i parametri per la connessione e per fare l'operazione, sarà possibile importare in un colpo solo e anche molto velocemente un intero database mysql compreso di tabelle e viste.

Tra l'altro ho notato che questo metodo di import sql da linea di comando sembra essere più veloce dello script usato dalla pagina del phpmyadmin, e questo non può che non essere una cosa positiva.

Ottenere elenco tabelle mysql

Dopo aver imparato come scoprire l'elenco dei database presenti, e come selezionarne uno, è arrivato il momento di apprendere come ottenere l'elenco delle tabelle in mysql.

Se infatti non si dispone di una comoda interfaccia grafica in stile phpmyadmin ma si deve operare solo da riga di comando, è giusto conoscere la sintassi necessaria per ottenere la lista di tabelle presenti all'interno di un database.
Va da se che per poter avere una lista di tabelle di un db, bisogna prima entrare nel db desiderato, con il metodo linkato poco sopra.

Bene, andiamo subito sul pratico e vediamo come compiere questa semplice operazione in sql.

Come ottenere l'elenco delle tabelle di un database mysql

Dopo essere entrati nel nostro database, se per caso non ci ricordiamo il nome esatto della tabella mysql che vogliamo andare ad interrogare, ecco il comando necessario per ricavare la lista delle tabelle presenti in un db:

show tables;

Con questo semplice comando seguito da punto e virgola ed un bell'invio, potremo vedere subito a video tutte le tabelle esistenti in un determinato database mysql.

Non penso ci sia molto altro da dire sull'argomento.
Ora si hanno infatti tutte le informazioni necessarie per poter iniziare a fare le prime query sulle tabelle, che siano di inserimento, modifica, o anche sola lettura.

Come cambiare database in mysql

Oggi vediamo un comando base per chi si avvicina per la prima volta nel fantastico mondo dei database relazionali come mysql.
E si, perchè prima di poter fare query complesse, selezionare dati, inserire nuove righe in tabelle o eliminarle, che cosa occorrerà mai fare?
E' semplice ed ovvio, bisogna sempre prima selezionare il database mysql corretto in cui sono contenute le informazioni che vogliamo andare a guardare o manipolare.
Vediamo subito come fare.

Come cambiare database in mysql

Dopo aver fatto accesso al nostro server mysql, magari tramite console, dopo aver messo dunque user e password, finiremo di default dentro un database predefinito.
Se però vogliamo cambiare database mysql, occorre conoscere il comando da digitare.

Per fortuna è tutto molto semplice anche se si vuole fare tutto da riga di comando, ecco la sintassi da usare:

use nome-database;

Il punto e virgola come al solito segna la fine dei comandi sql, e quindi dopo un bel invio si potrà subito ottenere il risultato desiderato, ovvero iniziare ad utilizzare il db appena selezionato.

A video vedrai un bel messaggio con scritto "database changed".
Tutto qui.

Come come? Non ti ricordi il nome esatto del database che vuoi utilizzare?
Nessun problema, leggi questo post.

Ricavare elenco database mysql

Se accedi per la prima volta ad un server mysql, dopo aver immesso user e password, quello che forse vorrai sapere è l'elenco dei database presenti sul server.
Se utilizzi un'interfaccia grafica come phpmyadmin ed accedi come root la cosa è immediata e salta subito all'occhio, ma se sei il nuovo dba e non conosci ciò che hai ereditato, e magari non disponi di un'interfaccia grafica, non disperare perchè tutto si può ovviamente fare anche da riga di comando.

Vediamo subito come fare per scoprire la lista completa dei database mysql presenti su uno specifico server.

Come ricavare l'elenco dei database in mysql

Il requisito fondamentale per il corretto esito di questa procedura è ovviamente avere un accesso di root / amministratori al server, dato che altrimenti non potrai vedere tutti i database presenti.

Comunque, ecco il semplice comando da scrivere per poter vedere l'elenco dei database mysql esistenti sul server:

show databases;

Comando chiuso con il solito punto e virgola, seguito dal tasto invio.

Se il comando sarà stato digitato correttamente e se si hanno i giusti permessi, il risultato sarà l'elenco completo dei database.

Fine breve post su mysql.

Chiave univoca mysql troppo lunga

Non troppo tempo fa mi sono trovato a dover convertire un database mysql da latin swedish ci al più prestante ed universale utf8 general ci.
A parte i vari problemi di conversione per via di charset salvati in maniera magari errata, mi sono ritrovato davanti ad un errore improvviso mysql:

"Specified key was too long; max key length is xxx bytes".

In buona sostanza, cercando su internet ho poi scoperto che le chiavi mysql, in questo caso una chiave univoca, possono essere lunghe massimo 1000 caratteri.
Il fatto che tutto prima del cambio di charset funzionasse è dovuto sostanzialmente al fatto che latin ed utf8 occupano diverso spazio in bytes per il salvataggio.

Comunque, cercando un po' su internet, ho per fortuna trovato la soluzione al problema della chiave mysql troppo lunga, vediamo subito di cosa si tratta.

Come risolvere il problema della chiave univoca mysql troppo lunga

Per poter evitare il terribile errore "Specified key was too long; max key length is xxx bytes" sulle chiavi mysql, si può agire sostanzialmente in tre modi.

La prima soluzione è la più semplice e forse veloce, anche se magari non sempre attuabile: bisogna ridurre la lunghezza dei campi che compongono la chiave.

Ad esempio, se un campo nome di una ipotetica tabella mysql è stato creato di tipo varchar(255), magari è il caso di ottimizzare le risorse e di farlo ad esempio varchar(100).

Se però non è possibile agire direttamente sulla lunghezza dei campi perchè sono già troppo corti, allora si può agire sulla lunghezza massima della chiave mysql generata.
Ecco un esempio di come poter fare:

ALTER TABLE `tabella` ADD UNIQUE ( campo1(50), campo2(100) );

Infine, un'altra possibile soluzione per questo antipatico errore mysql è quella di ridurre il numero di campi dell'indice univoco.
Anche in questo caso ovviamente, non è sempre possibile farlo.

A seconda dunque della versione di mysql in uso e della lunghezza massima degli indici, potrebbe essere necessario dover rivedere la logica su come sono stati costruiti i campi delle tabelle del database, o le key stesse.

MYSQL: ordinare risultati per valori campo

Una delle cose che sicuramente si impara subito quando si utilizzano i database relazionali come mysql, è come ordinare i risultati come meglio ci aggrada.
Che sia in ordine crescente o decrescente, per un campo piuttosto che due o tre, l'ordinamento dei dati presenti nelle tabelle è un requisito fondamentale per ogni app o sito che pesca i dati da un database.

Quello che però di solito non si fa, o meglio, che capita meno di dover fare, è di ordinare i risultati per valori.
In mysql infatti, è anche possibile ordinare i campi di una tabella, per uno specifico campo, ma non solo, anche per i valori contenuti in esso.
Mi spiego meglio.
Pensiamo ad esempio di avere una tabella con dentro un campo con lo stato dei record, il classico attivo, disattivo, archiviato, in lavorazione, ecc...
Se vogliamo ordinare i record presenti nella nostra tabella mysql, in ordine di stato, specificando però noi quale (ad esempio in lavorazione, attivo, disattivo), c'è una sintassi specifica che ci consente di fare ciò, vediamo subito quale.

Come ordinare i risultati di una tabella MySql per i valori contenuti in un campo

La sintassi che consente l'ordinamente dei record per valore in mysql, è la seguente:

SELECT campo FROM tabella ORDER BY find_in_set(campo,'valore1,valore2,valore3')

Tutto qui.
Grazie all'istruzione sql find_in_set, è infatti possibile ordinare per uno specifico campo, indicando i valori d'ordinamento.
Nell'esempio sopra indicato, il campo si chiama banalmente campo, ed i valori d'ordinamento sono valore1, valore2, valore3.
Ovviamente è possibile indicare quanti valori si vuole, ed accodare anche altri campi ordinati in maniera classica, con la sintassi che tutti conoscono.

Questa funzionalità di mysql è davvero potente, ma ovviamente non bisogna abusarne troppo.
Mettendo infatti troppi ordinamenti incrociati, si rischia di ritardare l'apparizione dei risultati della ricerca.
Eventualmente valutare l'ottimizzazione del db tramite gli indici.

Inserire dati da Sql Server a Mysql

Qualche tempo fa avevo scritto un post su come collegarsi da sql server a mysql, tramite la potentissima funzionalità chiamata open query.
Oggi invece vediamo come applicare quanto appreso in passato, per poter inserire dati da sql server a mysql, in maniera rapida ed indolore.

Ammettiamo ad esempio di avere una tabella su mysql che vogliamo popolare con dei dati presi su una tabella presente su sql server, ecco che quindi, oltre ad essere su database diversi, i dati delle rispettive tabelle sono anche su server diversi.
Nessun problema comunque, vediamo subito come fare.

Come inserire dati da Sql Server a Mysql

Innanzitutto, per poter fare query da un server ad un altro, bisogna prima linkare i due server, in questo caso in maniera monodirezionale, da sql server a mysql.
Tutta la sintassi e le operazioni da compiere per poter fare questo collegamento (linked server) l'ho già spiegata in questo post.

Vediamo invece la sintassi da utilizzare per inserire dati da un server ad un altro, in questo caso sql server che va a scrivere su mysql:

INSERT INTO OPENQUERY([NOME-SERVER-COLLEGATO], 'SELECT campo1,campo2 FROM nome-database.nome-tabella-destinazione') SELECT campo1,campo2 FROM tabella-origine WHERE eventuali-condizioni

Tutto molto semplice no?
Si tratta solo di usare una insert into select di sql server, una sintassi che già dovreste conoscere se smanettate da un po' con questo database.

I dati verranno dunque presi da sqlserver ed inseriti direttamente sul mysql presente su l'altro server.
Ovviamente, le tabelle d'origine e di destinazione, o meglio i campi che avete scelto di popolare, devono essere compatibili come formato, e perchè no, anche come charset impostato di default.

MYSQL: scoprire se un campo è un numero

La desiderata del giorno riguarda il database mysql e consiste nel voler sapere se un determinato campo di una tabella è di tipo numerico.
Per scoprire se un campo è un numero in mysql non esiste in realtà una specifica funzione, tuttavia è possibile ottenere questa informazione usando l'ingegno, vediamo subito di scoprire come fare.

Come scoprire se un campo è un numero in MYSQL

Per capire se un determinato campo di una tabella di un database mysql è di tipo numerico, basta usare la seguente sintassi:

SELECT campo FROM tabella WHERE campo REGEXP '^[0-9]+$';

Con le espressioni regolari infatti (regexp), è possibile utilizzare una condizione where per pescare solo le righe con specifici campi di tipo numerico, grazie alla particolare sintassi delle regexp ^[0-9]+$.

E' possibile usare questo metodo anche nella selezione dei campi, come IF.
In questo caso si può assegnare un valore diverso al risultato della query, in base al fatto che il campo analizzato sia di tipo numerico o meno.

Quindi, ricapitolando is_numeric mysql non esiste, ma per fortuna grazie alle espressioni regolari si può ottenere lo stesso il risultato atteso :)

MYSQL: Query tra tabelle su server diversi

Un'esigenza che prima o poi può capitare nella vita per chi gestisce database mysql, è quella di dover fare delle query tra tabelle presenti non solo su database differenti, ma addirittura su server diversi.
In sql server si chiamavano linked server, qui invece su mysql il loro nome è federated tables.

Vediamo subito cosa bisogna fare per poter pesare i dati su server diversi in mysql.

Come fare query tra tabelle su server diversi su mysql

Se abbiamo una tabella mysql in locale ed un'altra presente su un database collocato su un server remoto, il modo tipico per poter pescare questi dati con un'unica query sql, è quella di usare le federated tables.

Una tabella federata, se così la si può tradurre dall'inglese, è una sorta di tabella virtuale, una tabella che pesca i suoi dati direttamente da un'altra tabella presente su un altro server (ed un altro db).

Vediamo dunque come creare una federated table.
La sintassi per la creazione di queste tabelle è identica a qualsiasi altra tabella, è il solito create table infatti.
Quello che cambia è la parte finale, in cui si dice a mysql dove andare a prendere i dati.
Ecco un semplice esempio:

CREATE TABLE `tabella_federata` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(64) DEFAULT NULL,
  `cognome` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://utente:password@ip-server/nome-database/nome-tabella'

La parte cruciale di questo esempio è l'engine, il motore della tabella, che ha una sintassi del genere:
ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://utente:password@ip-server/nome-database/nome-tabella'

Penso che non ci sia bisogno di altre spiegazioni.

Quando si vuole creare quindi una tabella federata mysql, il mio consiglio è quello di esportare lo script di creazione della tabella presente sul server remoto, e poi di modificarlo nella sua parte finale, nel motore.

Una volta creata questa tabella virtuale, pescare i dati sarà un gioco da ragazzi.
Se infatti abbiamo creato una tabella federata sullo stesso database mysql della tabella da legare, la sintassi da usare sarà un classico:
SELECT tabella1.campo,tabella2.campo FROM tabella1,tabella2 WHERE idtabella1=idtabella2
Dove ovviamente una delle due tabelle in questione è la tabella federata creata poco prima.

Il limite di questo metodo per pescare dati da server diversi in mysql è abbastanza evidente.
Se si modifica la struttura della tabella remota, bisogna ricordarsi di modificare anche quella della tabella federata... particolare non da poco!

MYSQL: come fare insert o update in un colpo solo

Quante volte in mysql ti è capitato di dover fare delle query di aggiornamento dati non sapendo se un record era già presente in una tabella oppure no?
Ecco, in questi casi solitamente ci tocca fare una bella select, e poi a seconda del risultato trovato, fare una insert o un update.

Grazie alla sintassi che ti sto per spiegare invece, è possibile fare quello che io definirei un insert update mysql in un colpo solo, ovvero verificare con un metodo alternativo la presenza di un record in una tabella, ed agire di conseguenza... il tutto in una singola query!

Come fare insert o update in un colpo solo in MYSQL

Bando alle ciance, la sintassi da utilizzare per fare una insertupdate mysql, è la seguente:

INSERT INTO tabella (campo1,campo2) VALUES ('campo1','campo2') ON DUPLICATE KEY UPDATE nome='nome', cognome='nome';

Con questa semplice riga di codice sql infatti, se in una tabella si tenta di fare un inserimento dati, se questo fallisce perchè il record esiste già, viene fatto automaticamente un aggiornamento dati.
Ovviamente bisogna quantomeno riscrivere tutti i campi da inserire/aggiornare due volte, dato che bisogna comporre comunque sia la sintassi dell'insert che della update mysql... ma questo mi sembra proprio il minor dei mali, dato che ci siamo risparmiati una select che avrebbe magari anche rallentato l'eseguzione dello script.

La domanda però ora sorge spontanea... come si fa a capire se un record è già presente in una tabella?
Semplice, con le chiavi univoce!
Bisogna infatti prima creare una chiave univoca, o indicare un campo come univoco, e poi provare l'inserimento, altrimenti verrà effettuata sempre e solo l'insert.

Se non sai come creare chiavi univoce in mysql leggi questo post.
Quello che dovrai fare è, ad esempio, creare una chiave nome+cognome per impedire l'inserimento di omonimi, e gestire l'aggiornamento in tal senso.
Se poi vuoi fare una cosa ancora più pulita, definisci come indice univoco un id della tabella, in modo tale da aggiornare la riga solo quando si sta provando realmente ad inserire un duplicato.

Bene, adesso sai come fare insert update mysql in un colpo solo.
Come al solito, a grandi poteri corrispondono grandi responsabilità (cit.).

MYSQL: query tra database diversi

Se hai alberato la struttura dei tuoi dati in database diversi, devi sapere che in mysql è possibile compiere query multidatabase, ovvero tra un database ed un altro.
Mettiamo ad esempio di avere tutto un elenco di nominativi dentro una tabella in un database specifico, e di avere invece i corrispettivi indirizzi in un'altra tabella, ma in un diverso database, ecco che sapere la giusta sintassi per agganciare questi due db può sicuramente fare la differenza in termini di semplicità di query, ma anche di prestazioni.

Vediamo dunque come pescare dati da tabelle su database diversi con mysql.

Come fare query tra database diversi in mysql

Per poter legare i dati presenti in tabelle su database diversi, ecco la sintassi da utilizzare in mysql:

SELECT t1.campo,t2.campo FROM database1.tabella1 AS t1, database2,tabella2 AS t2

La sintassi dunque per poter pescare i dati da un database ad un altro, è semplicemente: database.nometabella.
Tra l'altro, visto che già ci troviamo sul primo database, non è necessario richiamare la tabella con il prefisso del db in cui ci si trova, ma si può farlo solo per collegarsi al secondo db.

Per facilitare le query, o per meglio dire, per renderle meno lunghe, è molto utile dare un'abbreviazione alle tabelle usate, con il comando AS nomecorto.
Quest'abbreviazione può essere usata sia nella select, ma anche e soprattutto nella WHERE dove vengono inserite le condizioni necessarie per legare le due tabelle.

Bene, ora sai come fare select su database diversi in mysql.
Ovviamente, un filino tutto ciò influisce sulle prestazioni, rispetto ad avere le tabelle già nello stesso db, ma parliamo veramente di una manciata di millisecondi.

MYSQL: impedire inserimento duplicati

Quando si progetta un'applicazione che si appoggia al database mysql, a volte conviene non lasciare la gestione delle tabelle al 100% allo script php (o in qualsiasi linguaggio il sito o l'app sia realizzata).
Ad esempio, se voglio essere sicuri al 100% che nessuno possa mai inserire dei record doppi, occorre lavorare sul database più che sull'app.

Vediamo quindi come fare per impedire l'inserimento di record doppi su mysql, utilizzando gli indici, in modo tale da impedire fisicamente qualsiasi operazione di inserimento di duplicati da parte di un sito web che si collega al nostro db sql.

Come impedire l'inserimento duplicati in MYSQL

Per bloccare a monte l'inserimento di record doppi in una tabella mysql, occorre usare gli indici.
Grazie agli indici di mysql infatti, è possibile evitare che un record venga inserito più volte.
Occorre individuare il campo o i campi che devono essere univoci, e creargli sopra un indice.

Vediamo subito la sintassi da utilizzare per aggiungere un indice univoco:

ALTER TABLE nome-tabella ADD UNIQUE(`nome-campo`);

Con questa semplice riga di codice sql infatti, avremo assegnato un indice univoco al campo desiderato.
Da ora in avanti dunque, non sarà più possibile inserire per quel campo dei valori uguali.
Il contro di questo metodo, è che ovviamente la tabella deve essere già sistemata prima di creare l'indice univoco, ovvero non devono esistere già dei duplicati nel campo da rendere unico, altrimenti non sarà possibile inserire l'indice.

Come creare un indice univoco con più campi

Ci sono poi dei casi in cui la chiave univoca da creare deve contenere più campi.
Prendiamo ad esempio il caso in cui in una tabella mysql non vogliamo che ci siano dei duplicati di persone che si chiamano nello stesso nome, ovvero facciamo finta di voler bloccare a monte l'inserimento di nome e cognome uguali.
Ecco cosa bisogna scrivere per creare quest'indice univoco multicampo:

ALTER TABLE nome-tabella ADD UNIQUE `nome-cognome` (`nome`, `cognome`);

Avremo così creato un unico indice che contiene due campi, a cui abbiamo anche dato un nome.
Grazie a questo indice sql, se si proveranno ad inserire 2 Mario Rossi, il database mysql genererà un errore del tipo:
"Valore duplicato 'Mario-Rossi' per la chiave 'nome-cognome'".

Bene, con gli indici univoci mysql per oggi è tutto!

Importare database access in mysql

Oggi mi è capitata una nuova esigenza, ovvero quella di dover importare un database access in mysql.
Avendo dunque delle tabelle in microsoft access, la mia necessità era quella di convertire i dati ed importare il tutto dentro un database relazionale online, ovvero mysql.

Cercando sul web ho trovato un tool gratuito che consente di fare ciò, il suo nome è mysql workbench.
Tuttavia però, dopo averci smanettato un po', ho valutato che era molto più semplice importare da access in mysql ogni singola tabella, con un metodo appena scoperto e decisamente rapido e funzionale... vediamo subito quale (cit.).

Come importare un database Access in Mysql

Per poter effettuare la migrazione da access a mysql, senza dover utilizzare alcun programma aggiuntivo, basta procedere nel seguente modo:

1. Crea un odbc su windows, una fonte dati che ti consentirà di effettuare la connessione a mysql dal tuo pc o dal server su cui eseguirai questa operazione (se non sai come fare leggi questo post)
2. Apri il tuo file access
3. Seleziona la tabella che vuoi importare in mysql (non te lo fa fare se ne selezioni più di una purtroppo), e clicca con il tasto destro del mouse
4. Clicca su Esporta
5. Clicca su Database ODBC
6.  Scegli il nome che vuoi dare alla tabella da importare, o lasciala uguale, poi clicca su Ok
7. Nella finestra che si aprirà, clicca sulla tab Origine dati dal computer
8. Adesso seleziona la fonte dati precedentemente creata e clicca su Ok

Avrai così esportato ed importato in un colpo solo, la tua tabella access dentro mysql.
Ovviamente manterrai tutte le configurazioni precedentemente settate, come il tipo di campo ed eventuali indici.

Certo, se devi importare tante tabelle da access a mysql questo processo potrebbe risultare un filino macchinoso, ma nel caso in cui il numero non sia eccessivamente elevato, con questo metodo avrai fatto un import access mysql in maniera molto semplice e senza programmi aggiuntivi, grazie alla possibilità di esportare le singole tabelle.

Ovviamente, affinchè questa procedura non dia errore, l'utente definito nella fonte dati odbc deve avere anche i permessi di creazione tabelle, e non solo di lettura/scrittura.

Stringa di connessione a Mysql (ODBC / OLEDB)

Per potersi collegare al database mysql da access o da qualsiasi altro strumento che utilizza i driver odbc o il metodo oledb, occorre conoscere la giusta stringa di connessione.
Sto parlando della giusta sintassi da utilizzare per poter far sì che i propri software possano leggere/scrivere dati sulle tabelle di mysql.

In questo breve post illustrerò come fare una stringa di connessione a mysql, in un metodo piuttosto che in un altro

Stringa di connessione MYSQL ODBC

"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER=ip-server; DATABASE=nome-database-mysql; UID=username-mysql; PASSWORD=password-mysql; OPTION=3"

La stringa sopra riportata direi che si spiega da sola.
Ad ogni modo, questa stringa di connessione a mysql è riferita ad una connessione di tipi ODBC, con i driver 3.51 Driver.
Nel caso quindi si utilizzino versioni differenti di driver, basta sostituire il suddetto valore con quello corretto.

Stringa di connessione MYSQL OLEDB

"Provider=MySQLProv;Data Source=nome-database-mysql; User Id=username-mysql; Password=password-mysql;"

Per connettersi a mysql usando l'oledb, un metodo che molti reputano più performante, basta dunque utilizzare la stringa sopra riportata.
Vale lo stesso principio per l'odbc, i dati sono più o meno gli stessi, anche se ad un occhio attento non sarà sfuggito il fatto che manca l'indicazione dell'ip del server mysql a cui collegarsi, che di fatto di default è il localhost.

Se infatti si vuole fare una OleDbConnection (per il framework .net) specificando un server mysql diverso online remoto, ecco una sintassi utilizzabile:

'conn.ConnectionString="provider=MYSQLOLEDB; Driver={MySQL};SERVER=ip-server;DATABASE=nome-database;USER=username;PASSWORD=password"

Bene, direi che con le stringhe di connessione per mysql è tutto :)

Risolvere errore: mysql odbc got a packet bigger than max_allowed_packet

La connessione tra access e mysql spiegata pochi giorni fa, come immaginavo, ha portato alla creazione di diversi post risolutivi di problemi vari :)
Oggi è venuto il momento di parlare del seguente errore: "mysql odbc got a packet bigger than max_allowed_packet", che altri non è che il dettaglio un errore di operazione update non riuscita su tabella..., errore che poteva anche essere riferito ad un'operazione di Insert.

Ma cosa ho provato a fare per ottenere questo fastidioso messaggio di errore?
Semplice, ho provato a salvare un file allegato su un database mysql, passando dalle maschere di access.

Vediamo dunque come sistemare questa ennesima problematica di compatibilità tra access e mysql... se così si può dire.

Come risolvere l'errore: mysql odbc got a packet bigger than max_allowed_packet

Per poter caricare dei file in un database mysql passando da una maschera di microsoft access, occorre innanzitutto essere sicuri che il campo nel database sia sufficientemente grande.
Ad esempio, se il nostro allegato è molto grande, il normale campo blob potrebbe non bastare, quindi occorre creare un campo long blob in mysql.

Allargando le dimensioni del campo del db mysql, da blob a longblob, già si risolvono eventuali problemi di dimensioni massima dei file caricabili, errori che possono dare come risultato su access, un bel errore: "odbc lost connection to mysql server during query", o "mysql server has gone away", oppure ancora, il terribile errore "odbc chiamata non riuscita".

C'è però un'altro parametro che occorre settare/controllare, affinchè non ci siano problemi di caricamento di file pesanti da access a mysql, e si tratta del my.ini, ovvero il file di configurazione di mysql.
Come indicato in un precedente post infatti, l'azione risolutiva è quella di modificare/aggiungere le seguenti linee di codice nel my.ini:

max_allowed_packet = 16M
wait_timeout = 6000

Ora, il 16 mega è un valore indicativo, potrebbe anche non bastare per voi, quindi settate pure il valore più opportuno in base ai file da caricare.
Il timeout invece serve per evitare che la lentezza di connessione possa inficiare negativamente sull'esito dell'operazione.
Tutto ciò, su wamp si trova nella sezione [wampmysqld64], altrimenti la sezione suppongo possa chiamarsi semplicemente [mysqld].

Bene, direi che è tutto.
Spero non insorgano altri problemi nell'utilizzo di mysql da access, altrimenti temo che si faccia prima a sviluppare una bella interfaccia web personalizzata al posto del prodotto della Microsoft :)

ODBC Chiamata non riuscita: access / mysql errore RISOLTO

L'altro giorno ho fatto un breve tutorial su come collegare access a mysql, ed eccomi qui subito a farne un altro ancora più breve per spiegare come risolvere l'errore odbc chiamata non riuscita.

E' il caso in cui, dopo aver correttamente configurato la fonte dati sul proprio pc, dopo averla testata con successo, si prova a collegarsi a mysql da access, e si viene bloccati istantaneamente da questo errore "odbc call failed" su access.

Vediamo subito come fare per rimediare a questo problema, che tra l'altro potrebbe presentarsi anche per la connessione da access ad altre fonti dati (es. sql server, oracle).

Come risolvere l'errore ODBC Chiamata non riuscita da access a mysql

Cercando un po' su internet, ho trovato qualche post a riguardo, ma le soluzioni proposte non mi convincevano molto, dato che spesso si parlava di problemi di timeout / latenza.
Ad ogni modo, prima di proporvi la mia soluzione, vi spiegherò brevemente cosa dicono di fare gli altri.

Premesso che do per scontato che abbiate inserito correttamente tutti i parametri di connessione come username, password, ip server, ecc..., e che non ci siano di mezzo terribili firewall, antivirus o proxy a bloccare le chiamate al database mysql, ecco quanto propongono di fare la maggior parte delle persone su internet: modificare il file di configurazione di mysql.

Bisognerebbe infatti andare sul file my.ini e settare i seguenti parametri:

max_allowed_packet = 64M
wait_timeout = 6000

Si tratta quindi di aumentare il numero massimo di pacchetti trasmessi ed il tempo di scadenza delle chiamate.
Se usate wamp, tutto ciò si trova nella sezione [wampmysqld64], dove max_allowed_packet è presente e andrebbe solo variato, mentre wait_timeout va proprio aggiunto.
Dopo aver modificato questi parametri, ricordarsi di riavviare mysql.

Come dicevo però poco sopra, nel mio caso l'errore "odbc chiamata non riuscita" appariva istantaneamente quando provavo a collegare mysql ad access, inoltre se aprivo la fonte dati mysql dal pannello di controllo, questa funzionava correttamente.
Ed infatti con me questa soluzione non ha funzionato.

Funziona: come risolvere errore odbc chiamata non riuscita

Ecco ciò che ha funzionato per me.
E' bastato installare il driver odbc a 32bit al posto che quello a 64 bit, e configurare il tutto di conseguenza.
Nonostante infatti io abbia un pc a 64 bit, e rispettivi programmi a 64 bit, ho dovuto usare una connessione a 32bit... poco male, l'importante è che funzioni.

Ad ogni modo, potrebbe per voi invece non funzionare il 32 ma il 64, quindi fate pure delle prove installando i giusti driver scaricandoli da questo sito internet:

https://dev.mysql.com/downloads/connector/odbc/

Spero che questo breve tutorial sia stato utile a qualcuno.
Probabilmente ci sono anche altre casistiche per cui possa risultare impossibile collegarsi a mysql da access, o da access a sql server, o a oracle (l'errore è generico e potrebbe presentarsi anche durante la connessione ad altri db), ma spero che rientrate in questo caso... dato che è il più facile da risolvere.