Changes & Improvements in MariaDB 10.1

MariaDB 10.1 is a previous stable version of MariaDB. The first stable release was in October 2014.

For details on upgrading from MariaDB 10.0, see Upgrading from MariaDB 10.0 to 10.1.

The following lists the major new features in MariaDB 10.1:

Implemented Features

Galera

• Galera, a true multi-master solution, is a standard part of MariaDB 10.1.
• Two new Information Schema tables for examining wsrep information, WSREP_MEMBERSHIP and WSREP_STATUS (MDEV-7053)

Encryption

• Table, Tablespace and log Encryption.

Page Compression

• InnoDB/XtraDB Page Compression
• Page compression for FusionIO.

Replication

• Optimistic mode of in-order parallel replication (MDEV-6676)
• domain_id based replication filters - see CHANGE MASTER TO (MDEV-6593)
• Enhanced semisync replication; Wait for at least one slave to acknowledge transaction before committing (MDEV-162).
• Triggers can now be run on the slave for row-based events.
• Dump Thread Enhancements from Google. Makes multiple slave setups faster by allowing concurrent reading of binary log. (MDEV-7257)
• Commits in certain instances in parallel replication complete immediately, avoiding losing throughput when many transactions need conflicting locks. See binlog_commit_wait_count.
• RESET_MASTER is extended with TO # which allows one to specify the number of the first binary log. (MDEV-8469)
• Due to the implementation of SQL standards-compliant behavior when dealing with Primary Keys with Nullable Columns, in certain edge cases, there may be replication issues when replicating from a MariaDB 10.0 master to a MariaDB 10.1 slave using statement-based replication. See MDEV-12248.

Roles

• SET DEFAULT ROLE (MDEV-5210).
• New columns for the INFORMATION_SCHEMA.APPLICABLE_ROLES table.

Optimization

• ORDER BY optimization is improved by several fixes for real-world cases.
• Don't create frm files for temporary tables (MDEV-4260).
• MAX_STATEMENT_TIME can be used to automatically abort long running queries. (MDEV-4427).
• UNION ALL works without usage of a temporary table (MDEV-334). The feature was backported from MySQL 5.7
• Scalability fixes (MDEV-7004). Up to 60% higher throughput in sysbench benchmarks on Power8.
• Make simple queries faster as we call malloc() fewer times.
• Automatic discovery of performance schema tables (simpler mysql_install_db code). (MDEV-4262), Performance Schema tables no longer use .frm files.
• Other Webscale patches (MDEV-6039)
• MDEV-7728 xid cache scalability was significantly improved (by using lock-free hash)

GIS

• Support for Spatial Reference systems for the GIS data (MDEV-60), new REF_SYSTEM_ID column attribute can be used to specify Spatial Reference System ID for columns of spatial data types:
• More functions from the OGC standard added (MDEV-4045):
  • ST_Boundary
  • ST_ConvexHull
  • ST_IsRing
  • ST_PointOnSurface
  • ST_Relate
• INFORMATION_SCHEMA.GEOMETRY_COLUMNS table.
• INFORMATION_SCHEMA.SPATIAL_REF_SYS table.

Syntax

• Consistent support for IF EXISTS, IF NOT EXISTS, and OR REPLACE clauses:
  • CREATE DATABASE (MDEV-7280)
  • CREATE FUNCTION UDF (MDEV-7283)
  • CREATE ROLE (MDEV-7288)
  • CREATE SERVER (MDEV-7285)
  • CREATE USER (MDEV-7288)
  • CREATE VIEW (MDEV-7283)
  • DROP ROLE (MDEV-7288)
  • DROP USER (MDEV-7288)
  • CREATE EVENT and DROP EVENT (MDEV-7281)
  • CREATE INDEX and DROP INDEX (MDEV-7284)
  • CREATE TRIGGER and DROP TRIGGER (MDEV-7286)
• Information Schema plugins can now support SHOW and FLUSH statements. New statements include:
  • SHOW QUERY_RESPONSE_TIME
  • FLUSH QUERY_RESPONSE_TIME
  • SHOW LOCALES
