MySQL Timestamp Compatibility - sebiboga/testlink-upgraded GitHub Wiki

MySQL 5.7.24+ TIMESTAMP Compatibility

Issue #137

Fixed MySQL timestamp column compatibility issues for MySQL 5.7.24+ support.

Problem

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:

inventory table

  • creation_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • modification_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ❌

baseline_l1l2_context table

  • begin_exec_ts timestamp DEFAULT CURRENT_TIMESTAMP
  • end_exec_ts timestamp DEFAULT CURRENT_TIMESTAMP
  • creation_ts timestamp DEFAULT CURRENT_TIMESTAMP ❌ (3 columns!)

Error Message

ERROR 1293: Incorrect table definition; 
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP 
in DEFAULT or ON UPDATE clause

Solution

inventory table

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+

baseline_l1l2_context table

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,

DATETIME vs 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

Verification

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 = TIMESTAMP

Installation

The fix is included in the updated schema:

mysql -u root -p testlink < install/sql/mysql/testlink_create_tables.sql

Tested Versions

  • ✅ MySQL 5.7.24
  • ✅ MySQL 8.0
  • ✅ MariaDB 10.3+

Documentation

See docs/MYSQL_TIMESTAMP_COMPATIBILITY.md for detailed technical information.

Related Links

Status: ✅ Resolved
Issue: #137
Commit: 1223956e1, 94bce74e2