Master MySQL Upgrades with pt‑upgrade: Detect Compatibility Issues Seamlessly

Why MySQL upgrades are tricky

Upgrading MySQL brings new features and performance gains, but new versions may be incompatible with the old one in three main ways: syntax differences, semantic differences (same SQL producing different results), and sometimes slower query performance. Therefore extensive functional and performance testing in a non‑production environment is essential.

Introducing pt‑upgrade

pt‑upgrade is a tool from the Percona Toolkit that checks compatibility by executing the same SQL on two MySQL instances (old and new) and comparing the outcomes.

Row count : are the numbers of rows returned identical?

Row data : is the result set identical?

Warnings : do both instances emit the same warnings?

Query time : is the execution time in the same order of magnitude, or is the new version faster?

Query errors : does a syntax error appear in only one instance?

SQL errors : does a syntax error appear in both instances?

Common usage patterns

Two typical ways to run pt‑upgrade:

Directly compare a SQL file against two instances. # pt-upgrade h=host1 h=host2 slow.log The --type option can specify the file type (slowlog, genlog, binlog, rawlog, tcpdump); the default is slowlog.

Generate a baseline result set on one instance, then reuse it to compare against another instance (useful when the two instances cannot be accessed simultaneously).

# pt-upgrade h=host1 --save-results host1_results/ slow.log
# pt-upgrade host1_results/ h=host2

Demo

A sample test file pt_upgrade_test.sql contains representative statements that often cause upgrade issues:

select "a word a" REGEXP "[[:<:]]word[[:>:]]";
select dept_no, count(*) from employees.dept_emp group by dept_no desc;
grant select on employees.* to 'u1'@'%' identified by '123456';
create table employees.t1(id int primary key, c1 text not null default ('');
select * from employees.dept_emp group by dept_no;

Running pt‑upgrade against MySQL 5.7 (port 3307) and MySQL 8.0 (port 3306) produces detailed class reports. For example:

SQL 3 (grant) : MySQL 5.7 implicitly creates the user, while MySQL 8.0 throws an error. The fix is to split the statement into a CREATE USER followed by GRANT.

SQL 5 (GROUP BY without ORDER BY) : Both versions reject the query because ONLY_FULL_GROUP_BY requires explicit ordering in MySQL 8.0.

SQL 4 (default value for TEXT) : Allowed from MySQL 8.0.13 onward; earlier versions reject it.

SQL 1 (word‑boundary regex) : The Spencer regex library used in 5.7 supports [[:<:]] and [[:>:]], but ICU in 8.0 requires \b.

SQL 2 (ORDER BY syntax) : MySQL 5.7 allows GROUP BY col_name DESC; MySQL 8.0 requires an explicit ORDER BY clause.

Important parameters

--[no]read-only

: By default pt‑upgrade runs only SELECT and SET; use --no-read-only to allow other statements. --save-results: Saves query results to a directory for later comparison. --max-class-size and --max-examples: Limit the number of distinct queries and total examples shown per fingerprint class (defaults: 1000 and 3).

Fingerprinting: normalises SQL by replacing numbers with ?, removing comments, collapsing whitespace, lower‑casing, and merging identical UNIONs.

Precautions

Before running pt‑upgrade, ensure the two instances contain identical data and are not being modified; otherwise false positives may appear. The tool is best used in test or development environments, not directly on production.

MySQL 5.7 → 8.0 upgrade checklist

Do not rely on GROUP BY col_name ASC/DESC; specify an explicit ORDER BY clause.

Regular‑expression syntax changed: ICU replaces the Spencer library, affecting word‑boundary tokens, character‑class names, and the handling of . and parentheses. Use \b for word boundaries and escape special characters as needed.

Default values for BLOB/TEXT/GEOMETRY/JSON columns are now allowed (from 8.0.13).

Conclusion

With pt‑upgrade, DBAs can confidently assess compatibility before a production upgrade, reducing the mental load and risk of hidden issues. As an alternative, MySQL Shell’s util.checkForServerUpgrade() performs a broader 21‑check analysis of server readiness.

Reference links:

https://www.percona.com/doc/percona-toolkit/3.0/pt-upgrade.html

https://bugs.mysql.com/bug.php?id=30241

https://dev.mysql.com/worklog/task/?id=353

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp

https://dev.mysql.com/doc/refman/5.7/en/regexp.html