2020-04-16

Upgrading Between Major MariaDB Versions

Hello world, I am still alive (even if this blog hasn't been that much alive lately)!

There have been way too long since my last blog, so I thought it's time to remedy that and write some updates about what I have been doing with MariaDB lately

I am a believer in easy upgrades (this is why we do our best in MariaDB to not removed old syntax, features or variables). This is why I designed MariaDB from the start to be able to upgrade from any previous version directly to any newer version. One should be able to "trivially" upgrade from the first MySQL version (1.0) to the current without any issues (as long as the data is in one of the supported storage engines).

Recently I was involved in a discussion if it's safe to upgrade between major versions of MariaDB (like 10.1 directly to 10.4) and I noticed that had not been documented properly.

I have now fixed that by writing an article about upgrading MariaDB.

I have included the article here to make it easier for everyone to read it.

Happy upgrading!

Upgrading Between Major MariaDB Versions

MariaDB is designed to allow easy upgrades. You should be able to trivially upgrade from ANY earlier MariaDB version to the latest one (for example MariaDB 5.5.x to MariaDB 10.5.x), usually in a few seconds. This is also mainly true for any MySQL version < 8.0 to MariaDB 10.4 and up.

Upgrades are normally easy because:
  • All MariaDB table data files are backward compatible
  • The MariaDB connection protocol is backward compatible.
  • A MariaDB slave can be of any newer version than the master.
MariaDB Corporation regularly runs tests to check that one can upgrade from MariaDB 5.5 to the latest MariaDB version without any trouble. All older versions should work too (as long as the storage engines you were using are still around).

Requirements for Doing an Upgrade Between Major Versions

* Go through the individual version upgrade notes (listed below) to look for any major changes or configuration options that have changed.
* Ensure that the innodb_fast_shutdown variable is not 2 (fast crash shutdown). The default of this variable is 1. The most safe option for upgrades is 0, but the shutdown time may be notable larger with 0 than for 1 as there is a lot more cleanups done for 0.
* Clean shutdown of the server. This is necessary because even if data files are compatible between versions, recovery logs may not be.
* Backup of the database (just in case). At least, take a copy of the mysql data directory with mysqldump --add-drop-table mysql as most of the upgrade changes are done there (adding new fields and new system tables etc).

Note that rpms don''t support upgrading between major versions, only minor like 10.4.1 to 10.4.2. If you are using rpms, you should de-install the old MariaDB rpms and install the new MariaDB rpms before running mysql_upgrade. Note that when installing the new rpms, mysql_upgrade may be run automatically. There is no problem with running mysql_upgrade many times.

Recommended Steps

* If you have a master-slave setup, first upgrade one slave and when you have verified that the slave works well, upgrade the rest of the slaves (if any). Then upgrade one slave to master, upgrade the master, and change the master to a slave.

* If you don't have a master-slave setup, then take a backup, shutdown MariaDB and do the upgrade.

Work Done by mysql_upgrade

The main work done when upgrading is done by running mysql_upgrade. The main things it does are:

* Updating the system tables in the mysql database to the newest version. This is very quick.
* mysql_upgrade also runs mysqlcheck --check-upgrade to check if there have been any collation changes between the major versions. This recreates indexes in old tables that are using any of the changed collations. This can take a bit of time if there are a lot of tables or there are many tables which used the changed collation. The last time a collation changed was in MariaDB/MySQL 5.1.23.

Post Upgrade Work

Check the MariaDB error log for any problems during upgrade.
The common errors are:
* Using obsolete options.  If this is the case, remove them from your my.cnf files.
* Check the manual for new features that have been added since your last MariaDB version.
* Test that your application works as before. The main difference from before is that because of optimizer improvements your application should work better than before, but in some rare cases the optimizer may get something wrong. In this case, you can try to use explain, optimizer trace or optimizer_switch to fix the queries.

If Something Goes Wrong

* First, check the MariaDB error log to see if you are using configure options that are not supported anymore.
* Check the upgrade notices for the MariaDB release that you are upgrading to.
* File an issue in the MariaDB bug tracker so that we know about the issue and can provide a fix to make upgrades even better.
* Add a comment to this manual entry for how we can improve it.

Disaster Recovery

In the unlikely event something goes wrong, you can try the following:
  • Remove the InnoDB tables from the mysql data directory. They are in MariaDB 10.5:
    • gtid_slave_pos
    • innodb_table_stats
    • innodb_index_stats
    • transaction_registry
  • Move the mysql data directory to mysql-old and run mysql_install_db to generate a new one.
  • After the above, you have to add back your old users.
  • When done, delete the mysql-old data directory.

Downgrading

MariaDB server is not designed for downgrading. That said, in most cases, as long as you don't have done any ALTER TABLE or CREATE TABLE statements and you have a mysqldump of your old mysql database , you should be able to downgrade to your previous version by doing the following:

* Do a clean shutdown. For this special case you have to set innodb_fast_shutdown to 0, before taking down the new MariaDB server, to ensure there are no redo or undo logs that need to be applied on the downgraded server.
* Delete the tables in the mysql database (if you didn't use the option --add-drop-table to mysqldump).
* Delete the new MariaDB installation
* Install the old MariaDB version
* Start the server with mysqld --skip-grant-tables
* Install the old mysql database
* Execute in the mysql client FLUSH PRIVILEGES

4 comments:

hartmut said...

Having seen at least two cases now where direct 10.1 -> 10.3 upgrades failed, I tend to object strongly to "You should be able to trivially upgrade from ANY earlier MariaDB version to the latest one". Well, I agree to the "should" part, it *should* be easy, but unfortunately it is not the safe way (and AFAIK anything but upgrades from the most recent previous major version don't seem to get any formal testing either ...)


https://jira.mariadb.org/browse/MDEV-20357

https://jira.mariadb.org/browse/MDEV-22167

Michael "Monty" Widenius said...
This comment has been removed by the author.
Michael "Monty" Widenius said...

I have checked both issues. In both cases an upgrade from 10.1 to 10.2 would have failed also in the same manner (in one case because of a bug in MariaDB 10.2 and in the other case because the database was inconsistent/crashed because it was a copy of a running server). In other words, upgrading 10.1 -> 10.2 -> 10.3 and 10.1 -> 10.3 both would have exactly the same issues.

Another thing is that if the user would have upgrade first to 10.2 and at once upgraded to 10.3 (without testing the application thoroughly with 10.2) things would have worked identically as if the user would have gone directly to 10.3. In other words, if the final destination should be 10.3, there was nothing to gain or loose to go directly to 10.3.

Note that Debian skipped MariaDB 10.2 totally. This means that most Debian users upgraded directly from 10.1 to 10.3 and I am not aware of any bug reports regarding this!

Vicentiu Ciorbaru said...

From the MariaDB Server Feedback Plugin, we are getting the following numbers about people skipping versions during upgrades:

* Number of unique servers that upgraded - 26829
* Number of servers upgrades that skipped 10.0 release - 4073 (15.1%) (from 5.5 to a version > 10.0)
* Number of servers upgrades that skipped 10.1 release - 1883 (7%) (from 10.0 to a version > 10.1)
* Number of servers upgrades that skipped 10.2 release - 3433 (12.7%) (from 10.1 to a version > 10.2)
* Number of servers upgrades that skipped 10.3 release - 58 (0.2%) (from 10.2 to a version > 10.3)