MySQL 8.0 - Nuove funzionalita'

La nuova versione MySQL 8.0 [NdE 19 Aprile 2018], anche se rispetto alla precedente release 5.7 cambia nella numerazione, e' sopratutto un'evoluzione sulle linee gia' intraprese nelle versioni precedenti: InnoDB, sicurezza, JSON, ... Ma ci sono altre importanti novita' nell'SQL come le clausole OVER e WITH o il Data Dictionary transazionale.
E' stata svolta anche una significativa riorganizzazione e pulizia del codice, anche se questo e' meno evidente per gli utenti finali che tipicamente non utilizzano il codice sorgente. Alcune differenze comunque si notano, ad esempio nella gestione del logging, nella riduzione degli sql_mode, nell'aumento delle parole riservate, ... e non mancano alcune incompatibilita'!

In questo documento sono riportati in dettaglio i principali nuovi elementi introdotti dalla versione 8.0 riportando esempi pratici di utilizzo:

Common table expressions (WITH)
Window functions (OVER)
Ottimizzatore e performances
JSON... di nuovo!
Data Dictionary... di nuovo!
InnoDB... di nuovo!
Sicurezza... di nuovo!

Ma le novita' non sono solo queste... continuate a leggere!

Common Table Expressions (WITH)

Non e' facile in SQL scrivere query per la distinta base, per analizzare strutture gerarchiche o per percorrere un grafo. Le Common Table Expressions (CTE) servono appunto ad effettuare queste ricerche complesse.

La forma piu' semplice delle CTE e' analoga alle SELECT annidate (derived tables):

with engineers as 
 ( select * from employees
    where dept='Engineering' )
select *
  from engineers
 where ...

select *
  from ( select *
           from employees
          where dept='Engineering' ) as engineers
where ...

Un poco piu' complessa e' la forma ricorsiva che consente di attraversare strutture complesse come alberi o grafi. La sintassi utilizza sempre la clausola WITH per definire il punto di partenza (anchor) ed il collegamento con i dati successivi basato sull'uso ricorsivo della CTE:

with recursive R as (
  select anchor_data
  union [all]
  select recursive_part
    from R, ...
)
select ...

Vediamo un esempio reale:

CREATE TABLE tratta (partenza varchar(50), arrivo varchar(50)); INSERT INTO tratta VALUES ('Acqui Terme', 'Asti'), ('Asti', 'Acqui Terme'), ('Acqui Terme', 'Alessandria'), ('Alessandria', 'Asti'), ('Asti', 'Torino'); WITH RECURSIVE percorsi (tragitto, destinazione, fermate) AS ( SELECT partenza, partenza, 0 fermate FROM tratta WHERE partenza='Acqui Terme' UNION SELECT CONCAT(percorsi.tragitto, ', ', tratta.arrivo), tratta.arrivo, percorsi.fermate+1 FROM percorsi, tratta WHERE percorsi.destinazione = tratta.partenza AND LOCATE(tratta.arrivo, percorsi.tragitto)=0 ) SELECT * FROM percorsi WHERE fermate>0; +----------------------------------------+--------------+---------+ | tragitto | destinazione | fermate | +----------------------------------------+--------------+---------+ | Acqui Terme, Asti | Asti | 1 | | Acqui Terme, Alessandria | Alessandria | 1 | | Acqui Terme, Alessandria, Asti | Asti | 2 | | Acqui Terme, Asti, Torino | Torino | 2 | | Acqui Terme, Alessandria, Asti, Torino | Torino | 3 | +----------------------------------------+--------------+---------+ 5 rows in set (0.00 sec)

Dall'esempio dovrebbe essere chiaro come e' possibile analizzare con l'SQL in maniera ricorsiva insiemi complessi di dati: basta definire i punti di partenza, i collegamenti e... evitare i loop infiniti!

Le Common Table Expressions sono definite nello standard SQL:1999. La documentazione ufficiale riporta maggiori dettagli...

Window functions (OVER)