• New columns for the INFORMATION_SCHEMA.APPLICABLE_ROLES and INFORMATION_SCHEMA.VIEWS tables.
• ANALYZE statement provides output that looks like EXPLAIN output, but also includes data from the query execution (how many rows were actually read, etc).
• EXPLAIN FORMAT=JSON is a re-implementation of similar feature in MySQL 5.6
• ANALYZE FORMAT=JSON produces detailed information about the statement execution
• GET_LOCK() now supports microseconds in the timeout, no longer rounding fractions to the nearest integer (MDEV-4018)
• Compound statements can be used outside of stored programs.
• The number of rows affected by a slow UPDATE or DELETE is now recorded in the slow query log - see also mysql.slow_log Table. (MDEV-4412)
• SQL standards-compliant behavior when dealing with Primary Keys with Nullable Columns. Note that this could cause replication issues in certain edge cases when replicating from a MariaDB 10.0 master to a MariaDB 10.1 slave. using statement-based replication. See MDEV-12248.
• Explicit or implicit casts from MAX(string) to INT, DOUBLE or DECIMAL now produce warnings (MDEV-8852).

XtraDB / InnoDB

• Allow up to 64K pages in InnoDB (old limit was 16K) (MDEV-6075).
• The Facebook/Kakao defragmentation patch (see Defragmenting InnoDB Tablespaces) which uses OPTIMIZE TABLE to defragment InnoDB tablespaces).

Collations

• Added the utf8_thai_520_w2, utf8mb4_thai_520_w2, ucs2_thai_520_w2, utf16_thai_520_w2 and utf32_thai_520_w2 collations.

Variables

For a list of all new variables, see System Variables Added in MariaDB 10.1 and Status Variables Added in MariaDB 10.1. Some of these, and other variable-related changes, include:

• INFORMATION_SCHEMA.SYSTEM_VARIABLES gives information, like description and value origin, for system variables (MDEV-6138).
• MDEV-6858 New server variable enforce_storage_engine
• New status variables to show the number of grants on different objects (see Status Variables Added in MariaDB 10.1)
• Default size of query_alloc_block_size changed from 8192 to 16384 and query_prealloc_size from 8192 to 24576 to avoid the need for simple queries with one join to call my_malloc.
• Added variable default-tmp-storage-engine (From MySQL 5.6) (MDEV-6107).
• SET STATEMENT - set variables for the duration of the query (MDEV-5231). This is a backport of Per-query Variable Statement feature of Percona Server 5.6 (which, in turn, is based in MySQL GSoC 2009 project by Joseph Lukas), with many bugs fixed.
• --mysql56-temporal-format option to use the MySQL-5.6 low level formats to store TIME, DATETIME and TIMESTAMP types. (MDEV-5528)
• Backport innodb_default_row_format (MDEV-14904)
• mysqld --help --verbose now shows valid variables for ENUM variables. (MDEV-6137)
• MDEV-6981 New status variables to track MASTER_GTID_WAIT time.
• MDEV-7198 New status variable Slave_skipped_errors.
• --silent-startup mysqld option. If specified, mysqld does not print Notes to the error log during startup.

Plugins

• Password validation plugin API (MDEV-6431).
• simple_password_check password validation plugin. It can enforce a minimum password length and guarantee that a password contains at least a specified number of uppercase and lowercase letters, digits, and punctuation characters.
• cracklib_password_check password validation plugin. It only allows passwords that are strong enough to pass CrackLib test. This is the same test that pam_cracklib.so does, installed by default on many Linux distributions.
• ed25519 authentication plugin for traditional password-based authentication. A new, secure alternative to the old mysql_native_password plugin.

Security

• Enhance security using special compilation options - MariaDB is now compiled with security hardening options by default. It is an additional protection layer that makes new, yet unknown, security vulnerabilities more difficult to exploit. (MDEV-5730)

Security Vulnerabilities Fixed in MariaDB 10.1

Comparison with MySQL

• System Variable Differences Between MariaDB 10.1 and MySQL 5.6
• System Variable Differences Between MariaDB 10.1 and MySQL 5.7

List of all MariaDB 10.1 releases

See Also

• Getting, Installing, and Upgrading MariaDB