View Revisions: Issue #6998

Back to Issue
Summary 0006998: Detect support for zero-based date and timestamp columns in MySQL and mark them as NULL-able
Revision 2025-07-18 16:30 by pbelov
Description In older versions of MySQL it is possible to mark timestamp (and date) columns as NOT NULL yet default value is an invalid ZERO-based value:

SymmetricDS should:
1. Detect zero-based defaults for timestamp columns
2. Mark them as NULL-able in the table model
Revision 2025-07-18 20:21 by pbelov
Description In older versions of MySQL it is possible to mark timestamp and date columns as both NOT NULL and an ISO-invalid ZERO-based value as DEFAULT.
Starting from MySQL 8.x this is no longer allowed , unless the NO_ZERO_DATE database setting is turned off manually.

SymmetricDS should:
1. Detect zero-based defaults for timestamp and date columns
2. Mark columns as NULL-able in the table model
3. Remove default value (since it is essentially NULL).
Revision 2025-07-18 16:30 by pbelov
Steps To Reproduce -- Set up MySQL to allow ZERO-based dates https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
-- ... disable strict mode ( remove STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE from sql_mode in /etc/mysql/my.cnf )
-- Create table on MySQL
-- Configure to replicate it to PostgreSQL
-- Add a few zero-based values
CREATE TABLE test_zero_datetimes (
  id int NOT NULL,
  created_date date NOT NULL DEFAULT '0000-00-00',
  updated_ts timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  confirmed_time time NOT NULL DEFAULT '00:00:00',
  PRIMARY KEY (id)
) ;
Revision 2025-07-18 20:21 by pbelov
Steps To Reproduce -- Set up MySQL to allow ZERO-based dates https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
-- ... disable strict mode ( remove STRICT_TRANS_TABLES, NO_ZERO_DATE from sql_mode in /etc/mysql/my.cnf )
-- Create table on MySQL
-- Configure to replicate it to PostgreSQL
-- Add a few zero-based values
CREATE TABLE test_zero_datetimes (
  id int NOT NULL,
  created_date date NOT NULL DEFAULT '0000-00-00', -- Invalid ISO value!
  updated_ts timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', -- Invalid ISO value!
  confirmed_time time NOT NULL DEFAULT '00:00:00', -- Midnight is a valid ISO value.
  PRIMARY KEY (id)
) ;
Revision 2025-07-18 16:30 by pbelov
Additional Information
Revision 2025-07-18 20:21 by pbelov
Additional Information Expected at the target (non-MySQL database):
CREATE TABLE test_zero_datetimes (
    id int4 NOT NULL,
    created_date date NULL,
    updated_ts timestamp NULL,
    confirmed_time time NOT NULL DEFAULT '00:00:00',
    CONSTRAINT test_zero_datetimes_pkey PRIMARY KEY (id)
);
Revision 2025-07-23 02:38 by pbelov
Description In older versions of MySQL (before 5.7) it is possible to mark timestamp and date columns as both NOT NULL and insert an ISO-invalid ZERO-based value (also use it as DEFAULT expression).

Starting from MySQL 5.7 this is no longer allowed by default, unless the NO_ZERO_DATE (and STRICT mode) database setting is turned off manually.

SymmetricDS should:
1. Detect zero-based defaults for timestamp and date columns
2. Mark columns as NULL-able in the table model
3. Remove default value (since it is essentially NULL).