Compatibilita' tra versioni MySQL

MySQL e' il noto e diffuso database relazionale Open Source. Nel tempo sono state rilasciate piu' versioni con funzionalita' crescenti e, come spesso avviene, con qualche problema di compatibilita'.

In questa paginetta cerchiamo di riportare i consigli piu' utili per operare tra versioni differenti di MySQL.

La storia delle versioni di MySQL non e' proprio lineare ed e' riassunta in questa tabella (Source: Your server stinks!):

Anche se le versioni supportate sono la 8.0 o sucessive sono ancora molto diffuse le versioni MySQL 5.7 e 5.6. In questa paginetta vedremo le problematiche che possono presentarsi passando ad una versione supportata di MySQL e come risolverle.

In teoria...

In teoria andrebbero aggiornate periodicamente le minor release di MySQL [NdA le minor release sono disponibili ogni 4 mesi e sono banali da aggiornare]... In teoria bisognerebbe utilizzare sempre una versione recente ed aggiornata di MySQL, dei tool, degli ambienti client, delle librerie di connessione, ... In teoria non andrebbe utilizzare le fuzionalita' deprecate e costrutti non standard...

Gli aggiornamenti alla versione successiva di MySQL sono semplici: nella maggior parte dei casi l'aggiornamento si effettua con un drop-in della nuova versione [NdA nelle ultime versioni non e' neanche piu' necessario il lancio del comando mysql-upgrade].
Il passaggio di versione e' sempre garantito verso la versione successiva mentre tipicamente non e' possibile effettuare "piu' salti" saltando versioni intermedi.

Solo utilizzando versioni supportate e con un costante aggiornamento delle minor relesease si e' protetti da problemi di sicurezza e si evitano i bug piu' gravi.

Quindi il primo consiglio per evitare problemi di compatibilita' con MySQL e' quello di mantenere sempre aggiornati sia le applicazioni che la base dati. Questo pero' spesso avviene solo in teoria...

...in pratica

In pratica e' molto comune trovare versioni obsolete di database MySQL e di applicazioni perche' "comunque funzionano".
Questo pero' puo' portate a parecchie difficolta' quando e' necessario aggiornare o migrare un componente dell'architettura applicativa che puo' risultare non piu' funzionante con la vecchia versione del database. Anche perche' l'aggiornamento di MySQL tra versioni molto lontane tra loro puo' essere particolarmente complesso.
A volte il problema e' invece sulle applicazioni che non vengono aggiornate ed utilizzano versioni obsolete di driver per accedere al database che si vorrebbe aggiornare.

Per effettuare un upgrade di MySQL vi sono una serie di situazioni d'errore che si possono verificare a cui forniremo una soluzione pratica nei prossimi paragrafi.

Problemi di connessione (plugin)

Nel tempo in MySQL il protocollo di connessione, gli algoritmi di crittografia, la memorizzazione delle password sono cambiati in modo molto significativo. Questo genera problemi spesso problemi di connessione quando si utilizzano versioni diverse tra il database server e le librerie del client.

Sulla base dati, nella tabella mysql.user, viene memorizzata una password crittografata che viene confrontata con quella fornita dall'utente. Ma l'algoritmo di crittografazione e' cambiato nel tempo [NdA e non solo quello...]. Le possibili combinazioni sono molteplici!

Come esempio prendiamo MySQL 8.0 ed una libreria PHP 7.3 che non supporta il nuovo plugin di autenticazione caching_sha2_password. L'algoritmo utilizzabile da entrambe e' mysql_native_password che crittografa la password in una stringa di 41 caratteri ed e' stato il default per molti anni. Per fare in modo che le password vengano salvate con tale algoritmo l'impostazione e':

mysqld.cnf:

default_authentication_plugin=mysql_native_password

Attenzione: In alternativa e' possibile specificare il plugin da utilizzare con il comando SQL:

La verifica del plugin utilizzato si effettua con una semplice query [NdA a seconda della versione alcune impostazioni non sono possibili]:

Naturalmente e' meglio utilizzare la modalita' piu' recente di crittografazione delle password supportata sia dal client che dal server perche' piu' sicura.

Problemi di connessione (charater set)

Un secondo problema in fase di connessione puo' essere generato da una differenza tra i character set supportati su client e server. In questo caso l'errore riportato e':
  Warning: mysql_connect(): Server sent charset (255) unknown to the client:
Per fare in modo che non vi siano problemi bisogna impostare sul server un character set che sia comprensibile al client. Tra le diverse possibilita' scegliamo le due piu' diffuse, la piu' semplice e':

mysqld.cnf:

[mysqld]
character-set-server = latin1
collation-server latin1_swedish_ci

