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:

  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 needed to restore 'psa' from:

   # gunzip mysql.daily.dump.0.gz

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

   # vi mysql.daily.dump.0

4. Find the line that starts with:

   INSERT INTO `exp_event` VALUES

5. With the cursor standing on the found line hit dd to remove the INSERT INTO block so that this section is left as follows:

   --
   -- 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;

   In case is required to remove more than one line at a time use dXXd. Where XX is the number of lines, e.g. d10d to remove ten lines.

6. Save the changes and close the file.

7. 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