Unable to import a database dump in Plesk: Unknown collation: ‘utf8mb4_unicode_ci’

Symptoms

• Unable to import a database dump going to Domains > example.com > Databases > database-name > Import Dump:

  PLESK_ERROR: Unable to import the db_name dump:

  ERROR 1273 (HY000) at line 29: Unknown collation: ‘utf8mb4_unicode_ci’
  Traceback (most recent call last):
  File “/usr/local/psa/admin/sbin/dbbackup”, line 6, in <module>
  File “/usr/local/psa/lib/modules/python/dbbackup/dbbackup.py”, line 99, in main
  restore(options, password)
  File “/usr/local/psa/lib/modules/python/dbbackup/dbbackup.py”, line 88, in restore
  raise Exception(“program ‘mysql’ finished with non-zero exit code: %d” % p.returncode)
  Exception: program ‘mysql’ finished with non-zero exit code: 1

• The current version of MySQL on the server is lower than 5.5.3:

  # mysql -V
  mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

• Failed to copy the content of a database during migration with the following error:

  PLESK_ERROR: Failed to copy content of database ‘example_db’ Migration tools tried to perform operation in 1 attempts: Restoration of MySQL database failed. Most probable reason: Target MySQL server does not contain necessary character set. Most likely it is required to have at least MySQL 5.5 to restore such database. Proposed solutions: *) Obtain another target server which has OS providing appropriate MySQL server version. For example, CentOS 6 provides MySQL 5.1 by default, while CentOS 7 provides MySQL 5.5 by default. *) Connect another external MySQL server to the target server with appropriate MySQL version. Configure that server as default, or specify it in service plan to which subscriptions are assigned to. Remove migrated subscription from the target server and start migration once more. *) Upgrade MySQL server on the target server to appropriate version, then run migration once more (no need to remove subscriptions from target – just run migration over existing domains). For example, for CentOS you could use packages provided by EPEL repositories. This way is not recommended for servers already running in production. Here is the original error message reported by MySQL client when restoring the database: ERROR 1273 (HY000) at line 445: Unknown collation: ‘utf8mb4_unicode_ci’

Cause

The
utf8mb4
Character Set (4-Byte UTF-8 Unicode Encoding) support is available starting from the MySQL 5.5.3 version.

Resolution

For Linux:
Upgrade MySQL to version 5.5 with the steps described in the following KB article: How to upgrade MySQL from 5.1 to 5.5 on Linux?

For Windows
Upgrade MySQL via Plesk following steps from How to upgrade MySQL 5.6 to 5.7 on Windows