Le Window functions consentono di eseguire operazioni di gruppo associate ad ogni singola riga estratta da una query.

Vediamo un esempio reale:

select deptno, ename, sal, SUM(sal) OVER (ORDER BY deptno, ename) AS running_total from emp order by deptno, ename; select deptno, ename, sal, SUM(sal) OVER (ORDER BY deptno, ename) AS running_total, SUM(sal) OVER (PARTITION BY deptno) AS dept_total, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_rank, FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal DESC) as top_sal from emp order by deptno, ename; +--------+--------+---------+---------------+------------+-----------+---------+ | deptno | ename | sal | running_total | dept_total | dept_rank | top_sal | +--------+--------+---------+---------------+------------+-----------+---------+ | 10 | CLARK | 2450.00 | 2450.00 | 8750.00 | 2 | 5000.00 | | 10 | KING | 5000.00 | 7450.00 | 8750.00 | 1 | 5000.00 | | 10 | MILLER | 1300.00 | 8750.00 | 8750.00 | 3 | 5000.00 | | 20 | ADAMS | 1100.00 | 9850.00 | 10875.00 | 4 | 3000.00 | | 20 | FORD | 3000.00 | 12850.00 | 10875.00 | 1 | 3000.00 | | 20 | JONES | 2975.00 | 15825.00 | 10875.00 | 3 | 3000.00 | | 20 | SCOTT | 3000.00 | 18825.00 | 10875.00 | 1 | 3000.00 | | 20 | SMITH | 800.00 | 19625.00 | 10875.00 | 5 | 3000.00 | | 30 | ALLEN | 1600.00 | 21225.00 | 9400.00 | 2 | 2850.00 | | 30 | BLAKE | 2850.00 | 24075.00 | 9400.00 | 1 | 2850.00 | | 30 | JAMES | 950.00 | 25025.00 | 9400.00 | 6 | 2850.00 | | 30 | MARTIN | 1250.00 | 26275.00 | 9400.00 | 4 | 2850.00 | | 30 | TURNER | 1500.00 | 27775.00 | 9400.00 | 3 | 2850.00 | | 30 | WARD | 1250.00 | 29025.00 | 9400.00 | 4 | 2850.00 | +--------+--------+---------+---------------+------------+-----------+---------+ 14 rows in set (0.00 sec)

Dall'esempio dovrebbe essere chiaro come vengono utilizzate le window functions per estrarre dati progressivi, raggruppati, ordinati, ...

Particolarmente conciso e, sopratutto, di facile lettura e' l'SQL per ottenere valori medi:

SELECT variable_value - lag(variable_value) over (order by time_stamp) Bytes_received, time_stamp
  FROM global_status_history
 WHERE variable_name='Bytes_received'
 ORDER BY time_stamp;


SELECT  iot_time, iot_val, 
        AVG(iot_val) OVER (order by iot_time ROWS between 3 PRECEDING and 3 FOLLOWING) mean_val,
        AVG(iot_val) OVER (order by iot_time ROWS 6 PRECEDING) pred_val
  FROM iot_devices;


SELECT  anno, mese,
        AVG(fatturato) OVER ( PARTITION BY anno, mese
                       ORDER BY anno, mese
                       ROWS 12 PRECEDING) as fatturato_mensile
...

Oltre alle normali funzioni di gruppo (eg. SUM(), AVG(), ...) sono supportate ulteriori funzioni sulle partizioni: CUME_DIST(), DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), NTH_VALUE(), NTILE(), PERCENT_RANK(), RANK(), ROW_NUMBER().

Le Window functions sono definite nello standard SQL:2003. La documentazione ufficiale riporta maggiori dettagli, tra cui un esempio per determinare il primo valore di un gruppo.

Ottimizzatore e performances

