Lead Image by Adarsh Kummur on Unsplash

Lead Image by Adarsh Kummur on Unsplash

New features in MariaDB 10.3

Mature

Article from ADMIN 49/2019
By
What lacked maturity in MariaDB 10.2 has now been sorted out in version 10.3. We look at the benefits you can reap now.

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.

The Story So Far

In 2008, MySQL was purchased by Sun Microsystems; just a year later, Sun was acquired by Oracle. This development did not suit the founder of MySQL, Monty Widenius, and he founded a new company named MariaDB, in which he continued a fork of MySQL with a few former MySQL developers. This happened at version 5.5, and at the time, you could still confidently speak of a MariaDB drop-in replacement.

As time went by and new features were added, MariaDB and MySQL began to diverge. Almost imperceptibly the wording changed from "drop-in-replacement" to "compatible," and today, the two databases are not even 100% compatible anymore. When it comes to switching, you need to be on your toes!

MariaDB v5.5 was followed by version 10.0. This version leap should have made it clear that MariaDB was already a bit different from MySQL. MariaDB 10.0 added user roles, an audit plugin, a full-text search plugin for CJK languages (Chinese, Japanese, and Korean), parallel replication, replication with global transaction IDs (GTIDs) and multisource replication, and numerous new storage engines (e.g., Cassandra, Connect, Spider, TokuDB), including a Sequence engine.

MariaDB 10.1 was the first MySQL spin-off to declare itself "Galera ready by default." In other words, it could run in a Galera cluster without changes. A lot of work was also invested in data-at-rest encryption: The tables, tablespaces, redo logs, and binary logs could be written to disk in encrypted form. Moreover, the MariaDB ColumnStore – a storage engine that stores the data not line by line, but column by column – was added, which allowed for faster access to specific data for reporting and business intelligence queries.

In the MariaDB 10.2 version, the XtraDB storage engine by Percona was replaced by the InnoDB 5.7 storage engine from MySQL, which allowed the use of virtually all InnoDB features available in MySQL 5.7. The most important of these were probably the InnoDB spatial indexes (GIS indexes that facilitate geographic queries with high performance). Modules for MongoDB and MyRocks also were added.

On the SQL extensions side, window functions (queries over value ranges), recursive and non-recursive common table expressions (a kind of explicit subquery), and CHECK constraints were added. The developers also added new features in GIS, JSON, and GeoJSON and further improvements in terms of security and encryption.

Finally, MariaDB offered its own connectors (C/C++, JDBC, ODBC) under a new license (LGPL), which made it possible to use the connectors for commercial purposes without violating the GPL license. Additionally, a new backup tool – mariabackup – was developed that roughly corresponded to MySQL Enterprise Backup and was a fork of the well-known XtraBackup tool. It was now also possible to flash back the data with the binary logs (i.e., undo data changes), and initial preparations for compatibility with the database from Oracle were initiated.

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.

Listing 1

Installing on Ubuntu

# 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

Listing 2

Installing on CentOS

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

Listing 3

Creating a Sequence

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

Listing 4

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

Listing 5

Sequence Information

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

Listing 6

PL/SQL Block in MariaDB

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

Listing 7

Custom Aggregate Functions

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

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

comments powered by Disqus