Dates (datetime) defaults are not accepted by mysql 8, so certain tables are not created (fix included).

[Poikilos]

• XCloner version: 4.7.1
• mysql (used for restore): Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

I didn't fill out the prerelease issue form since the issue isn't marked solved. Also, there was no way to test the prerelease of XCloner since the old instance of the server where I used XCloner is already deleted. I hope this issue report and fix is helpful.

Sometimes tables have a default datetime that is not accepted (not having a timezone offset may be the reason), such as in the following command in database-backup.sql:

CREATE TABLE `wp_9dghqw_comments` (
  `comment_ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_karma` int NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_parent` bigint unsigned NOT NULL DEFAULT '0',
  `user_id` bigint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

The result of the restore via mysql source command includes the following errors (spread out through long output, but I found them again by re-running table create commands from it):

ERROR 1067 (42000): Invalid default value for 'comment_date'
ERROR 1067 (42000): Invalid default value for 'link_updated'
ERROR 1067 (42000): Invalid default value for 'post_date'
ERROR 1067 (42000): Invalid default value for 'user_registered'
ERROR 1067 (42000): Invalid default value for 'scheduled_date_gmt'
ERROR 1067 (42000): Invalid default value for 'date_created_gmt'
ERROR 1067 (42000): Invalid default value for 'log_date_gmt'
ERROR 1067 (42000): Invalid default value for 'comment_date'
ERROR 1067 (42000): Invalid default value for 'created'
ERROR 1067 (42000): Invalid default value for 'link_updated'
ERROR 1067 (42000): Invalid default value for 'post_date'
ERROR 1067 (42000): Invalid default value for 'user_registered'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'timestamp'
ERROR 1067 (42000): Invalid default value for 'date_created'
ERROR 1067 (42000): Invalid default value for 'access_granted'

On running initially, there were therefore also many INSERT errors due to the table not existing.

The fix was to add the following to the top of the SQL:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00";

However, I didn't want to risk double entries, therefore I ran a limited version of the SQL only involving the failed tables and got the site to work. That was done using output from a script I made (comments explain how it can help you fix a failed restore, and sources for info): https://github.com/Poikilos/linux-preinstall/blob/master/utilities-server/wordpress-xcloner-validator.py. A further issue may be if there where any kind of referential integrity enforcement, even some entries from other tables wouldn't get inserted. That doesn't seem to be an issue in this case. In any case, the two SQL lines above caused the CREATE TABLE commands from database-backup.sql to work.