Plesk migration fails: Invalid default value for ‘field_name’

Symptoms

• A migration fails with the following error:

  Migration tools tried to perform operation in 3 attempts: Command execution failed on the local server with non-zero exit code.
  command: mysql --defaults-file=/usr/local/psa/var/modules/panel-migrator/sessions/<datetime>/target-server/my_db.cnf -h localhost -P 3306 -uadmin my_db < /usr/local/psa/var/modules/panel-migrator/sessions/<datetime>/target-server/db-dumps/my_db.sql
  exit code: 1
  stdout:
  stderr: ERROR 1067 (42000) at line 487: Invalid default value for 'date_updated'

• A database restoration can also fail with the following error:

  # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin database < /var/lib/mysql/database2
  ERROR 1067 (42000) at line 1022: Invalid default value for 'created_at'

• The source server has Plesk with MySQL 5.6.5, while the target has Plesk with MySQL 5.6.4 or older

• The line 487 of the dump is specified as follows:

  date_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

Cause

This is caused due MySQL bug with ID 27645: Datetime field does not accept default NOW()

Resolution

Apply one of the following solutions:

• Upgrade MySQL on the target server to 5.6.5 or newer: How to upgrade MySQL from 5.1 to 5.5 on Plesk for Linux, How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux

• Modify the database dump:

  1. Log into source Plesk

  2. Export the database dump on the source server and modify the line with the field declaration:

     FROM

     date_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

     TO

     date_updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

     OR

     date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP