Page MenuHomePhabricator

MySQL's TIMESTAMP type is affected by server time_zone setting
Open, Needs TriagePublic

Description

Currently, DatabaseMysqlBase does not set the time_zone setting, meaning the server's configured time zone will be used. This may or may not be UTC (+00:00), which is problematic in the few places where an actual timestamp column is in use (rather than binary(14), varbinary(14), or datetime), as most MediaWiki code assumes UTC.

For example, on my system, the query SELECT MIN(cl_timestamp) FROM categorylinks WHERE cl_to = 'All_stub_articles', when run against an enwiki DB dump from October 2015, returns 2014-06-17 05:11:10, because time_zone is SYSTEM and system_time_zone is EST. The actual UTC value stored by MySQL or MariaDB is 2014-06-17 09:11:10.

Fixing new data could be done by having MediaWiki set time_zone, though is that going to cause problems with statement-based replication? Would it be better to switch to one of the other types instead of or in addition to this?

Fixing existing data may require a script to be written to adjust categorylinks.cl_timestamp, as well as at least some of these columns in extensions' tables (which will have to be checked individually). However, it would be hard to be sure any adjusted timestamp is correct because the timestamp may have been generated either by MediaWiki or the server, and the server's time zone could have changed after the timestamp was stored.

  • BlueSpiceExtensions: bs_review_steps.revs_timestamp
  • DjangoAnalytics: squidlog.timestamp, squidrecord.timestamp, bannerimpression_raw.timestamp, bannerimpressions.timestamp, landingpageimpression_raw.timestamp, landingpageimpressions.timestamp
  • FlickrAPI: FlickrAPI.expiration
  • GooglePlaces: GooglePlaces.expiration
  • MathSearch: mathlog.math_timestamp,mathperformance.timestamp, mathindex.mathindex_timestamp, mathobservation.mathobservation_timestamp
  • UIFeedback: uifeedback.uif_created, uifeedbackreviews.uifr_created

See also: T42626: Standardise type of timestamp database fields (MySQL)

Event Timeline

PleaseStand raised the priority of this task from to Needs Triage.
PleaseStand updated the task description. (Show Details)
PleaseStand added a project: Wikimedia-Rdbms.
PleaseStand added subscribers: PleaseStand, aaron.
Restricted Application added subscribers: StudiesWorld, Aklapper. · View Herald TranscriptNov 20 2015, 2:04 AM
Legoktm added a subscriber: Legoktm.Dec 5 2015, 8:30 AM
Krinkle added a subscriber: Krinkle.

Short version: Using native timestamp fields is generally considered a bad practice in Wikimedia software. They are, as such, not used in MediaWiki core, nor in any bundled or WMF-deployed extensions. This ticket tracks use of it in a few extensions.

Dinoguy1000 updated the task description. (Show Details)Jul 21 2019, 4:40 AM
Dinoguy1000 added a subscriber: Dinoguy1000.

I've removed a couple of extensions from the OP which have been archived in the years since this task was created. (There's probably one or two more that should be archived too, but they shouldn't be removed unless they actually get archived probably...)