Creation of modx_versionx_delta table fails on MariaDB 10.4 with `NO_ZERO_DATE`

[atorockio]

Summary

When installing or upgrading to VersionX 3.3.0 on MODX Revolution 2.8.8 with MariaDB 10.4.34 running strict SQL modes including NO_ZERO_DATE, the install fails while creating the modx_versionx_delta table with:

ERROR 1067 (42000): Invalid default value for 'time_end'

This occurs:

• when upgrading from VersionX 2.x, and
• on a fresh installation on a brand-new MODX 2.8.8 site with no previous VersionX installed.

Environment

• MODX: 2.8.8
• VersionX: 3.3.0 (installed via MODX.com provider)
• DB: MariaDB 10.4.34
• sql_mode (global and session):

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

(Output from SELECT @@GLOBAL.sql_mode; and SHOW VARIABLES LIKE 'sql_mode';)

Steps to Reproduce

A) Upgrade path

1. In MODX (2.8.8) Package Manager, upgrade VersionX to 3.3.0 (from MODX.com provider).
2. Installation fails during table creation.

B) Fresh install path (also fails)

1. Use a clean MODX 2.8.8 instance. Ensure no VersionX transports exist under core/packages/ (deleted/empty).
2. In Package Manager, download VersionX 3.3.0 from the MODX.com provider.
3. Install; creation of modx_versionx_delta fails with the same error.

Actual Result

Installer error:

Could not create table `modx_versionx_delta` SQL: CREATE TABLE `modx_versionx_delta` (`id` INTEGER unsigned NOT NULL AUTO_INCREMENT, `principal_package` VARCHAR(128) NOT NULL DEFAULT 'core', `principal_class` VARCHAR(128) NOT NULL, `principal` INT(10) unsigned NOT NULL DEFAULT '0', `type_class` VARCHAR(128) NOT NULL, `milestone` VARCHAR(128) NOT NULL DEFAULT '', `time_start` TIMESTAMP NOT NULL, `time_end` TIMESTAMP NOT NULL, PRIMARY KEY (`id`), INDEX `principal_class` (`principal_class`), INDEX `principal` (`principal`), INDEX `time_start` (`time_start`), INDEX `time_end` (`time_end`)) ENGINE=InnoDB ERROR: Array ( [0] => 42000 [1] => 1067 [2] => Invalid default value for 'time_end' ) 

Suspected Cause

In Commit 0a65b02, the explicit defaults for start_time and end_time are removed. According to the MySQL docs:

TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute, are automatically declared as DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE. See Section 5.1.10, “Server SQL Modes”.