Why MySQL 5.6 → 5.7 Upgrades Fail and How to Prevent Rollbacks

Background

A long‑standing project was still using MySQL 5.6. The database had never been formally managed, but the team inherited it and decided to upgrade to a newer version to gain the benefits of improved replication, performance, and stability.

Typical Problems in MySQL 5.6

Replication is prone to lag; MySQL 5.7 introduced parallel replication, allowing multiple threads to apply changes concurrently.

Enabling performance_schema in 5.6 adds noticeable overhead, so many operators keep it disabled as they did in 5.5.

Binlog‑based replication can lose data consistency; later versions added GTID and other mechanisms to improve reliability.

Memory management and file‑handle limits restrict high‑concurrency workloads, making 5.6 unsuitable for heavy traffic compared with 5.7 and newer releases.

Upgrade Attempt and Reasons for Rollback

Before upgrading, developers and DBAs evaluated the migration but overlooked two critical differences between 5.6 and 5.7:

SQL syntax changes : Certain statements accepted in 5.6 are rejected in 5.7. For example, a UNION ALL query that uses LIMIT 1 on each side works in 5.6 but triggers ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT in 5.7.

Case‑sensitivity handling : 5.6 treats identifiers case‑insensitively by default, while 5.7 can be configured to be case‑sensitive, causing mismatched query results when data contains mixed‑case values.

Case‑Sensitive Collation Fix

To align the behavior of 5.7 with 5.6, the database can be created with a binary collation:

CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;

After importing the data, the case‑sensitivity issue disappears, but because the existing data had already been loaded under the wrong collation, the team chose to roll back instead of re‑importing.

Code Demonstrations

Below are representative MySQL sessions that illustrate the syntax error and case‑sensitivity differences.

mysql> create table table1 (id int primary key, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table table2 (id int primary key, name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into table1 (id,name) values (1,'system');
Query OK, 1 row affected (0.01 sec)

mysql> insert into table2 (id,name) values (2,'system2');
Query OK, 1 row affected (0.00 sec)

-- This works in 5.6 but fails in 5.7
mysql> select * from table1 limit 1 -> union all -> select * from table2 limit 1;
ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT

-- Case‑sensitivity example
mysql> insert into table1 (id,name) values (2,'System');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1 where name = 'system';
+----+--------+
| id | name   |
+----+--------+
|  1 | system |
|  2 | System |
+----+--------+
2 rows in set (0.01 sec)

Conclusion

While many assume that moving from MySQL 5.6 to 5.7 is straightforward, this case shows that overlooked syntax incompatibilities and collation differences can force a painful rollback. Proper pre‑upgrade testing of SQL statements and character‑set settings is essential to avoid such setbacks.