Unable to restore a database from a dump / backup on the Plesk server: errno: 150 "Foreign key constraint is incorrectly formed"

Symptoms

When restoring a database from a dump / backup, the operation fails with one of the following error messages:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` database < database.sql

ERROR 1005 (HY000) at line 15: Can't create table `database`.`table` (errno: 150 "Foreign key constraint is incorrectly formed")

# zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `roundcubemail`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin --default-character-set=utf8

ERROR 1215 (HY000) at line 15: Cannot add foreign key constraint

Cause

Unclear.

Resolution

Solution I

1. Connect to your server via SSH.

2. Restore the database with SET SESSION FOREIGN_KEY_CHECKS=0; key:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" -uadmin database < database.sql

   For example, to restore the roundcubemail database from the latest Plesk daily dump, run the command:

   # zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `roundcubemail`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;"

Solution II

1. Connect to your server via SSH.
2. Open database dump file database.sql in a text editor (for example, vi editor).

3. Add SET FOREIGN_KEY_CHECKS=0;  after USE `database`; line: 

   CONFIG_TEXT: ....
   USE `database`;
   SET FOREIGN_KEY_CHECKS=0;
   ....

4. Save the changes and close the file.
5. Restore the database.