Mature

If you want to install a MySQL database on a modern Linux distribution, in more than half of the cases you are likely to opt for MariaDB. CentOS no longer offers the mysql-server package and installs mariadb-server instead. Debian ships with MariaDB 10.1, and only Ubuntu still uses an original MySQL 5.7.

If you don't believe me, try this test:

SQL> SHOW GLOBAL VARIABLES WHERE Variable_name IN ('version','version_comment');
+-----------------+----------------+
| Variable_name   | Value          |
+-----------------+----------------+
| version         | 10.3.9-MariaDB |
| version_comment | MariaDB Server |
+---------------+------------------+

MariaDB is designed to be a drop-in replacement for MySQL (see the box "The Story So Far"), which from a technical perspective means that you would stop the database server, replace the binaries, and restart the database server. Done! Whether this will work in reality is a different matter, but I'll consider just one thing at a time.

Important v10.3 Changes

If your distribution delivers a slightly older MariaDB version, getting close to the latest MariaDB 10.3 is not magic: You simply add the vendor's repository to your distribution and install. Listings 1 and 2 show this for Ubuntu 18.04 and CentOS.

# sudo apt-get install software-properties-common
# sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
# sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.mva-n.net/mariadb/repo/10.3/ubuntu bionic main'
# sudo apt update
# sudo apt install mariadb-server

# sudo cat > /etc/yum.repos.d/mariadb.repo << _EOL
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
_EOL
# sudo yum install MariaDB server MariaDB client

Anyone who has followed the MariaDB release notes closely over the last few years will have noticed that Oracle compatibility has always been the buzzword. It seemed that MariaDB was trying to corner the Oracle market. In addition to the numerous small details that were implemented, the Oracle-style sequences were particularly noticeable.

Sequences are database objects that return on request a number that the database creates in line with a certain pattern. Typically, it is an ascending integer sequence that may have gaps. With MariaDB, this has been achieved so far by applying the function AUTO_INCREMENT to a unique column. However, this was neither particularly flexible nor compatible with Oracle.

In contrast, a sequence can now be described as shown in Listing 3, and Listing 4 shows how the result can be used.

SQL> CREATE SEQUENCE demo_seq
INCREMENT BY 3
MINVALUE = 8
MAXVALUE = 99
START with 10
ENGINE = InnoDB
COMMENT = 'Demo Sequence';

SQL> INSERT INTO test
VALUES (NEXTVAL(demo_seq), 'Some data', NULL);
SQL> SELECT * FROM test;
+----+-----------+---------------------+
| id | data      | ts                  |
+----+-----------+---------------------+
| 10 | Some data | 2018-09-10 17:19:13 |
+----+-----------+---------------------+

The form well known from Oracle,

SELECT demo_seq.nextval;

only works if MariaDB has been switched to Oracle mode (more about that later). The lastval function allows the last assigned value for the current connection to be queried:

SELECT lastval(demo_seq);

Additionally, the value of the sequence can be changed with SETVAL:

SELECT SETVAL(demo_seq, 42);

With the ALTER SEQUENCE and DROP SEQUENCE commands, you can modify or delete the sequence. Finally, the instructions in Listing 5 queries how often a particular sequence has been created, changed, or deleted.

SQL> SHOW GLOBAL STATUS LIKE '%seq%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| com_alter_sequence   0     |
| com_create_sequence  2     |
| com_drop_sequence    1     |
+----------------------------+

More Oracle Compatibility

The sql_mode variable has been around in MariaDB for quite some time, and it has certainly caused confusion – or even trouble – at times.

In version 10.3, MariaDB understands a subset of the Oracle PL/SQL language in addition to the traditional MySQL SQL/PSM language resources for stored routines, assuming the admin previously set the SQL mode to Oracle. Facilitated by a major Asian bank [1], the feature clearly aims to make it easier for Oracle Database users to migrate to MariaDB.

The database administrator can also change the SQL mode ad hoc at the command line. For production operation, however, it is recommended that you choose either the legacy MariaDB mode or the new Oracle mode for each MariaDB instance. As a small example, Listing 6 shows an anonymous Oracle PL/SQL block in MariaDB. Further examples can be found online [2].

