#1067 - Invalid default value for 'timestamp'

Question

I transferred a WordPress website from a shared hosting plan using Plesk to a VPS server with a newer Plesk version. When trying to upload the MYSQL database I am getting the following error:

Error SQL query: Copy

CREATE TABLE `XZu2B8_wc_reserved_stock` (
  `order_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `stock_quantity` double NOT NULL DEFAULT 0,
  `timestamp` datetime NOT NULL DEFAULT current_timestamp(),
  `expires` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`order_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

MySQL said: Documentation

#1067 - Invalid default value for 'timestamp'

Does anyone have any idea how to fix that?

Answer 1

This was introduced only in mysql 5.6

For mysql 5.5 you need following trigger and you need to remove the DEFAULTs FIRST create the table:

CREATE TABLE `XZu2B8_wc_reserved_stock` (
  `order_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `stock_quantity` double NOT NULL DEFAULT 0,
  `timestamp` datetime NOT NULL,
  `expires` datetime NOT NULL ,
  PRIMARY KEY (`order_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Then create the trigger

DELIMITER $$

 CREATE TRIGGER before_insert_XZu2B8_wc_reserved_stock
 BEFORE INSERT ON XZu2B8_wc_reserved_stock
 FOR EACH ROW
 BEGIN
    IF NEW.`timestamp` IS NULL THEN
       SET NEW.`timestamp`= NOW();
    END IF;
    IF NEW.`expires` IS NULL THEN
       SET NEW.`expires`= NOW();
    END IF;
 END$$
 DELIMITER ;