MariaDB su macOS

Questa paginetta riporta i passi per l'installazione su macOS di MariaDB nella recente versione 10.2 [NdE 23 Maggio 2017] e, soprattutto, le nuove funzionalita' introdotte.

MariaDB e' un fork del diffusissimo database MySQL ed e' presente su molte distribuzioni Linux. Essendo un fork, dal punto di vista funzionale di chi sviluppa applicazioni tra MariaDB e MySQL le differenze sono molto poche: la maggioranza dei programmi funziona correttamente con entrambe i database.
Poiche' l'installazione su macOS e' semplicissima questo documento e' in realta' l'occasione per presentare alcune delle nuove funzionalita' presenti nell'ultima versione di MariaDB.

Nel seguito sono riportate alcune informazioni di interesse organizzate in paragrafi specifici: Introduzione, Installazione, Nuove funzionalita' (WITH, OVER, JSON, Flashback, Limiti utente), Varie ed eventuali.

Introduzione

MariaDB e' un fork del diffusissimo database MySQL creato nel 2009, tra gli altri, da Michael Widenius (aka Monty), uno degli ideatori di MySQL.
Da principio le versioni di MariaDB e di MySQL erano corrispondenti, successivamente MariaDB ha sviluppato in modo indipendente nuove funzionalita' (eg. GTID replication) ed introdotto una nuova numerazione. La seguente tabella [NdA disponibile in modo completo ed aggiornato su questo documento] riporta le principali funzionalita' inserite in MariaDB:

Version	Status	Features	Last release	Date (from)	Date (last)	Date (to)	Notes
10.2	Production	Based on MariaDB 10.1 with backported features from MySQL 5.6 and 5.7. New features: window functions (OVER), common table expression (WITH), DECIMAL(38), multiple triggers, JSON and GeoJSON functions support, CHECK constraints, InnoDB 5.7.18 (but shows 5.7.14). (10.2.13 2018-02): InnoDB 5.7.21, fixed a WSREP serius bug (10.2.17 2018-08): InnoDB 5.7.23, CVE fixes	10.2.25	2017-05	2019-06	2022-05	Suggested
10.1	Production	Based on MariaDB 10.0 with backported features from MySQL 5.6 and 5.7. New features: Galera; Table/Tablespace/Log Encryption, AWS Key Management Service; InnoDB compression; GIS	10.1.40	2015-10	2019-05	2020-10	Suggested
10.0	Production	Based on MariaDB 5.5 with backported features from MySQL 5.6. New features: Roles, Audit Plugin, TokuDB Engine, Parallel Replication, Multi source Replication, GTID (NB different from MySQL), Information and Performance schemata as MySQL 5.6.	10.0.38	2014-03	2019-01	2019-03
5.5	Production	Based on MySQL 5.5 and MariaDB 5.3. Performance schema.	5.5.64	2012-04	2019-04	2020-04
...
5.1	Production	Based on the corresponding version of MySQL with some added features: Aria Engine, XtraDB Engine, microseconds in processlist	5.1.67	2010-02	2013-01	2015-02

