🥇MySQL (Percona Server) upgrade from 5.7 to 8.0

Progress doesn’t stand still, so the reasons to upgrade to the latest versions of MySQL are becoming increasingly compelling. Not long ago, we had to update our cozy Percona Server 5.7 clusters to version 8 in one of our projects. This was all happening on the Ubuntu Linux 16.04 platform. How to carry out such an operation with minimal downtime and what issues we faced during the upgrade — read about it in this article.

Preparation

Any database server upgrade is likely to be associated with reconfiguring the database: changes in the requirements for system resource limits and correcting database configs, which need to be cleaned of outdated directives.

Before the upgrade, we will definitely refer to the official documentation:

And we will formulate an action plan:

Correct configuration files by removing outdated directives.
Check compatibility with utilities.
Upgrade slave databases by installing the package percona-server-server.
Upgrade the master by installing the same package.

Let’s break down each item of the plan and see what might go wrong.

IMPORTANT! The procedure for upgrading a MySQL cluster based on Galera has its nuances that are not described in this article. Do not use this instruction in that case.

Part 1: Checking configurations

In MySQL version 8, the query_cachewas removed. In fact, it was declared deprecated back in version 5.7, but now it has also been removed entirely.Therefore, it is necessary to remove related directives. For query caching, external tools can now be used — for example, ProxySQL.

There were also outdated directives about innodb_file_format. While in MySQL 5.7 it was possible to choose the InnoDB format, version 8 now works only with the Barracuda format..

Our conclusion — removing the following directives:

query_cache_type, query_cache_limit and query_cache_size;
innodb_file_format and innodb_file_format_max.

For testing, we will use the Percona Server Docker image. We will place the server config in the directory mysql_config_test, and create directories for data and logs next to it. Example configuration test for percona-server:

