View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0007806 | OXID eShop (all versions) | 4.01. Database handling | public | 2025-07-03 16:00 | 2025-07-03 17:07 |
| Reporter | kjunker | Assigned To | |||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | acknowledged | Resolution | open | ||
| Product Version | 6.5.3 | ||||
| Summary | 0007806: Default Value Timestamp is invalid | ||||
| Description | OXID eShop CE/PE/EE v6.x (confirmed with v6.14.1) MySQL 5.7.4x+ Environment: MySQL 5.7.44 (default SQL mode includes NO_ZERO_DATE, STRICT_TRANS_TABLES) OXID eShop's database schema defines several TIMESTAMP columns with NOT NULL DEFAULT '0000-00-00 00:00:00', which was compatible with MySQL 5.6. Starting from MySQL 5.7, this default value is disallowed when NO_ZERO_DATE is active (default setting), resulting in failing INSERT statements if those fields are omitted. A typical example is the OXACTIVEFROM column in the oxarticles table: `OXACTIVEFROM` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', | ||||
| Steps To Reproduce | Running a simple INSERT without explicitly setting any column with the described DEFAULT Value fails: INSERT INTO oxarticles (oxid) SELECT UUID(); [2025-07-03 15:56:44] [22001][1292] Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'OXACTIVEFROM' at row 1 | ||||
| Additional Information | Root Cause: MySQL 5.7 (and higher) treats '0000-00-00 00:00:00' as an invalid value for DATETIME/TIMESTAMP fields when NO_ZERO_DATE is enabled. This was permitted in MySQL 5.6, so existing schema definitions and queries silently relied on this. Potential Impact on OXID: - Setup fails on clean installations with MySQL 5.7+ - Inserts into affected tables fail unless workarounds are applied - Breaking change for hosting environments that upgraded MySQL versions - Limits compatibility with MySQL 5.7+ and newer (including MariaDB forks that follow this rule) Recommended Solutions: Update all affected TIMESTAMP columns (e.g., in oxarticles, oxorder, oxuser, etc.) to use a valid default, such as: `OXACTIVEFROM` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01' | ||||
| Tags | No tags attached. | ||||
| Theme | Not defined | ||||
| Browser | Not defined | ||||
| PHP Version | 8.1 | ||||
| Database Version | MySQL 5.7 | ||||
