Error while importing/restoring a database in Plesk: Unknown collation: ‘utf8mb4_unicode_ci’

Symptoms

• The following error occurs on an attempt to import a database dump:

  PLESK_ERROR: Unable to import the database_name dump:
  ERROR 1273 (HY000) at line 6: Unknown collation: ‘utf8mb4_unicode_520_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′

• Collation entry does not exist in the database:

  # plesk db
  MariaDB [psa]> SHOW COLLATION LIKE ‘utf8mb4_unicode_520_ci’;
  Empty set (0.00 sec)

Cause

Invalid character set and collation.

Resolution

Edit the database dump:

1. Connect to the server via SSH;

2. Create a database dump:

   # plesk db dump database_name > ~/database_dump.sql

3. Correct the database dump by using sed:

   # sed -i ‘s/utf8mb4_unicode_520_ci/utf8_general_ci/g’ database_dump.sql
   # sed -i ‘s/utf8mb4/utf8/g’ database_dump.sql

4. Import the database.

Another way is to upgrade MySQL or MariaDB to a higher version with reference to the article How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux, where ‘utf8mb4_unicode_520_ci’ collation present by default.