Le novita' piu' significative per il DBA per ottimizzare le performances sono due: i descending indexes e gli invisible indexes. I nomi indicano chiaramente il trattamento da parte dell'ottimizzatore, vediamo quindi un esempio della sintassi per entrambe:

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; CREATE TABLE scadenziario ( anno INT, cognome VARCHAR(32), INDEX idx1 (anno DESC, cognome ASC)); SELECT anno, cognome FROM scadenziario ORDER BY anno DESC, cognome;

Facile vero?

La gestione delle tabelle temporanee puo' avere impatti significativi sulle performances. Lo Storage Engine TempTable e' il default per le tabelle temporanee e sostituisce lo storico Engine MEMORY.

La scalabilita' verticale e' stata migliorata in modo significativo nella versione 8, le differenze sono evidenti superando 64 sessioni concorrenti.

La gestione dei charset e' stata ottimizzata nella versione 8.0 con il default (utf8mb4) le differenze prestazionali sono significative rispetto alle versioni precedenti.

Infine tra le ottimizzazioni dell'ottimizzatore della otto [NdA scusate non mi sono trattenuto sull'888] e' da segnalare l'utilizzo degli istogrammi sulle statistiche delle colonne.
Le altre differenze rispetto alle precedenti versioni sono sopratutto funzionali, dal punto di vista delle prestazioni MySQL 8.0 e' sostanzalmente in linea con le altre releases.

JSON

La versione 8.0 introduce nuove importanti funzioni per il trattamento del data type JSON, gia' introdotto con la versione 5.7: ->> (inline path), JSON_PRETTY(), JSON_ARRAYAGG(), JSON_OBJECTAGG(), JSON_TABLE(), ...
L'inplace update puo' essere notevolmente piu' performante rispetto alla gestione precedente che in pratica effettuava una cancellazione ed un nuovo inserimento. Per sfruttare l'inplace update vanno usate le funzioni: JSON_SET(), JSON_REPLACE(), JSON_REMOVE().

Con l'uscita della versione 8.0 una forte enfasi e' stata data anche all'utilizzo di MySQL come Document Store grazie all'X Protocol... ma anche questo era gia' stato introdotto con la 5.7!
E' comunque importante sottolineare che MySQL e' utilizzabile anche con API CRUD come avviene con i database NoSQL.

Data Dictionary

Ogni nuova versione di MySQL presenta un aggiornamento significativo sul data dictionary... la versione 8.0 introduce ulteriori novita'.

