Unable to restore the psa database from dump: Data truncated for column ‘event_type’ at row X

Symptoms

• An attempt to restore the 'psa' database from a dump fails with:

  ERROR 1265 (01000) at line 3506: Data truncated for column 'event_type' at row 265

Cause

Unclear.

Resolution

1. Connect to the Plesk server via SSH.

2. Switch to the directory with daily dumps:

   # cd /var/lib/psa/dumps

3. Extract the mysql.daily.dump.X.gz backup file from where to restore the psa database into a file:

   # gunzip -c mysql.daily.dump.0.gz > mysql.daily.dump.0

   Note: The commands are provided for the latest daily dump numbered with "0" as an example, change it in all steps as needed.

4. Open the file in a text editor. In this example, we are using the vi editor:

   # vi mysql.daily.dump.0

   4.1. Find the line that starts with:

   INSERT INTO `exp_event` VALUES

   4.2. Set the courser at the beginning of the line and type dd to remove it.

   4.3. Save the changes and close the file.

5. Restore the database from the decompressed and edited file:

   # cat mysql.daily.dump.0 | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin