Plesk upgrade fails: Incorrect datetime value: '0000-00-00 00:00:00' for column 'received' at row 1

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

  OR

  ===> Plesk database scheme upgrade has been started.
  
  ===> Plesk database scheme was not upgraded completely. See installation log for details.
  
  
  Stopping sw-cp-serverd: [ OK ]
  cat: /proc/net/if_inet6: No such file or directory
  Starting sw_engine service... done
  Starting sw_cp_server service... done
  Starting mysql service... already started
  Starting named service... not installed
  Starting mailer service... already started
  Starting spamfilter service... already started
  Starting drweb service... unused
  Starting tomcat service... not installed
  Starting apache service... already started
  Starting xinetd service... already started
  ***** 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.
  exit status 1

OR

• ERROR while trying to execute SQL query, the query was: ALTER TABLE `avstatistics` ADD KEY `date_type` (type,`date`) 

• MySQL version 5.7 is installed:

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

• The 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

This is a Plesk bug with ID PPPM-6145, which is planned to be fixed in future Plesk updates.

Resolution

As a workaround:

1. Temporary disable the SQL strict mode by adding the line below under the [mysqld] section of the file /etc/my.cnf on CentOS/RHEL-based distributions or /etc/mysql/my.cnf for Debian/Ubuntu-based distributions:

   CONFIG_TEXT: 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:

   • for CentOS/RHEL-based distributions:

     # service mysqld restart

   • for Debian/Ubuntu-based distributions:

     # service mysql restart

3. Perform Plesk upgrade.

4. After the upgrade is completed, roll back the changes: comment out or delete the string that was added on the step 1.

5. Restart the MySQL service:

   • for CentOS/RHEL-based distributions:

     # service mysqld restart

   • for Debian/Ubuntu-based distributions:

     # service mysql restart