SQL> SELECT @@session.sql_mode INTO @old_sql_mode;
SQL> SET SESSION sql_mode=ORACLE;
SQL> DELIMITER /
SQL> BEGIN
SQL> SELECT 'Hello world from MariaDB anonymous PL/SQL block!';
SQL> END;
SQL> /
SQL> DELIMITER ;
SQL> SET SESSION sql_mode=@old_sql_mode;

Stored Aggregate Functions

Aggregate functions perform calculations on a group of rows and then return only one result for the whole group. Examples of existing aggregate functions are COUNT(), AVG(), and SUM(). Stored aggregate functions in MariaDB now offer a way to implement your own functions (Listing 7).

01 SQL> CREATE TABLE marks (
02 `student` VARCHAR(17)
03 `grade` TINYINT UNSIGNED
04 );
05
06 SQL> INSERT INTO marks VALUES ('Alena', 6), ('Milva', 4), ('Marino', 5), ('Pablo', 5), ('Leo', 6);
07 INSERT INTO marks VALUES ('Alena', 5), ('Milva', 4), ('Pablo', 6), ('Leo', 2);
08 INSERT INTO marks VALUES ('Alena', 4), ('Milva', 3), ('Marino', 6), ('Pablo', 5), ('Leo', 4);
09
10 SQL> SELECT * FROM marks;
11
12 SQL> DELIMITER //
13
14 SQL> CREATE AGGREGATE FUNCTION agg_count(x INT) RETURNS INT
15 BEGIN
16 DECLARE count_students INT DEFAULT 0;
17 DECLARE CONTINUE HANDLER FOR NOT FOUND
18 RETURN count_students;
19
20 LOOP
21 FETCH GROUP NEXT ROW;
22 IF x THEN
23 SET count_students = count_students + 1;
24 END IF;
25 END LOOP;
26 END;
27 //
28
29 SQL> DELIMITER ;
30
31 SQL> SELECT student, agg_count(5) AS 'tests'
32 FROM marks GROUP BY student;
33 +---------+-------+
34 | student | tests |
35 +---------+-------+
36 | Alena   | 3     |
37 | Leo     | 3     |
38 | Marino  | 2     |
39 | Milva   | 3     |
40 | Pablo   | 3     |
41 +---------+-------+

The query in this example would be much easier to map with the use of a simple COUNT(*). However, aggregated stored functions can be used to create far more complex constructs (e.g., a geometric mean).

News About SQL

MariaDB has come closer to the standards for SQL commands, as well. Now, DELETE statements that reference themselves can delete rows (Listing 8) instead of provoking the error:

01 SQL> CREATE TABLE customer (
02 id INT UNSIGNED
03 , year SMALLINT UNSIGNED
04 , revenue DECIMAL(11, 2)
05 , customer_class CHAR(1)
06 );
07
08 SQL> INSERT INTO customer
09 VALUES (1, 2016, 100.0), (2, 2016, 0.0), (3, 2016, 999.99);
10 SQL> INSERT INTO customer
11 VALUES (1, 2017, 500.0), (2, 2017, 0.0), (3, 2017, 100.00);
12 SQL> INSERT INTO customer
13 VALUES (1, 2018, 400.0), (2, 2018, 0.0), (3, 2018, 0.0);
14
15 SQL> SELECT * FROM customer;
16 +---+------+---------+
17 | id| year | revenue |
18 +---+------+---------+
19 | 1 | 2016 | 100.00  |
20 | 2 | 2016 |   0.00  |
21 | 3 | 2016 | 999.99  |
22 | 1 | 2017 | 500.00  |
23 | 2 | 2017 |   0.00  |
24 | 3 | 2017 | 100.00  |
25 | 1 | 2018 | 400.00  |
26 | 2 | 2018 |   0.00  |
27 | 3 | 2018 |   0.00  |
28 +---+------+---------+
29
30 SQL> DELETE FROM customer
31 WHERE id IN (SELECT id FROM customer GROUP BY id HAVING SUM(revenue) = 0.0);

ERROR 1093 (HY000): You can't specify target table 'customer' for update in FROM clause