Il Data dictionary e' ora definito su tabelle transazionali.
La gestione delle DDL e' ora atomica (Atomic DDL).
L'engine InnoDB non ha piu' un proprio distinto data dictionary ma viene utilizzato il MySQL Data Dictionary (eg. la tabella INNODB_SYS_TABLES e' ora INNODB_TABLES).

Si tratta di variazioni significative ma con un impatto applicativo limitato.

InnoDB

Anche in questa versione vengono aggiunte nuove funzionalita' all'Engine InnoDB.
Sono disponibili le modalita' NOWAIT e SKIP LOCKED negli statement SELECT ... FOR SHARE e SELECT ... FOR UPDATE.
La configurazione dell'AUTO_INCREMENT si mantiene tra riavvi, in caso di rollback non viene incrementato ed e' stato ottimizzato.
Le tabelle temporanee InnoDB sono create in un tablespace dedicato: ibtmp1.
Sono utilizzate tablespace di undo separate (minimo 2).

Sicurezza

Da ultimo ma non come ultimo... la sicurezza. Anche su questo MySQL continua a migliorarsi: ci sono tante piccole differenze e nuove funzionalita' introdotte con la versione 8.0. In questa breve panoramica vediamo: ROLES, password history, validate_password, caching_sha2_password, Redo/Undo Log encryption.

La prima novita' e' la disponibilita' dei ruoli, funzionalita' presente da tempo su molti altri DB. Vediamo la sintassi con degli esempi:

CREATE ROLE 'r_app_dev', 'r_app_ro', 'r_app_rw'; GRANT ALL ON app_db.* TO 'r_app_dev'; GRANT SELECT ON app_db.* TO 'r_app_ro'; GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'r_app_rw'; GRANT 'r_app_dev' TO 'to111213'@'10.1.2.3'; GRANT 'r_app_ro' TO 'to111214'@'10.1.2.4', 'to111215'@'10.1.2.5'; GRANT 'r_app_rw', 'app_write' TO 'rw_user1'@'app_serv.domain'; SHOW GRANTS FOR 'to111213'@'10.1.2.3'; SHOW GRANTS FOR 'to111213'@'10.1.2.3' USING 'r_app_dev'; SET DEFAULT ROLE ALL TO 'to111213'@'10.1.2.3'; SELECT CURRENT_ROLE(); SET ROLE NONE; SET ROLE ALL; SET ROLE DEFAULT; SELECT DISTINCT User RoleName, if(from_user is NULL,0, 1) Active FROM mysql.user LEFT JOIN mysql.role_edges ON from_user=user WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';

Con la versione 8.0 puo' essere mantenuta la password history, per evitare che un utente utilizzi la stessa password piu' volte. La gestione e' molto semplice e viene effettuata con due parametri (ovviamente da impostare nel file my.cnf):

password_history=6
password_reuse_interval=365

E' anche possibile impostare i valori con l'impostazione di variabili, in modo persistente, oppure indicarli in modo specifico per utente alla creazione/alter di un utente:

SET PERSIST password_history = 6; SET PERSIST password_reuse_interval = 365; CREATE USER 'scott'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;

Il plugin validate_password e' stato sostituito da un componente con lo stesso nome. Anche se trasparente in molti casi, vi sono alcune piccole differenze... Di default il componente viene installato, ma non su tutte le piattaforme; se non e' presente bisogna lanciare il comando:
INSTALL COMPONENT 'file://component_validate_password';

Con la versione 8.0 e' possibile impostare la crittografia dei Redo Log e degli Undo Log: basta impostare un parametro a ON [NdA i parametri sono rispettivamente: innodb_redo_log_encrypt, innodb_undo_log_encrypt]. Non e' al momento possibile crittografare il binary log che resta l'unico tipo di file non crittografabile dell'Engine InnoDB [NdA il binary log non e' sempre presente ed e' utilizzato per la replica MySQL].

E' disponibile un nuovo pluging ottimizzato per la crittografia della password: caching_sha2_password. Rispetto al plugin sha256_password, introdotto nella 5.7, mantiene lo stesso livello di sicurezza ma risulta piu' veloce utilizzando un caching. Nella versione 8.0 il plugin caching_sha2_password e' il default per le nuove installazioni.
Attenzione: se si utilizza il nuovo plugin di autenticazione vanno aggiornate anche le librerie client alla versione 8.0! [NdA almeno alla 8.0.4 (che supporta tutte le funzionalita' del plugin) o meglio alla 8.0.11 (prima GA)]. Altrimenti non ci si collega e si hanno errori come:
mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

Per creare/modificare un'utente assegnando il vecchio plugin di autentificazione, compatibile con i client delle versioni precenti, la clausola SQL e'
IDENTIFIED WITH mysql_native_password BY 'XXX';
Le variazioni impattano anche la replica... nel caso di errore: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection impostate l'opzione master_ssl=1 nella clausola change master to.
La configurazione del plugin di default si effettua nel my.cnf con [NdA richiede un riavvio]:

default-authentication-plugin=mysql_native_password

Dalla versione 5.7.23 [NdE rilasciata il 27 luglio 2018], le librerie ed i programmi client 5.7 supportano caching_sha2_password e possono cosi' essere utilizzati per collegarsi ad una versione 8.0.

Varie ed eventuali

Altre novita' interessanti [NdA aggiornate alla piu' recente versione di produzione: 8.0.13] sono:

Resource groups
E' disponibile, dalla versione 8.0, la possibilita' di creare resource group assegnabili ai thread MySQL. E' in questo modo possibile assegnare una priorita' (da -20, la piu' alta, a 20) alle VCPU definite. Al momento le VCPU sono l'unica risorsa definibile, ma e' probabile che in futuro ci saranno possibilita' piu' ampie.
Character set
Il character set di default e' passato dallo storico latin1 al recente e molto completo utf8mb4 (che contiene anche i famigerati emoticons).
Logging
E' stato reingenierizzato il logging che risulta ora molto piu' configurabile. E' ad esempio possibile filtrare specifici messaggi dal log con [NdA filtra il messaggio Multi-threaded slave statistics]:
```
INSTALL COMPONENT 'file://component_log_filter_dragnet';
SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';
SET GLOBAL dragnet.log_error_filter_rules = 'IF err_code == 10559 then drop.';
```
La documentazione ufficiale riporta tutti i dettagli.
Backup lock
Per effettuare backup consistenti sono ora disponibili le clausole: LOCK INSTANCE FOR BACKUP e UNLOCK INSTANCE.
GIS
Nuove e piu' estese funzionalita' sulla componente GIS con la gestione degli SRS (Spatial Reference Systems) standard. [NdA con la versione 5.7 era possibile indicare l'SRS ma poi i calcoli venivano effettuati solo con l'SRID 0 che e' il piano cartesiano piatto e nelle relazioni tra oggetti veniva utilizzato il minimum bounding rectangle (MBR) anziche' la forma esatta]
Tools
Sono stati aggiornati tutti i tool per gestire al meglio le funzionalita' della versione 8.0 ed hanno ora un numero di versione legato al DB (eg. MySQL Router 2.1.6 -> 8.0) Sono stati aggiornati: MySQL shell (storia dei comandi persistente tra sessioni, prompt basato su sessione e contesto, custom fonts, ...), MySQL Router (routing_strategy, ...), ...
SET PERSIST
Che dire? Grande! La sintassi e':
SET PERSIST max_connections = 1000; SET @@persist.max_connections = 1000;
Utile sopratutto per i DBA smemorati come il sottoscritto che si dimenticano di aggiornare il my.cnf quando correggono un parametro...
password_require_current
Dalla versione 8.0.13 [NdE 2018-10] e' possibile indicare che l'utente deve conoscere l'attuale password prima di poterla cambiare.
Data Masking and De-Identification
Dalla versione 8.0.13 [NdE 2018-10] e per la sola edizione Enterprise [NdE e' un prodotto commerciale che richiede licenza] sono fornite una serie di funzioni per il data masking (eg. mask_inner, mask_ssn, gen_rnd_email, ...).
Deprecated and Removed
Alcune funzionalita' sono state deprecate e quindi e' consigliabile non utilizzarle piu'...
Altre funzionalita', deprecate da tempo, sono state rimosse nella versione 8.0 e quindi non si possono piu' utilizzare!
Tra le molte variazioni proviamo a ricordare quelle che riteniamo piu' importanti: 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; lo stato Sending data e' stato rimosso e quindi gli statement correnti risultano sempre nello stato Executing [NdA 8.0.17]; ...
La documentazione ufficiale riporta tutti i dettagli delle funzionalita' deprecate o rimosse nella 8.0.
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';
Incompatibilita'
Oltre alle funzionalita' rimosse gia' riportate nel punto precedente ed alle differenze nei protocolli di autenticazione, diversi altri componenti in MySQL 8.0 sono stati implementati in modo differente rispetto alle precedenti versioni... in generale non e' supportato il downgrade alla 5.7 sia come strutture binarie sia con un mysqldump.

Un breve riassunto delle funzionalita' introdotte nel tempo si trova su Introduzione a MySQL. MySQL e' in costante evoluzione; ecco le funzionalita' introdotte nelle versioni precedenti: 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.
Un documento completo con le versioni dei principali software, tra cui MySQL, e' Il tuo server puzza!

Titolo: MySQL 8.0 - Nuove funzionalita'
Livello: Avanzato (3/5)
Data: 19 Aprile 2018
Versione: 1.0.5 - 15 Agosto 2023
Autore: mail [AT] meo.bogliolo.name