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