The same applies to UPDATE statements. Recently, ORDER BY and LIMIT clauses have been added to multitable updates. Besides the existing UNION and UNION ALL operators, MariaDB now also knows EXCEPT (MINUS) and INTERSECT (Listing 9).

01 SQL> CREATE TABLE customer (
02 first_name VARCHAR(33)
03 , last_name VARCHAR(55)
04 );
05
06 SQL> CREATE TABLE user (
07 first_name VARCHAR(33)
08 , last_name VARCHAR(55)
09 );
10
11 SQL> INSERT INTO customer VALUES ('Hans', 'Meier'), ('Sepp', 'Müller'),
12 ('Fritz', 'Huber');
13 SQL> INSERT INTO user VALUES ('Oman', 'Klept'), ('Hans', 'Meier'), ('Getfor'),
14 'Free');
15
16 # In the first case, we want to identify all users who are not customers:
17
18 SQL> SELECT first_name, last_name FROM user
19 EXCEPT
20 SELECT first_name, last_name FROM customer
21 ;
22 +------------+-----------+
23 | first_name | last_name |
24 +------------+-----------+
25 | Oman       | Klept     |
26 | Getfor     | Free      |
27 +------------+-----------+
28
29 # And in the second case we want to know who is user and customer:
30
31 SQL> SELECT first_name, last_name FROM user
32 INTERSECT
33 SELECT first_name, last_name FROM customer
34 ;
35 +------------+-----------+
36 | first_name | last_name |
37 +------------+-----------+
38 | Hans       | Meier     |
39 +------------+-----------+

Previously, these operations were managed with somewhat more complicated JOIN constructs, but thanks to the two new clauses, the code looks much more intuitive and simpler. The non-standard MINUS, which corresponds to EXCEPT and is familiar to Oracle developers, is unfortunately not yet supported by MariaDB.

System-Versioned Tables

Besides numerous other small improvements in the SQL layer, the new system-versioned tables feature stands out. Defined by the SQL:2011 standard, this feature stores the entire history of a row from creation to deletion, which means you can analyze data for a specific point in time or track changes during an audit. The command

SQL> CREATE TABLE accounting (
name VARCHAR(55),
amount DECIMAL(11, 2),
`date` DATE
) WITH SYSTEM VERSIONING;

creates a versioned table, to which you can now enter data:

SQL> UPDATE accounting SET amount = amount + 500.0, `date` = '2018-03-13' WHERE name = 'Offenders';

At a later date, it will be possible to see exactly what the account balance of Offenders was at a certain point in time (e.g., March 1) (Listing 10).

SQL> SELECT * FROM accounting FOR SYSTEM_TIME AS OF TIMESTAMP '2018-03-01 00:00:00';
+-----------+---------+------------+
| name      | amount  | date       |
+-----------+---------+------------+
| Employees |  600.00 | 2018-02-25 |
| Chief     | 1200.00 | 2018-02-25 |
| Offenders |  600.00 | 2018-02-25 |
+-------------+---------+----------+

Further options for limiting the time range include FROM … TO … or BETWEEN … AND … . MariaDB also offers the possibility of querying the rows in a transaction-specific way with the trxid marker. An existing table can be converted to system versioning with:

SQL> ALTER TABLE accounting ADD SYSTEM VERSIONING;

Be careful when backing up: If you save with mysqldump, the entire history is lost. Therefore, you should use a physical backup method such as the mariabackup tool.

Other New Features

Storage engine-independent column compression has also been added. This feature especially reduces the footprint of very large columns (e.g., BLOB, TEXT, VARCHAR, VARBINARY, JSON):

SQL> CREATE TABLE mail (
 subject VARCHAR(255)
, body VARCHAR(255)
, attachment LONGBLOB COMPRESSED
, metadata JSON COMPRESSED
);

Indexing compressed columns is not possible.

RocksDB

The default storage engine for MariaDB is InnoDB, in (MySQL) version 5.7. Thus, most of its features are also available under MariaDB. In version 10.3, minor improvements and numerous bug fixes have been made.

