Skip to content

MySQL version mismatch during a cPanel migration: keeping databases working

A migration that moves MySQL 5.7 databases to a MySQL 8 destination is not just a copy. Schema changes, default character sets, and authentication plugins all shift.

Moving from MySQL 5.7 to MySQL 8 (or MariaDB 10.3 to 10.6) during a cPanel migration is straightforward in the typical case and breaks specific applications in the edge cases. The breaks are predictable: applications that pin utf8 (which means utf8mb3 in MySQL 8), use mysql_native_password authentication, or rely on now-reserved keywords as column names. The fix is testing the application against the destination version before cutover.

What’s happening

cPanel’s source and destination can run different database engines and versions. The Transfer Tool dumps the source database and imports to the destination. The import succeeds if the destination accepts the SQL. The application then connects, and that is where the version difference shows: deprecated SQL modes, removed keyword tolerance, different character set defaults, and authentication plugin defaults that older clients cannot speak.

How to verify

Check both sides before transfer:

# source
mysql -e "SELECT VERSION();"
mysql -e "SHOW VARIABLES LIKE 'character_set_server';"
mysql -e "SHOW VARIABLES LIKE 'default_authentication_plugin';"
# destination
mysql -e "SELECT VERSION();"
mysql -e "SHOW VARIABLES LIKE 'character_set_server';"
mysql -e "SHOW VARIABLES LIKE 'default_authentication_plugin';"
# application's MySQL connection
grep -r "DB_HOST\|DB_USER" /home/<user>/public_html | head

A test that catches most problems: dump one production database from the source, import to the destination, run a typical application page that does writes. Watch the destination error_log.

The fix

  1. Character set drift — MySQL 8 prefers utf8mb4. Older WordPress installs that specified utf8 will get utf8mb3 columns on the destination, which cannot store emojis. Force the right collation in the dump (mysqldump --default-character-set=utf8mb4) or alter tables post-import.
  2. Authentication plugin — MySQL 8 defaults to caching_sha2_password. PHP 7.0 and older PHP-MySQLi clients cannot connect. Either set default_authentication_plugin=mysql_native_password in /etc/my.cnf or upgrade PHP on the destination.
  3. Reserved keywords — MySQL 8 added new reserved keywords. A column named rank or groups (without backticks) breaks. Either backtick the references in the application or rename the column.
  4. Strict SQL mode — MySQL 8 has stricter defaults; queries that worked under permissive mode in 5.7 may fail. Test the application against the destination with the destination’s defaults. If breaking, set sql_mode in /etc/my.cnf to a more permissive value as a temporary measure.
  5. MyISAM tables — MyISAM still works but is discouraged. If the migration is also a chance to convert to InnoDB, do it explicitly with ALTER TABLE after import, not implicitly during transfer.

If the application is old and the version gap is wide, the safer path is keeping the source MySQL version on the destination (install MariaDB 10.3 explicitly even if newer is available) and upgrading the database later. Stack Harbor handles database-side migrations under migration solutions — book a migration review before the version jump.