Repository navigation
MySQL Timestamp Compatibility
sebiboga edited this page Aug 16, 2026
·
1 revision
Fixed MySQL timestamp column compatibility issues for MySQL 5.7.24+ support.
MySQL 5.7.24 introduced a constraint: only ONE TIMESTAMP column per table can have automatic defaults (DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP).
TestLink schema violated this constraint in two tables:
creation_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP-
modification_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP❌
begin_exec_ts timestamp DEFAULT CURRENT_TIMESTAMPend_exec_ts timestamp DEFAULT CURRENT_TIMESTAMP-
creation_ts timestamp DEFAULT CURRENT_TIMESTAMP❌ (3 columns!)
ERROR 1293: Incorrect table definition;
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP
in DEFAULT or ON UPDATE clause
Changed modification_ts to use DATETIME with ON UPDATE:
-- BEFORE
`modification_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- AFTER
`modification_ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,Benefits:
- Maintains creation tracking
- Auto-updates on modification
- Supports MySQL 5.7.24+
Changed execution timestamps to DATETIME:
-- BEFORE
begin_exec_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
end_exec_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
creation_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- AFTER
begin_exec_ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
end_exec_ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
creation_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,| Feature | TIMESTAMP | DATETIME |
|---|---|---|
| Automatic Default | ✅ (1 per table) | ✅ (multiple allowed) |
| Auto Update | ✅ (1 per table) | ✅ (multiple allowed) |
| Storage Range | 1970-2038 | 1000-9999 |
| Timezone Aware | Yes (UTC) | No (local time) |
| Size | 4 bytes | 8 bytes |
Check your database structure:
-- Check inventory table
DESCRIBE inventory;
-- Should show: creation_ts = TIMESTAMP, modification_ts = DATETIME
-- Check baseline_l1l2_context table
DESCRIBE baseline_l1l2_context;
-- Should show: begin_exec_ts = DATETIME, end_exec_ts = DATETIME, creation_ts = TIMESTAMPThe fix is included in the updated schema:
mysql -u root -p testlink < install/sql/mysql/testlink_create_tables.sql- ✅ MySQL 5.7.24
- ✅ MySQL 8.0
- ✅ MariaDB 10.3+
See docs/MYSQL_TIMESTAMP_COMPATIBILITY.md for detailed technical information.
- Developer-Guide - Development setup
- Sample-Database - Database structure
- File-Import-Export - Data import procedures
Status: ✅ Resolved
Issue: #137
Commit: 1223956e1, 94bce74e2