The MyRocks MariaDB storage engine is based on RocksDB, the open source storage engine that was originally (and probably still is) developed by Facebook. RocksDB uses a log-structured merge (LSM) tree, which has an advantage over B-tree indexes for write-intensive workloads and is optimized for solid state drives (SSDs).

The RocksDB compression ratio is about twice as good as compression under InnoDB and is therefore particularly memory efficient. RocksDB offers better write performance than InnoDB for the same workload, accelerating data loading and reducing replication bottlenecks. However, given a read-heavy workload, the RocksDB results are not quite on par with InnoDB.

RocksDB comes with 129 configuration variables, and 105 items of status information can be queried. The output of the command

SHOW ENGINE ROCKSDB STATUS;

is just as complex as it would be from InnoDB. MyRocks configuration seems to be much more complicated than that for InnoDB; therefore, some examples from the RocksDB developers could prove useful for an easy start [3].

OQGRAPH

The Open Query GRAPH (OQGRAPH) computation engine [4] is not an actual storage engine but simulates a graph database with relational tables (InnoDB). OQGRAPH lets you query hierarchies (tree structures, Figure 1) and complex graphs.

The OQGRAPH computation engine in MariaDB 10.3 still has gamma status, so it is not yet recommended for production use. The Leaves algorithm has been implemented. In contrast to the RocksDB storage engine, the user does not need to reinstall the OQGRAPH computation engine, just activate it.

Spider

Another exotic animal is the Spider storage engine. It, too, is not really a storage engine, but a kind of sharding layer that consolidates data from different instances (Figure 2). Specifically, if a table is partitioned and the individual partitions are moved to separate instances on different servers, the whole construct behaves like a single large instance.

The Spider storage engine has been tagged stable in MariaDB 10.3.7 [5]. At the same time, the partition engine has been extended to support condition pushdown (WHERE clause is already evaluated in the partition), multi-range read, full-text search, and aggregation pushdown, as well as bulk update and delete operations.

For the Admin

Although most of the new features benefit developers, a few new features are for the admin, such as fast fail for data definition language (DDL) commands on InnoDB tables. If a long-running open transaction locks a table with a metadata lock (MDL), and a DDL command is simultaneously issued against this table, the DDL command has to wait with a status of Waiting for table metadata lock for up to one day (timeout after 86,400 seconds).

Thanks to a community contribution from Alibaba, you can now tell the DDL command not to wait at all (NOWAIT) or only for a certain number of seconds:

SQL> ALTER TABLE test WAIT 5 ADD COLUMN bla INT;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This behavior is referred to as "fast fail."

A similar change also affects the TRUNCATE-TABLE command. So far, it has simply ignored the metadata locks, which, strictly speaking, leads to a violation of the ACID (atomicity, consistency, isolation, durability) conditions. Now the TRUNCATE TABLE command respects the metadata lock and can be equipped with NOWAIT or WAIT accordingly.

Previously, an OLD TABLE command in MariaDB always required a complete copy of the table. Since MariaDB 10.0, many OLD TABLE commands can be executed in place without copying the data. The ALGORITHM clause controls whether the old procedure (COPY) or the new in-place procedure (INPLACE) is to be used.

However, the in-place procedure has a negative effect: It can take an extremely long time and dramatically slow down the database instance. For this reason, two new options, INSTANT and NOCOPY, were introduced. INSTANT refuses to execute the ALTER command if the data files need to be modified, and NOCOPY refuses if the clustered index (primary key), and thus the entire table, needs to be rebuilt:

SQL> ALTER TABLE test ADD INDEX (data), ALGORITHM = INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX.Try ALGORITHM=NOCOPY

These commands offer improved security without needing a more detailed knowledge of the internal behavior.

Two other very practical features are Instant ADD COLUMN and HIDDEN COLUMN. The first of these two features lets you create a new column without performing an expensive table copy operation. Creating a new column does not take much longer than inserting a row.

A few restrictions have to be considered: Instant ADD COLUMN does not work on tables with a full-text index, and the new column must be in the last position. These features were also contributed by the MariaDB community, from Alibaba and Tencent.

