View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006998 | SymmetricDS | Improvement | public | 2025-07-18 16:30 | 2025-08-30 12:37 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | assigned | Resolution | open | ||
| Product Version | 3.16.0 | ||||
| Target Version | 3.16.7 | ||||
| Summary | 0006998: Detect support for zero-based date and timestamp columns in MySQL and mark them as NULL-able | ||||
| 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). | ||||
| 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) ) ; | ||||
| 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) ); | ||||
| Tags | No tags attached. | ||||
|
|
Query to detect MySQL system settings regarding Zero-based Date Null values: SELECT @@GLOBAL.sql_mode If either NO_ZERO_DATE and STRICT_TRANS_TABLES flags are set, then zero-based dates are NOT allowed. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-07-18 16:30 | pbelov | New Issue | |
| 2025-07-18 16:30 | pbelov | Status | new => assigned |
| 2025-07-18 16:30 | pbelov | Assigned To | => pbelov |
| 2025-07-18 20:21 | pbelov | Summary | Detect zero-based defaults for timestamp columns in MySQL and mark them as NULL-able => Detect zero-based defaults for timestamp and date columns in MySQL and mark them as NULL-able |
| 2025-07-18 20:21 | pbelov | Description Updated | View Revisions |
| 2025-07-18 20:21 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-07-18 20:21 | pbelov | Additional Information Updated | View Revisions |
| 2025-07-23 02:38 | pbelov | Target Version | => 3.16.5 |
| 2025-07-23 02:38 | pbelov | Summary | Detect zero-based defaults for timestamp and date columns in MySQL and mark them as NULL-able => Detect support for zero-based date and timestamp columns in MySQL and mark them as NULL-able |
| 2025-07-23 02:38 | pbelov | Description Updated | View Revisions |
| 2025-07-23 02:38 | pbelov | Note Added: 0003267 | |
| 2025-08-07 14:46 | pbelov | Target Version | 3.16.5 => 3.16.6 |
| 2025-08-30 12:37 | pbelov | Target Version | 3.16.6 => 3.16.7 |