An attempt to restore 'psa' database from a dump fails with: Data truncated for column 'event_type' at row

Symptoms

Restoration of the psa database fails with:

CONFIG_TEXT: 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. Unpack the .gz backup file from which you want to restore:

   # gunzip mysql.daily.dump.0.gz

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

   # vi mysql.daily.dump.0

5. Find the line that starts with:

   CONFIG_TEXT: INSERT INTO `exp_event` VALUES

6. Put the cursor at the beginning of this line and type "dd" to remove it.
   Once done, the content must look like this:

   CONFIG_TEXT: --
   -- Dumping data for table `exp_event`
   --
   
   LOCK TABLES `exp_event` WRITE;
   /*!40000 ALTER TABLE `exp_event` DISABLE KEYS */;
   /*!40000 ALTER TABLE `exp_event` ENABLE KEYS */;
   UNLOCK TABLES;

7. Save the changes and close the file.

8. Restore the database from the unpacked file:

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