In contrast, the reverse operation – a DROP COLUMN – starts a complete copy of the table, which takes a long time. Invisible columns were created during a Google Summer of Code project. Instead of deleting a column, the user can simply declare it INVISIBLE (Listing 11) and delete it later when the table is scheduled to be modified anyway.

SQL> ALTER TABLE test MODIFY COLUMN bla int INVISIBLE, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.001 sec)
SQL> SELECT * FROM test LIMIT 1;
+----+-----------+---------------------+
| id | data      | ts                  |
+----+-----------+---------------------+
| 3  | Some data | 2018-09-11 17:47:04 |
+----+-----------+---------------------+

If the column is referenced explicitly, it can still be displayed or filled, but admins do need to take a look at the application code if they want to remove columns:

SQL> SELECT * FROM test LIMIT 1;
+---+-----------+-------------------+-----+
| 3 | Some data |2018-09-11 17:47:04|NULL |
+---+-----------+-------------------+-----+

Long-running open transactions are a problem because a database might need to maintain locks that block other applications, and it might need to hold all data changes back to the oldest open transaction to make that data available. Experienced database developers are aware of the problem and work carefully, but if you don't take this into account, you will provoke blocking or, in the worst case, a complete collapse of the database.

For this scenario, MariaDB 10.3 now lets the admin close the connection for long-running idle transactions with the variables idle_readonly_transaction_timeout, idle_readwrite_transaction_timeout, and, for both together, idle_transaction_timeout (Listing 12).

SQL> SET SESSION idle_readonly_transaction_timeout = 5;
SQL> START TRANSACTION READ ONLY;
SQL> do sleep(6);
Query OK, 0 rows affected (6,000 sec)
SQL> do sleep(6);
ERROR 2006 (HY000): MySQL server has gone away

Proxy and Network

MariaDB is used in combination with high-availability (HA) proxies. Connections arriving via a TCP/IP proxy often identify the IP address of the proxy and not the IP address of the original server that sent the request, which makes it difficult to assign a connection to an application or a specific server in case of problems. In this case, the MariaDB security mechanism, which is based on IP addresses, is leveraged.

To solve these problems, HA Proxy defines the PROXY protocol [6], which MariaDB now supports on both the client and server sides. In this way, the original IP addresses can be seen again on a server. On the server side, the PROXY protocol is enabled by a variable:

[mysqld]
proxy_protocol_neworks = localhost,192.168.2.0/24

Now it is possible to override the default TCP keep-alive parameters of the operating system for MariaDB. The admin achieves this with the MariaDB system variables tcp_keepalive_time, tcp_keepalive_interval, and tcp_keepalive_probes, which are to be used analogously to the operating system variables.

Disk Management

MariaDB is extensible through plugins (modules) and encourages the community to develop and contribute them. One such plugin is called DISKS, which lets you query the storage capacity level through the database. This is especially important if the database admin does not have direct access to the operating system and makes it easier to monitor the disks relevant to the database. The DISKS plugin ships with MariaDB by default – you only need to load it; however, an unexpected problem appears because the plugin is not fully mature (beta):

SQL> INSTALL SONAME 'disks';
ERROR 1126 (HY000): Can't open shared library 'disks.so' (errno: 1, Loading of beta plugin DISKS is prohibited by --plugin-maturity=gamma)

The server only allows plugins with at least gamma quality (one level lower than the quality of the server itself: stable).

The gradation is as follows: unknown, experimental, alpha, beta, gamma, stable. Unfortunately, this variable, which is intended to protect the server from loading plugins of insufficient quality, cannot be changed online. A database restart is required. After restarting the database, you can query the disk with built-in SQL resources (Listing 13).

SQL> SELECT Disk, Path
, ROUND(Total/1024/1024, 0) AS Total_GB
, ROUND(Used/1024/1024, 0) AS Used_GB
, ROUND(Available/Total*100, 1) AS Free_Pct
FROM information_schema.disks
WHERE disk LIKE '/dev/%';
+-----------+----------------------+----------+---------+----------+
| Disk      | Path                 | Total_GB | Used_GB | Free_Pct |
+-----------+----------------------+----------+---------+----------+
| /dev/sdb1 | /                    | 183      | 23      | 82.4     |
| /dev/sdb3 | /home                | 704      | 485     | 26.0     |
| /dev/sda1 | /home/mysql/database | 164      | 75      | 49.4     |
+-----------+----------------------+----------+---------+----------+