[client]
default-character-set=latin1
[mysql]
default-character-set=latin1

Oppure se si vuole utilizzare l'UTF8 (evitando di utilizzare il default della 8.0 utf8mb4/utf8mb4_0900_ai_ci che non e' retrocompatibile con le vecchie versioni):

mysqld.cnf:

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

Naturalmente e' meglio utilizzare i driver piu' recenti che non presentano problemi di compatibilita'.

Compatibilita' SQL

L'SQL di MySQL e' sempre stato noto per la sua semplicita' e flessibilita', ma questo era un poco a scapito rispetto all'aderenza agli standard. Nel tempo sono state implementate le funzionalita' previste dagli standard e corrette alcune semplificazioni presenti. Questo genera alcune incompatibilita' tra versioni che pero' possono essere corrette con alcune impostazioni.

La principale variabile da impostare e' sql_mode i cui valori possibile e default sono cambiati nel tempo:

In MySQL 5.7:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

In MySQL 8:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

La verifica sull'impostazione presente si esegue con:

L'impostazione che fornisce la miglior retrocompatibilita' e':

[mysqld]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Naturalmente e' meglio correggere l'applicazione non utilizzando costrutti SQL non standard, nel tempo le versioni di MySQL sono diventate piu' precise nel segnalare errori che nelle versioni precedenti non erano emessi.

Dump/Restore

Il dump/restore consente di spostare un database MySQL anche tra versioni e piattaforme differenti. Poiche' il comando mysqldump produce uno script in SQL il dump e' facilmente portabile e vi sono generalmente pochi problemi di compatibilita'.

Tuttavia se le versioni sono molto diverse o si utilizzavano funzionalita' desupportate anche il backup logico puo' presentare problemi.

In caso di import da una versione precedente e' utile impostare innodb_strict_mode=OFF nel file my.cnf sulla base dati di destinazione. In questo modo si aggirano una serie di possibili errori. Il valore di default di innodb_strict_mode e' cambiato nella 5.7. Al termine dell'import e' consigliabile impostare nuovamente lo strict mode.

Replica

Naturalmente la configurazione ideale della replica MySQL prevede la stessa versione per tutti i nodi e l'aggiornamento regolare delle minor releases avendo l'accortezza di aggiornare prima gli slave e poi il master. In realta' la replica e' molto flessibile e consente diversi trucchi che possono risolvere problemi di compatibilita' tra database.
Con MySQL e' possibile utilizzare versioni diverse tra i nodi posti in replica purche' si tratti della versione immediatamente sucessiva [NdA dopo l'introduzione delle versioni LTS il limite e' stato leggermente allargato agli upgrade path].
Non vi sono problemi ad utilizzare minor release diverse anche se e' consigliato di utilizzare sempre le piu' recenti: l'evoluzione, i bug ed i fix sulla replica sono molto significativi in MySQL.
Naturalmente vi sono problemi se si utilizzano funzionalita' non compatibili tra le due versioni ed un grande numero di piccole eccezioni generalmente aggirabili con opportuni workaround.

Se si ha la necessita' di replicare dati tra versioni non sucessive, ad esempio per migrare da 5.6 alla versione 8.0, e' possibile utilizzare una configurazione in daisy-chain.

In caso di upgrade alla versione 8.0 con la replica si puo' verificare l'errore: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Si risolve impostando l'opzione master_ssl=1 nella clausola change master to.

Concludendo configurazione in replica e' sia una complessita' in in piu' che un'opportunita' per gli upgrade di MySQL ed in generale i vantaggi superano le eventuali difficolta'. La replica e' sicuramente da tenere in conto se si vuole effettuare un upgrade con un disservizio minimale.

Parole chiave

Tutti i linguaggi di programmazione hanno parole chiave con un significato preciso che non possono essere utilizzate come nomi di oggetti. Lo stesso avviene per l'SQL di MySQL in cui alcune parole non possono essere utilizzati come nomi di tabelle o di colonne. A seconda delle versioni le parole riservate cambiano ed e' possibile che diano problemi in caso di upgrade.

Breve storia di MySQL

La facilita' d'uso, l'efficenza dell'implementazione, la stabilita' del database, la frequenza di introduzione di nuove funzionalita' e, non da ultimo, la licenza Open Source hanno decretato l'enorme successo del database MySQL sopratutto per le applicazioni web.
Vediamo ora una breve storia di MySQL con una particolare attenzione alle modifiche che possono portare a problemi in caso di aggiornamento.

Il motore originale di MySQL (1995) e' costituito da una serie di librerie ISAM per l'accesso ai dati. La release 3.23 (2001) ha introdotto le maggiori innovazioni fornendo un SQL molto completo per le applicazioni. Nella versione 3.23 sono stati introdotti l'efficiente Storage Engine MyISAM (3.23.0), la replicazione asincrona (3.23.15), lo Storage Engine InnoDB (3.23.34) con supporto delle transazioni e delle foreign key, ...
La versione 4.0 (2003) ha introdotto le Union ed i savepoint su InnoDB. Molto diffusa e' stata anche la 4.1 (2004) in cui sono disponibili la funzionalita' di Cluster basata sullo Storage Engine NDB, una maggiore sicurezza nell'autenticazione utente e le subquery.
La versione 5.0 (2005) e' compatibile ANSI SQL:2003 e presenta diverse notevoli estensioni: viste, stored procedure, cursori ed un catalogo standard nel database information_schema.
Nella versione 5.1 (2008) sono stati inseriti il partizionamento, uno schedulatore, un'API per i plugin, la row based replication (la replication MySQL e' statement based), alcune importanti estensioni per il cluster (replication e disk storage), ...

Viene rilasciata una versione 5.4 con ottimizzazioni specifiche per sistemi multiprocessori ed una versione 6.0 che utilizza l'Engine Falcon al posto di InnoDB ma entrambe le versioni non vengono piu' mantenute. Queste discontinuita' sono anche dovute al fatto che nel 2009 la Oracle Corp. acquista la Sun Microsystems che l'anno precedente aveva acquisito MySQL AB.

