What Is New in MariaDB 10.2
MariaDB 10.2 is a significant release packed with new SQL features, compatibility improvements, and enhanced functionality. This version brings MariaDB closer to common SQL standards and other database systems, making it a more powerful and versatile choice.
| Category | Key Changes |
|---|---|
| New Features | Window Functions, Common Table Expressions, JSON Functions, COMPRESSED Column Storage Format |
| Compatibility | Oracle-like Sequences, PL/SQL Compatibility Mode, CHECK CONSTRAINT Enforcement |
| Replication | Global Transaction ID (GTID) for multi-master, Enhanced Parallel Replication |
| Security | ALTER USER, Password Validation Plugin, Default sql_mode includes STRICT_TRANS_TABLES |
| Deprecated & Removed | Deprecated: InnoDB monitor tables, OQGRAPH engine. Removed: PBXT storage engine |
What are the major new SQL features in 10.2?
The headline features are Window Functions and Common Table Expressions (CTEs). Window functions like ROW_NUMBER() and RANK() let you perform complex calculations over groups of rows without collapsing them. CTEs, especially recursive ones, are a game-changer for writing hierarchical queries in a much cleaner way than old methods with temporary tables.
You also get a suite of built-in JSON functions like JSON_EXTRACT() and JSON_OBJECT(). While it's not a full document store, it provides solid tools for handling semi-structured data stored in text columns, which is incredibly common in modern applications.
How does 10.2 improve compatibility with other databases?
This release makes porting applications from Oracle or PostgreSQL significantly easier. The new SEQUENCE engine provides Oracle-style sequences, a more flexible alternative to AUTO_INCREMENT. The PL/SQL compatibility mode allows for parsing some Oracle-specific syntax, reducing friction when migrating stored procedures.
Finally, the CHECK constraint is now fully enforced. This was a long-standing difference from the SQL standard and other databases, and its enforcement ensures data integrity rules defined in your schema are actually respected.
What changes were made to replication?
Global Transaction ID (GTID) support was extended to include domain_id, enabling true multi-master replication setups. This allows you to have multiple replication topologies without worrying about transaction ID collisions between masters.
Parallel replication was also improved for better performance on slave servers. In practice, this means your replicas can apply transactions from the master faster, reducing replication lag, especially under heavy write loads on the primary server.
Were there any important security updates?
Yes, user management got a major upgrade with the ALTER USER statement. This provides a standardized way to modify user accounts, which is more robust than directly manipulating the mysql.user table. The password validation plugin allows administrators to enforce strong password policies.
Perhaps the most impactful change for developers is the new default sql_mode, which now includes STRICT_TRANS_TABLES. This matters because it makes MariaDB stricter by default-it will now throw errors for invalid data inserts instead of issuing warnings and truncating data, preventing a whole class of silent data corruption bugs.
What features were deprecated or removed?
The PBXT storage engine was completely removed. If you were still using it, this is a hard break and requires a migration to another engine like InnoDB. The InnoDB monitor tables (INNODB_MONITOR) and the OQGRAPH engine were marked as deprecated, signaling that they will be removed in a future release.
This cleanup is part of the project's focus on maintaining a modern and efficient codebase. It's a good idea to check your applications for any usage of these deprecated features to ensure future compatibility.
FAQ
Can I use Window Functions in my existing queries?
Yes, absolutely. Window functions are additive and can be incorporated into your current SELECT statements. They work by performing calculations across a set of table rows that are somehow related to the current row, without grouping them into a single output row.
Does the new strict default sql_mode break my application?
It might if your application was relying on the old behavior of silently truncating data. Upon upgrading, test your application thoroughly. You might see new errors for operations that previously only generated warnings. You can revert the mode, but fixing the data handling is the better long-term solution.
What should I use instead of the deprecated InnoDB monitors?
You should use the Information Schema (INFORMATION_SCHEMA) tables and Performance Schema (PERFORMANCE_SCHEMA) tables for monitoring InnoDB status. These are standard, more powerful interfaces for getting diagnostic information about your database.
Are the new JSON functions as powerful as a dedicated NoSQL database?
No, they are not. They provide convenient ways to validate, query, and manipulate JSON data stored in a text column. For simple JSON document handling within a relational structure, they are perfect. For complex document-based applications, a dedicated document store is still more appropriate.
Why would I use a SEQUENCE instead of AUTO_INCREMENT?
Use a sequence when you need a unique number that isn't tied to a specific table, or when you need more control over the numbering (e.g., cycling, caching, or ordering). AUTO_INCREMENT is simpler and remains the best choice for standard primary key generation on a per-table basis.