Variables and Status Info

MariaDB system variables and status information are laws unto themselves. New variables are constantly being added; sometimes a few old ones drop away. The following is a brief overview of some system variables and status information not yet discussed that have changed with MariaDB 10.3.

The secure_timestamp variable influences how and whether a connection can change its local session timestamp. The following InnoDB system variables have been dropped and are therefore banned from appearing in the MariaDB configuration file. If you do not remove these variables from my.cnf, the MariaDB instance will not start after the upgrade:

• innodb_file_format
• innodb_file_format_check
• innodb_file_format_max
• innodb_large_prefix
• innodb_mtflush_threads
• innodb_use_mtflush

Feature status information shows which features MariaDB has used and how often. New variables are:

• Feature_system_versioning
• Feature_invisible_columns
• Feature_json
• Feature_custom_aggregate_functions

The table open cache (TOC) implements all status information already available in MySQL 5.7 to better monitor the behavior of the TOC:

• Table_open_cache_active_instances
• Table_open_cache_hits
• Table_open_cache_misses
• Table_open_cache_overflows

Finally, the innodb_buffer_pool_load_incomplete status information indicates whether loading of the InnoDB buffer pool has been completed or is still running after the restart of the instance.

Logging

MariaDB has two powerful logfiles: the general query log for all queries and the slow query log for slow queries. Both logfiles have new exclusion criteria that define what types of statements should not be logged. The log_disabled_statements variable with its possible values slave and sp (stored procedures) is responsible for the general query log, whereas the log_slow_disabled_statements variable with its possible values admin, call, slave, and sp is responsible for the slow query log.

Another way to set filters for the slow query log is to use the log_slow_filter variable. Queries that use priority queue optimization for file sort operations can now also be logged with the filsort_priority_queue variable. These queries are then also potential candidates for query tuning.

The information_schema database (also called I_S) has also improved slightly. Its tables have been optimized and are now expected to consume far less memory. System-Versioned Tables and Columns are now visible in the corresponding I_S.TABLES and I_S.COLUMNS tables, with the I_S.CHECK_CONSTRAINTS table already introduced with MariaDB 10.2, as well.

Replication

Some changes for semi-synchronous replication have appeared after a long wait. Alibaba has built in the Semisync plugin, so the functionality has now migrated directly into the server code. This step is intended to improve performance.

In asynchronous replication, two new variables have been added that the admin can use to control for which errors the system should try again to execute a command (slave_transaction_retry_errors) and how long it should wait before trying (slave_transaction_retry_interval). This change comes from the Spider developers camp.

Three status variables – Rpl_transactions_multi_engine, Transactions_gtid_foreign_engine, and Transactions_multi_engine – can be employed to determine whether transactions have been executed using different transactional storage engines (InnoDB, RocksDB, TokuDB) and whether it might make sense to use one table for each storage engine with the variable gtid_pos_auto_engines, which updates the GTIDs.

On the Galera cluster side, the plugin has been updated to the latest version. With the state snapshot transfer (SST) rsync method you can use stunnel, if available, to achieve data-in-transit encryption. The new variable wsrep_reject_queries makes it possible to reject requests with an error message while waiting for a node.

Future Outlook

MariaDB 10.3 is ready to use in production. What's next? The MariaDB roadmap for version 10.4 [7] has two items on the agenda: security and Oracle compatibility. Furthermore, Kentoku Shiba seems to be continuing the development of his Spider storage engine.

In terms of InnoDB, the Instant ADD COLUMN is now followed by Instant DROP COLUMN; INVISIBLE does not seem to be enough. Like MySQL, MariaDB is also being write-optimized. Query parallelization and server-managed configuration (my.cnf) are also on the list. The people at MariaDB are unlikely to run out of ideas for what can be improved any time soon.

MariaDB 10.3 offers numerous new features that admins will want to test or use. Although the distributions deliver outdated MariaDB versions, MariaDB itself offers good repositories for all popular distros.