Symptoms

• Unable to upgrade Plesk from the version 17.0, the following output is shown:

  Migration failed: 17.5.0/2016-12-26-09-56-48_change_dates_default_value.php
  Query failed: UPDATE `APSCatalogUpdates` SET `received` = '1970-01-01 00:00:00' WHERE `received` = '0000-00-00 00:00:00'
  Incorrect datetime value: '0000-00-00 00:00:00' for column 'received' at row 1
  
  ...
  
  ***** problem report ***** 
  ===> Plesk database scheme was not upgraded completely. See installation log for details. 
  Error: An error occurred during performing of installation PREP actions (see log for details). Installation was rolled back. 
  Warning! Not all packages were installed. 
  Please check if your system services are operable. 
  Please resolve this issue and try to install the packages again. 
  If you cannot resolve the issue on your own, contact product technical support for assistance. 
  =========

• MySQL version 5.7 is installed:

  # mysql -V
  mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64)

• SQL strict mode is enabled:

  # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin -i -BN -e 'SELECT @@sql_mode' | egrep 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES'
  IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Cause

Plesk bug #PPPM-6145 .

The fix is considered to be included in one of the next Plesk updates.

Resolution

As a workaround:

1. Disable SQL strict mode by adding the following line under [mysqld] section of the file /etc/mysql/my.cnf :

   # grep sql_mode /etc/mysql/my.cnf
   sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
   

2. Restart the MySQL service:

   # service mysql restart

3. Perform Plesk upgrade.

4. Roll back the changes: comment out or delete the string that was added on the step 1

5. Restart the MySQL service:

   # service mysql restart