MAY 23, 2017 — By Ralf Gebhardt
We are happy to announce the general availability (GA) of MariaDB Server 10.2! MariaDB Server 10.2 is the newest major version of MariaDB Server, the fastest growing open source relational database.
MariaDB Server 10.2 is the next evolution after MariaDB Server 10.1. In 10.1 the integration of Galera Cluster as a high availability solution, data-at-rest encryption and other security features like the password validation API have been the key enhancements of MariaDB Server.
Now, with MariaDB Server 10.2.6 GA, new significant enhancements are available for our users and customers, including:
- SQL enhancements like window functions, common table expressions and JSON functions allow new use cases for MariaDB Server
- Standard MariaDB Server replication has further optimizations
- Many area limitations have been removed, which allows easier use and there is no need for limitation handling on the application level
- MyRocks, a new storage engine developed by Facebook, has been introduced, which will further enrich the use cases for MariaDB Server
Window functions are popular in Business Intelligence (BI) where more complex report generation is needed based on a subset of the data, like country or sales team metrics. Another common use case is where time-series based data should be aggregated based on a time window instead of just a current record, like all rows inside a certain time span.
As analytics is becoming more and more important to end users, window functions deliver a new way of writing performance optimized analytical SQL queries, which are easy to read and maintain, and eliminates the need to write expensive subqueries and self-joins.
Common Table Expressions
Hierarchical and recursive queries are usually implemented using common table expressions (CTEs). They are similar to derived tables in a FROM clause, but by having an identification keyword WITH, the optimizer can produce more efficient query plans. Acting as an automatically created temporary and named result set, which is only valid for the time of the query, it can be used for recursive and hierarchical execution, and also allows for reuse of the temporary dataset. Having a dedicated method also helps to create more expressive and cleaner SQL code.
Through the use of virtual columns, the JSON function, JSON_VALUE and the newest indexing feature of MariaDB Server 10.2 on virtual columns, JSON values will be automatically extracted from the JSON string, stored in a virtual column and indexed providing the fastest access to the JSON string.
Using the JSON function JSON_VALID, the new CHECK CONSTRAINTS in MariaDB Server 10.2 guarantee that only JSON strings of the correct JSON format can be added into a field.
Binary Log Based Rollback
The enhanced mysqlbinlog utility delivered with MariaDB Server 10.2 includes a new point-in-time rollback function, which allows a database or table to revert to an earlier state, and delivers binary log based rollback of already committed data. The tool mysqlbinlog is not directly modifying any data, it is generating an “export file” including the reverted statements of the transactions, logged in a binary log file. The created file can be used with the command line client or other SQL tool to execute the included SQL statements. This way all committed transactions up to a given timestamp will be rolled back.
In the case of addressing logical mistakes like adding, changing or deleting data, so far the only possible way has been to use mysqlbinlog to review transactions and fix the problems manually. However, this often leads to data inconsistency because corrections typically only address the wrong statement, thereby ignoring other data dependencies.
Typically caused by DBA or user error, restoring a huge database can result in a significant outage of service. Rolling back the last transactions using point-in-time roll back takes only the time of the extract, a short review and the execution of the reverted transactions – saving valuable time, resources and service.
Start now and learn about the newest evolution of MariaDB Server 10.2:
- Window functions
- Common table expressions
- JSON and GeoJSON functions
- Delayed replication and compressed binary log
- Enforced CHECK constraints
- DEFAULT now allows the use of expressions
- Multiple triggers per table
- Binary Log Based Roll Back
- InnoDB from MySQL 5.7, which is the default engine in MariaDB 10.2
- Indexes for virtual columns
- Limitations on number of connections, queries and forcing of encryption per user. See CREATE USER and look for 10.2 features
About Ralf Gebhardt
Ralf Gebhardt is Product Manager at MariaDB Corporation AB
Sponsored by MariaDB