Nella versione 5.5 (2010) e' stata introdotta la replicazione semi-sincrona, diversi, significativi miglioramenti prestazionali sull'InnoDB (che diventa l'Engine di default) ed i comandi SQL SIGNAL/RESIGNAL e LOAD XML.

La versione 5.6 (2013) introduce un significativo arricchimento del PERFORMANCE_SCHEMA (eg. Host Cache), gli indici Fulltext per InnoDB, un'interfaccia NoSQL (memcache), miglioramenti sulle prestazioni e sulla scalabilita', un incremento sulla sicurezza dei dati nella replicazione, l'introduzione di policy per la gestione delle password.
La versione 5.7 (2015) prosegue con miglioramenti sulle prestazioni e sulla scalabilita', con nuove funzionalita' sulla replication e sulla security. Alcune funzionalita' deprecate da tempo sono state definitivamente rimosse nella versione 5.7! Tra queste sono significativi i seguenti casi: non sono piu' disponibili le funzioni old_password e tutto quanto le utilizzava; non e' piu' utilizzabile il datatype YEAR(2), va utilizzato con le 4 cifre significative; il formato ROW_FORMAT=FIXED, desupportato da tempo, restituisce errore con l'impostazione di default, in caso di import da una versione precedente conviene quindi impostare innodb_strict_mode=OFF nel my.cnf.

Cambia la numerazione con la versione 8.0 (2018) ma prosegue l'evoluzione su sicurezza, prestazioni, JSON, Common Table Expressions (WITH) e Window Functions (OVER). Con la versione 8.0 e' stata limitata compatibilita' con le versioni precedenti; alcune differenze sono aggirabili (eg. sql_mode, authentication) ma altre sono definitivamente desupportate (eg. downgrade, query cache).
Nella versione 8.0 il plugin caching_sha2_password e' il default per le nuove installazioni. Se si usa il nuovo plugin di autenticazione vanno aggiornate anche le librerie client alla versione 8.0.
Il character set di default e' passato dallo storico latin1 al recente e molto completo utf8mb4 (che contiene anche i famigerati emoticons). E' stata rimossa la Query Cache, non sono piu' utilizzabili i relativi parametri; non e' piu' possibile definire utenze con un GRANT: e' necessario usare CREATE USER; sono state rimosse alcune funzioni di crittografia (eg. ENCRYPT, PASSWORD) considerate poco sicure; sono stati rimossi una serie di sql_mode (eg. oracle, postgres) che quindi non possono essere piu' utilizzati; le funzioni Spatial utilizzano ora il prefisso standard ST_ e sono state rimosse tutte le funzioni deprecate in precedenza, le modifiche a fronte di un upgrade sono significative; sono state rimossi dall'INFORMATION_SCHEMA: GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS le cui informazioni sono ora disponibili nel Performance Schema; non e' piu' disponibile il partizionamento per l'Engine MyISAM;
Dalla versione 8.0.34 e' stata deprecato il plugin di autenticazione mysql_native_password. L'effetto collaterale e' che ogni accesso segnala un warning nel file di log. Per evitare la segnalazione si puo' usare: set global.log_error_suppression_list='MY-013360';

La versione 8.4 (2024) e' stata rilasciata come LTS e raccoglie le nuove funzionalita' delle Innovation Release 8.x precedenti.
Le modifiche che al momento mi sembrano piu' importanti la variazione dei valori di default di parecchie parametri di tuning sono il definitivo desupporto di molte funzionalita' gia' deprecate. Se la modifica dei valori di default e' sicuramente opportuna per tenere conto delle piu' recenti tecnologie (eg. i dischi allo stato solido hanno quasi completamente soppiantato quelli meccanici) il desupporto di funzionalita', anche se motivato da ottime ragioni (eg. maggior sicurezza), puo' provocare ulteriori problemi di compatibilita'. La versione 8.4 ha rimosso la possibilita' di utilizzare l'impostazione default-authentication-plugin=mysql_native_password ma e' ancora possibile supportare il vecchio plugin con l'impostazione mysql-native-password=ON.

La versione 9.0 e' la nuova Innovation Release ed ha rimosso completamente il plugin mysql-native-password. Al momento non e' consigliabile un upgrade di ambienti di produzione.

L'elenco completo delle versioni MySQL con le date di rilascio e delle funzionalita' e' riassunto in questa tabella.

Fork

Oltre a MySQL mantenuto da Oracle Corp. e' importante riportare che vi sono fork quali Percona Server for MySQL, MariaDB, Amazon Aurora MySQL, ... I diversi fork hanno un elevato grado di compatibilita' applicativa ma presentano importanti differenze rispetto a MySQL.

Il piu' semplice da descrivere e' Percona Server perche' e' un downstream build: segue sempre le release di MySQL Community con un piccolo ritardo. Percona server e' compatibile con MySQL Community con l'aggiunta di ulteriori funzionalita' come gli Engine XtraDB o TokuDB o il comando di backup Xtrabackup.
Trattandosi di un fork di downstream la compatibilita' di Percona Server e' praticamente al 100% e puo' essere sostituito con un drop-in a MySQL. Anche il passaggio inverso e' tipicamente possibile, purche' non si utilizzino funzionalita' non presenti in MySQL come gli Engine aggiuntivi di Percona.

MariaDB ha mantenuto le stesse versioni di MySQL fino alla 5.5. Poi ha rilasciato la versione 10.0 (2014) con nuove funzionalita' ed il backporting delle features 5.6. Con le prime versioni era possibile il drop-in upgrade avendo solo l'avvertenza di lanciare il comando mysql_upgrade al riavvio. I rilasci delle versioni 10.x sono proseguiti molto velocemente divergendo sempre di piu' da MySQL con cui comunque e' sempre stata mantenuta la compatibilita' applicativa. Da diversi punti di vista le versioni 10.x ed 11.x di MariaDB sono piu' vicine alle versioni 5.6 e 5.7 di MySQL di quanto lo sia la 8.0 di MySQL.
La documentazione ufficiale di MariaDB e' molto dettagliata e precisa.

Aurora MySQL e' un'implementazione Amazon di un database compatibile a livello applicativo a MySQL. Su Amazon RDS sono disponibili diverse versioni di Aurora corrispondenti alle differenti versioni di MySQL. In generale una versione di Aurora MySQL ha le stesse identiche funzionalita' della corrispondente versione di MySQL, il livello di compatibilita' e' molto elevato. La principale differenza e' nella gestione dello Storage che in Aurora e' ottimizzato per l'infrastruttura Cloud di Amazon.

TiDB e' un Database Open Source compatibile con MySQL progettato per supportare transazioni OLTP e per scalare linearmente distribuendo su piu' nodi i dati. TiDB e' progettato per un carico HTAP ovvero per supportare sia query OLTP (On-Line Transaction Processing) che OLAP (On-Line Analytical Processing). La versione MySQL di riferimento e' la 5.7, vi sono significative differenze da MySQL.

Vi e' infine una serie di database che utilizzano il protocollo di rete di MySQL e che forniscono diversi livelli di compatibilita' (eg. ClickHouse).

Varie ed eventuali

MySQL e' in costante evoluzione... ecco le funzionalita' introdotte nelle principali versioni: MySQL 8.4, MySQL 8.0, MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0. Per i piu' curiosi e' anche utile il confronto con un importante fork: MariaDB 10.2.

Il documento completo con le versioni dei principali software, tra cui MySQL, MariaDB, Aurora, e' Il tuo server puzza!

Titolo: Compatibilita' tra versioni MySQL
Livello: Avanzato (3/5)
Data: 1 Aprile 2024
Versione: 1.0.0 - 1 Aprile 2024
Autore: mail [AT] meo.bogliolo.name