Fino alla versione 5.5 le versioni di MySQL e MariaDB erano le stesse, anche se generalmente disponibili con maggior frequenza da MariaDB. La versione 10.0 di MariaDB e' invece derivata dalla versione 5.5 di MariaDB con l'aggiunta del backport di diverse funzionalita' della 5.6 di MySQL. Nella maggior parte dei casi MariaDB puo' essere sostituito a MySQL con un drop-in ovvero facendo leggere le strutture dati MySQL a MariaDB senza che sia necessaria nessuna conversione di dati. Le differenze maggiori sono nella gestione delle repliche e sugli Storage Engine [NdA tipicamente MariaDB ne include molti di piu'].

Con la versione 10.2 sono state introdotte nuove importanti funzionalita' che verranno descritte nel seguito.

Installazione

Per l'installazione su macOS X utilizziamo Homebrew che rende il tutto molto semplice, basta un comando:
brew install mariadb

Ecco il dettaglio:

$ brew install mariadb
==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.2.6.sierra.bottle.tar.gz
Already downloaded: /Users/meo/Library/Caches/Homebrew/mariadb-10.2.6.sierra.bottle.tar.gz
==> Pouring mariadb-10.2.6.sierra.bottle.tar.gz
==> Using the sandbox
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To connect:
    mysql -uroot

To have launchd start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mariadb/10.2.6: 620 files, 145.0MB

Per collegarsi al DB:
mysql -uroot

Per avviare il DB basta il comando:
mysql.server start

Tutti gli altri comandi? Esattamente quelli di MySQL.

Facilissimo!

Non avete brew? Basta installarlo!
Non avete un Mac? Mi spiace per voi, pero' potete sempre (beh, non proprio sempre ;-) installare un repository con:
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

Nuove Funzionalita'

Le funzionalita' introdotte dalla release 10.2 di MariaDB sono notevoli. Le principali le descriveremo nei prossimi capitoli:

Common Table Expressions (WITH)
Window functions (OVER)
JSON functions
Flashback
Limiti utente

Ma in realta' le novita' sono molte di piu':

InnoDB storage Engine di default
In precedenza MariaDB utilizzava XtraDB di Percona come default Engine ora utilizza InnoDB come default Engine. La versione di InnoDB utilizzata e' la 5.7.18 [NdA la piu' recente disponibile al momento del rilascio]
Compatibilita' con la struttura di DB di MySQL 5.7.
MariaDB puo' sostituire MySQL con la sola sostituzione degli eseguibili operando direttamente sui database precedentemente creati. La struttura dati di MySQL 5.7 non era ancora supportata: lo e' con MariaDB 10.2.
Clausola di CHECK CONSTRAINT
La sintassi del CHECK CONSTRAINT e' riconosciuta da sempre su MySQL ma solo dal punto di vista sintattico: non era mai stata implementata. Ora i controlli sono effettivi.
Estensione della clausola DEFAULT
Sono disponibili dalla 10.2 DEFAULT con expressions, BLOB e TEXT possono avere un valore di DEFAULT.
Incremento delle cifre significative di DECIMAL
Le cifre decimali significative del datatype DECIMAL sono state aumentate da 30 a 38 [NdA come in Oracle RDBMS].
Trigger multipli
E' ora possibile definire trigger multipli sullo stesso evento. E' semplicemente molto comodo per gestire implementazioni differenti basate su trigger.
AWS key management
MariaDB supporta l'encryption dei dati. Per aumentare il livello di sicurezza e per essere conformi ai piu' stringenti requisiti di sicurezza, la chiave di crittografia deve essere mantenuta all'esterno della base dati e del file system del sistema ospite. Amazon Web Services (AWS) fornisce un Key Management Service (KMS) che soddisfa tali requisiti e che e' stato integrato come Plug-in in MariaDB.

Sembra molto? In realta' vi sono altre nuove funzionalita' che non ho riportato (eg. MyRocks Engine, OpenSSL 1.1 e LibreSSL, ...) che sono dettagliate nella documentazione ufficiale.

Common Table Expressions (WITH)

Non e' facile in SQL scrivere query per la distinta base, per analizzare strutture gerarchiche o per percorrere un grafo. Oracle ha da tempo una clausola per gestire i casi piu' semplici [NdA la clausola CONNECT BY], ma la soluzione generale e' piu' complessa. MariaDB 10.2 ha introdotto le Common Table Expressions (CTE) previste dallo standard SQL che 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 state definite nello standard SQL:1999. La piu' recente versione di produzione di MySQL [5.7] non supporta le CTE. Maggiori dettagli nella documentazione ufficiale!

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 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 | 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  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
...

Le Window functions sono state definite inizialmente nello standard SQL:2003. La piu' recente versione di produzione di MySQL [5.7] non supporta le Window Functions [NdA sono state implementate in MySQL versione 8].

Le funzioni disponibili sono parecchie, molte piu' delle group functions: min, max, avg, median, first_value, last_value, lag, rank, row_number, ... Maggiori dettagli nella documentazione ufficiale MariaDB!

JSON

Il formato JSON per la memorizzazione e lo scambio di dati e' molto utilizzato dagli sviluppatori Java e JavaScript per la sua semplicita'. Alcuni recenti database NoSQL lo utilizzano in modo nativo ed la sua diffusione e' in continuo aumento. MariaDB ha scelto di non implementare un datatype specifico ma di fornire l'insieme completo delle funzioni per la gestine dei dati JSON sui normali datatype di testo.

