MariaDB 10.2 Beta

Posted on octubre 3, 2016 by

With the release of MariaDB 10.2.2, the newest version of MariaDB has entered the beta stage. In this article, I’ll give an overview of the main new features in MariaDB 10.2. The focus of MariaDB 10.2 can be seen from the categories that the features fall into:

  • Analytics
  • Security
  • Consistency
  • Performance
  • Storage Engines
  • Lifted limitations

Analytics – Window functions
Window functions are popular in Business Intelligence where there’s a desire to, for example, in a report table, include on every row, a calculation based on the other rows that compare a value of the current row with the other rows. MariaDB’s implementation now support all generally used Window Functions from the SQL standards.https://mariadb.com/kb/en/mariadb/window-functions/

Analytics – CTE
A typical query in many aspects that has needed some tricks before is to do a hierarchical query to fetch a parent and all its children in a tree like structure that could be for example the categories and subcategories (and their subcategories and so on) of a product catalog. This is now possible to do in a single query by making use of the recursive Common Table Expression WITH RECURSIVE. Both the standard non-recursive and recursive expressions are supported.

Security
Limits can now be set on user accounts for how many queries, updates or connections can be made for a specific user account per hour. Also SSL/TLS encryption options have been introduced for users, permitting only encrypted connections for a user, that the user make use of a certificate with certain specifics (issuer, subject) that can be set and that a specific cipher is used for encryption.https://mariadb.com/kb/en/mariadb/create-user/

MariaDB 10.2 introduces two new statements for user management as well. ALTER USER is used to, of course, change any settings on the user, like required authentication type or limitations on amount of queries per hour. SHOW CREATE USER logically shows the CREATE statement that would create the user you’re referring to.

Consistency – CHECK constraints, DEFAULT
A long lasting wish from users are met in MariaDB 10.2 where it’s finally possible to make use of CHECK constraints. With CHECK constraints you can put a condition on every value in a row to meet a certain criteria, for example that a column only permits values that are non-negative or that the value of one column has to be smaller than another column’s value.

MariaDB 10.2 supports the use of functions and expressions in the DEFAULT constraint of a column. Also, BLOB and TEXT columns can now have DEFAULT values. See CREATE TABLE for more information.

Performance
There are several things that add to improved performance, like the new InnoDB engine and optimizer changes, but there are also internal server changes that give a nice boost to the overall performance of the database server:

  • The creation of new connections inside the server has been modified so that the creation is much faster than before. When there are plenty of connection threads in the server, we’ve measured a significant speedup.
  • Temporary tables for InnoDB can be put in a separate location, which allows for using separate storage for them.

Storage Engines – InnoDB, CONNECT
InnoDB from MySQL 5.7 is included in MariaDB 10.2 and it has been merged with all MariaDB specific functionality like MariaDB’s encryption, compression, defragmentation. Also, Galera, the clustering technology is integrated with InnoDB.

MariaDB includes a storage engine for data integration called the CONNECT engine. With the CONNECT engine, it’s possible to connect to remote data sources and expose them as tables inside MariaDB. Together with MariaDB 10.2, a new JDBC table type has been introduced. It allows it to connect to other data sources, for example other databases over JDBC. The CONNECT engine with this feature is also available in MariaDB 10.0 and 10.1.

Lifted limitations

  • Some applications (especially financial ones) require more decimals than 30, which has been the limit so far in MariaDB. This limit has been raised to 38.
  • In MariaDB 10.2 the expression of Virtual Columns can be longer than 252 characters and the expressions can include other virtual columns. Also user defined functions are now supported in the expression.
  • VIEWs can now include subqueries in the FROM clause.
  • The same temporary tables can be used several times in a query. This has been a long lasting limitation in MariaDB and MySQL.
  • ANALYZE TABLE used to lock the whole table when analysing key distribution. Locking of the table is not needed anymore.

Other useful enhancements

  • libmysqlclient has been removed from the server. It has been replaced by MariaDB’s own Connector/C.
  • INFORMATION_SCHEMA includes USER_VARIABLES which lists all user defined variables.
  • EXPLAIN FORMAT=JSON has been expanded to include the used sort_key in filesort and outer_ref_condition in SELECTs.
  • Analytical query performance improvement – If a derived table, view or CTE includes a GROUP BY clause it would be internally processed with temporary tables, which would then be materialized and used by the parent query. If the parent query has restrictions on the GROUP BY columns, this new optimization applies them early to exclude non-matching groups from materialization.