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