Upgrading to MariaDB 10.11 from MariaDB 10.6: A Comprehensive Guide

MariaDB is a popular open-source relational database management system that has seen significant growth and development over the years. With the release of MariaDB 10.11, there are numerous changes, improvements, and new features that users should be aware of. In this blog post, we will delve into the key aspects of upgrading to MariaDB 10.11 from MariaDB 10.6.

New Features & Improvements in MariaDB 10.11

1. Authentication:
   • GRANT to PUBLIC: This feature allows granting privileges to the public. MDEV-5215
   • Separate SUPER and READ ONLY ADMIN privileges. MDEV-29596
2. Optimizer:
   • The ANALYZE FORMAT=JSON now displays the time spent in the query optimizer. MDEV-28926
3. Information Schema:
   • Addressed performance issues when reading the Information Schema Parameters table. MDEV-29104
   • Resolved full table scan issues in the Information Schema Parameters and Information Schema Routines tables. MDEV-20609
4. System Versioning:
   • Introduced a system versioning setting, system_versioning_insert_history, to allow history modification. MDEV-16546
   • mariadb-dump now supports dumping and restoring historical data. MDEV-16029
5. InnoDB:
   • The variables innodb_write_io_threads and innodb_read_io_threads are now dynamic, allowing their values to be changed without restarting the server. MDEV-11026
6. Windows:
   • Implemented passwordless login for the MariaDB root user, specifically for OS admin users. MDEV-26715
7. General:
   • Renamed several slow query variables:
     • log_slow_min_examined_row_limit to min_examined_row_limit
     • log_slow_query to slow_query_log
     • log_slow_query_file to slow_query_log_file (Note: This was named log_slow_query_file_name in the MariaDB 10.11.0 preview release.)
     • log_slow_query_time to long_query_time
   • The replicate_rewrite_db is now a system variable and not just an option. MDEV-15530
8. Variables:
   • For a comprehensive list of all new variables, refer to the official documentation on System Variables Added in MariaDB 10.11 and Status Variables Added in MariaDB 10.11.

Deprecated Options:

The following options have been deprecated in MariaDB 10.11. They haven’t been removed yet but will be in future versions:

• keep_files_on_create: MariaDB now deletes orphan files, making this setting unnecessary.

Options That Have Been Removed or Renamed:

• innodb_log_write_ahead_size: On Linux and Windows, the physical block size of the underlying storage is detected and used, making this option redundant.
• innodb_version: This option has been deemed redundant.
• wsrep_replicate_myisam and wsrep_strict_ddl: Users should use wsrep_mode instead.

Potential Pitfalls:

1. Compression Issues: If you’ve used a non-zlib compreSSIon algorithm in InnoDB or Mroonga in MariaDB 10.6, you’ll need to install the appropriate compreSSIon library to read those tables in 10.11.
2. Configuration Changes: If you’ve customized your MariaDB configuration, you’ll need to review and potentially adjust it to account for the changed, removed, or renamed options.

Benefits of Upgrading to MariaDB 10.11

1. Enhanced Security: With the introduction of new authentication features, MariaDB 10.11 offers improved security mechanisms, ensuring that your data remains protected.
2. Optimized Performance: The new optimizer features and improvements in the Information Schema ensure that queries run faster and more efficiently.
3. Advanced Versioning: The system versioning enhancements provide better control over historical data, making data management more flexible.
4. Dynamic Configuration: With dynamic InnoDB variables, administrators can now tweak performance settings without the need to restart the server, leading to minimal downtime.
5. Improved Windows Support: The passwordless login feature for the MariaDB root user on Windows platforms simplifies the authentication process for OS admin users.
6. Better Query Management: The renaming of slow query variables provides clarity and consistency in query management and monitoring.

In conclusion, upgrading to MariaDB 10.11 from MariaDB 10.6 brings a plethora of new features, improvements, and optimizations. It’s essential to familiarize yourself with these changes to make the most of your MariaDB experience.

Note: For a more detailed understanding and for any specific deprecated options, options that have been removed or renamed, and incompatible changes between versions, it’s recommended to refer to the official MariaDB documentation and release notes.