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