Vediamo un esempio reale:

SET @json='{ "nome": "Bartolomeo", "cognome": "Bogliolo", "soprannome": "Meo", "eta": 69, "esperienza": [ "MySQL", "MariaDB", "MongoDB", "SQLite" ], "telefono": "01123456789", "peso" : 69 }'; select json_query(@json, '$.esperienza'); select json_extract(json_query(@json, '$.esperienza'), '$[1]');

Particolarmente importate per le colonne JSON e' l'implementazione dei CHECK constraints perche' consente una semplice validazione anche senza un datatype associato. Basta utilizzare nella definizione della tabella:
CHECK (json_valid(colonna_json))

Il JSON in SQL e' stato definito nello standard SQL:2016. La piu' recente versione di produzione di MySQL [5.7] supporta JSON con un datatype nativo. Maggiori dettagli nella documentazione ufficiale!

Flashback

Il Flashback consente di tornare indietro nel tempo sulle modifiche effettuate su una tabella o su un database. L'implementazione di MariaDB utilizza una nuova opzione del comando mysqlbinlog, che serve appunto ad analizzare il contenuto dei binlog.

Il database deve essere stato attivato con il parametro binlog_row_image=FULL. E' a questo punto possibile utilizzare il comando:

mysqlbinlog mysql-bin.000069 -vv -d mydb -T mytab \ --start-datetime="2017-05-24 17:00:00" --flashback > flashback.sql

Sul file flashback.sql si otterranno tutti gli statement SQL necessari per effettuare un rollback logico a momento desiderato.

Al momento il flashback e' possibile solo sulle istruzioni di DML e, considerato che richiede un parametro specifico ed opera in memoria, non ci aspettiamo che questa funzionalita' abbia un forte impiego.

Limiti utente

La versione 10.2 di MariaDB introduce due nuove clausole utilizzabili nella creazione degli utenti.

La clausola REQUIRE consente di indicare se un determinato utente deve utilizzare una connessione cifrata per collegarsi alla base dati. La sintassi e' la seguente:

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

tls_option:
  SSL 
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

Con la clausola WITH e' invece possibile limitare per utente il numero di connessioni contemporanee ed il numero di statement eseguiti in un ora. La sintassi e' la seguente:

[WITH resource_option [resource_option] ...]

reource_option:
  MAX_QUERIES_PER_HOUR count
  | MAX_UPDATE_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

Riteniamo che la crittografia delle trasmissioni dati ai database diverra' sempre piu' diffusa.

Varie ed eventuali

MySQL e' presente da sempre nelle distribuzioni Linux come componente fondamentale delle architetture LAMP. Da alcuni anni molte distribuzioni hanno adottato MariaDB come database nei loro repository (eg. RedHat, CentOS, ...). In alcune distribuzioni MariaDB ha sostituito MySQL, in altre e' stato inserito in alternativa. E' comunque sempre possibile utilizzare i repository ufficiali di MariaDB o di MySQL [NdA fortemente consiglato se si utilizza il DB come ambiente di produzione] piuttosto che quelli delle distribuzioni che sono sempre piuttosto obsoleti. L'elenco completo delle distribuzioni che supportano MariaDB si trova nella documentazione ufficiale [NdE ad oggi sono piu' le distribuzioni che utilizzano MariaDB che MySQL].

MariaDB e MySQL sono oggettivamente in forte concorrenza e gli sviluppi hanno iniziato a divergere presentando funzionalita' differenti o implementate in modo diverso tra loro. Ma sono assolutamente di piu' i punti in comune che le differenze, anzi nel tempo vi sono stati diversi esempi di convergenze evolutive.
Per fare un esempio l'Engine di default nelle ultime versioni di produzione, il giorno del rilascio in produzione MariaDB 10.2, e' l'InnoDB esattamente nella stessa versione e praticamente con la stessa configurazione di default.

Titolo: MariaDB su macOS
Livello: Esperto (4/5)
Data: 23 Maggio 2017
Versione: 1.0.2 - 25 Maggio 2017
Autore: mail [AT] meo.bogliolo.name