mkdir -p {mysql_config_test,mysql_data,mysql_logs}
cp -r /etc/mysql/conf.d/* mysql_config_test/
docker run  --name some-percona -v $(pwd)/mysql_config_test:/etc/my.cnf.d/  -v $(pwd)/mysql_data/:/var/lib/mysql/ -v $(pwd)/mysql_logs/:/var/log/mysql/ -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD} -d percona:8-centos

As a result, either in the Docker logs or in the directory with logs—depending on your configurations—a file will appear that describes the problematic directives.

Here’s what we had:

2020-04-03T12:44:19.670831Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-04-03T12:44:19.671678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-04-03T12:44:19.671682Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

Thus, we needed to further address the encodings and replace the deprecated directive expire-logs-days.

Part 2: Checking working setups

The upgrade documentation contains 2 utilities for checking the database for compatibility. Their use helps the administrator verify the compatibility of the existing data structure.

Let's start with the classic utility mysqlcheck. It’s quite simple to run:

mysqlcheck -u root -p --all-databases --check-upgrade

If no problems are found, the utility will finish with a code of 0:

Moreover, modern versions of MySQL provide the utility mysql-shell (for Percona, this is the package percona-mysql-shell). It replaces the classic mysql client and combines the functions of a client, SQL code editor, and MySQL administration tools. To check the server before an upgrade, you can execute the following command through it:

mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=\/etc\/mysql\/my.cnf

And here are the remarks we received:

Overall, nothing critical—only warnings about encodings. (see below)The overall outcome was:

We decided that the upgrade should proceed without issues.

The note about the warnings above indicates problems with encodings. The fact is that UTF-8 in MySQL until recently was not ‘real’ UTF-8, as it stored only 3 bytes instead of 4. In MySQL 8, this has finally been corrected: the alias utf8 will soon point to the encoding utf8mb4, while the old columns in tables will become utf8mb3.In the future, the encoding utf8mb3. will be removed, but not in this release. Therefore, we decided to correct the encodings already in the working installation of the DBMS after its upgrade.

Part 3: Upgrading servers

What could possibly go wrong when there is such a wonderful plan? Understanding that nuances always happen, we conducted the first experiment on the MySQL dev cluster.

As already mentioned, the official documentation addresses the issue of updating MySQL servers with replicas. The essence of the matter is that you should first update all the replicas (slaves), as MySQL 8 can replicate from a master of version 5.7. The complexity is that we are using a master mastersetup, where the remote master is in read-onlymode. This means that the actual traffic is received by one data center, while the second is a backup.

The topology looks as follows:

The update should start with the replicas mysql replica dc 2, mysql master dc 2 and mysql replica dc 1, and end with the server mysql master dc 1. For extra reliability, we stopped the virtual machines, took snapshots, and just before the update, halted replication with the command STOP SLAVE. Other than that, the update looks like this:

We restart each replica, adding 3 options to the configs: skip-networking, skip-slave-start, skip-log-bin. The thing is, the database update generates binary logs with updates of system tables. These directives ensure that application data will not change in the database, and the binary logs will not include information about updates to system tables. This will help avoid problems when resuming replication.
We install the package percona-server-server. It's important to note that in MySQL 8, do not you need to run the command mysqlupgrade after updating the server.
After a successful start, we restart the server again — without the parameters added in the first step.
We ensure that replication is working successfully: we check SHOW SLAVE STATUS and see that the tables with the counters in the application database are being updated.

All of this seems quite straightforward: the dev update went successfully. Okay, we can calmly plan the nightly update for production.

There were no troubles — we updated prod

However, transferring the successful dev experience to production did not go without surprises.

Fortunately, the update process begins with the replicas, so when we encountered difficulties, we halted the work and restored the replica from the snapshot. Investigating the problems was postponed until the following morning. The logs contained the following entries:

2020-01-14T21:43:21.500563Z 2 [ERROR] [MY-012069] [InnoDB] table: t1 has 19 columns but InnoDB dictionary has 20 columns
2020-01-14T21:43:21.500722Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for db1.t1
2020-01-14T21:43:24.208365Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-01-14T21:43:24.208658Z 0 [ERROR] [MY-010119] [Server] Aborting

Researching archives of various mailing lists in Google led to the understanding that this issue arises due to a MySQL bug. Although it is more likely a bug of the utilities mysqlcheck and mysqlsh.

It turns out that MySQL changed the way data is represented for decimal fields (int, tinyint, etc.), thus a different storage method is used within the mysql-server. If your database was initially in version 5.5 or 5.1 and then you upgraded to 5.7, you may need to perform OPTIMIZE for certain tables. Then MySQL will update the data files by converting them to the current storage format.

This can also be verified using the utility mysqlfrm:

mysqlfrm --diagnostic -vv /var/lib/mysql/db/table.frm
...
 'field_length': 8,
  'field_type': 246, # field format
  'field_type_name': 'decimal',
  'flags': 3,
  'flags_extra': 67,
  'interval_nr': 0,
 'name': 'your_decimal_column',
...

If field_type is equal to 0, then the table uses the old type — it needs to be conducted OPTIMIZE. However, if the value is 246 — you already have the new type. More details on the types can be found in the code.

Moreover, in this bug there is a second possible reason that bypassed us — the absence of InnoDB tables in the system table INNODB_SYS_TABLESPACES, if they were created in version 5.1. To avoid issues during the upgrade, you can utilize the attached SQL script.

Why didn't we encounter such problems on dev? The database is periodically copied from production — thus, tables are recreated.

Unfortunately, on a large working DB, it won't be simple to just execute a widespread OPTIMIZE. The percona-toolkit will help here: the utility pt-online-schema-change is excellent for online OPTIMIZE operations.

The updated plan turned out to be:

Optimize all tables.
Update the databases.

To check it and also find out the update time, we turned off one of the replicas, and for all tables, we executed the following command:

pt-online-schema-change --critical-load Threads_running=150 --alter "ENGINE=InnoDB" --execute --chunk-size 100 --quiet --alter-foreign-keys-method auto h=127.0.0.1,u=root,p=${MYSQL_PASSWORD},D=db1,t=t1

Table updates are performed without lengthy locks because the utility creates a new temporary table, into which it copies data from the main table. At the moment when both tables are identical, the original table is locked and replaced with the new one. In our case, the test run showed that updating all tables would take about a day, but during this time, copying data caused too much disk load.

To avoid this, we added an argument to the command on production --sleep with a value of 10 — this parameter regulates the waiting time after moving a batch of data into the new table. This can reduce load if the running application is sensitive to response time.

After performing the optimization, the update was successful.

… but not completely!

Just half an hour after the update, the client came with a problem. The database was behaving very strangely: connections were periodically starting to drop. Here’s how it looked in monitoring:

The screenshot shows a sawtooth graph, indicating that some MySQL server threads were periodically crashing with an error. The application displayed errors:

[PDOException] SQLSTATE[HY000] [2002] Connection refused

A quick glance at the logs revealed that the mysqld daemon could not obtain the required resources from the operating system. While addressing the errors, we discovered ‘orphaned’ apparmor policy files in the system. These files were created when updating to MySQL 5.7 a couple of years ago and belong to a removed package. Deleting the files and restarting the apparmor service resolved the issue::

# dpkg -S /etc/apparmor.d/cache/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/cache/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/local/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/local/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/usr.sbin.mysqld
mysql-server-5.7: /etc/apparmor.d/usr.sbin.mysqld
# dpkg -l mysql-server-5.7
rc  mysql-server-5.7 5.7.23-0ubuntu0.16.04.1      amd64

systemctl stop apparmor rm /etc/apparmor.d/cache/usr.sbin.mysqld rm /etc/apparmor.d/local/usr.sbin.mysqld rm /etc/apparmor.d/usr.sbin.mysqld systemctl start apparmor

Any operation, even the simplest one, can lead to unexpected problems. And even having a well-thought-out plan does not always guarantee the expected outcome. Now, any update plans for our team include mandatory cleanup of unnecessary files that may have appeared as a result of recent actions.