Page MenuHomePhabricator
Create Task
Maniphest T42626

Standardise type of timestamp database fields (MySQL)
Closed, ResolvedPublic
Actions

Description

MediaWiki is using 14 char field as binary or varbinary or data type timestamp. Please clean this up, by using only one data type for all of them.

tables.sql contains a sentence about this:

-- The MySQL table backend for MediaWiki currently uses
-- 14-character BINARY or VARBINARY fields to store timestamps.
-- The format is YYYYMMDDHHMMSS, which is derived from the
-- text format of MySQL's TIMESTAMP fields.
--
-- Historically TIMESTAMP fields were used, but abandoned
-- in early 2002 after a lot of trouble with the fields
-- auto-updating.

Following fields are timestamp:

user_newpass_time binary(14),
user_touched binary(14) NOT NULL default '',
user_email_authenticated binary(14),
user_email_token_expires binary(14),
user_registration binary(14),
user_editcount int
user_last_timestamp varbinary(14) NULL default NULL
page_touched binary(14) NOT NULL default '',
rev_timestamp binary(14) NOT NULL default '',
ar_timestamp binary(14) NOT NULL default '',
cl_timestamp timestamp NOT NULL,
ipb_timestamp binary(14) NOT NULL default '',
ipb_expiry varbinary(14) NOT NULL default '',
img_timestamp varbinary(14) NOT NULL default '',
oi_timestamp binary(14) NOT NULL default '',
fa_deleted_timestamp binary(14) default '',
fa_timestamp binary(14) default '',
us_timestamp varbinary(14) NOT NULL,
rc_timestamp varbinary(14) NOT NULL default '',
rc_cur_time varbinary(14) NOT NULL default '',
wl_notificationtimestamp varbinary(14)
exptime datetime
tc_time binary(14) NOT NULL
log_timestamp binary(14) NOT NULL default '19700101000000',
job_timestamp varbinary(14) NULL default NULL,
qci_timestamp binary(14) NOT NULL default '19700101000000'
pr_expiry varbinary(14) NULL,
pt_timestamp binary(14) NOT NULL,
pt_expiry varbinary(14) NOT NULL default '',
mr_timestamp binary(14) NOT NULL

Version: 1.20.x
Severity: normal

Details

Reference
bz40626
Show related patches Customize query in gerrit

Related Objects
Search...

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Ladsgroup added a comment.Jun 27 2020, 11:15 PM

I encountered this when doing T230428: Migrate tables.sql to abstract schema and probably do it with this task (or do it later) but I have a question. What about Postgres? Right now, mysql uses (var)binary but postgres uses TIMESTAMPTZ. Should I change it to varbinary for consistency or do hacks to make MySQL varbinary and Postgres TIMESTAMPTZ?

Krinkle unsubscribed.Jun 28 2020, 5:42 PM
Krinkle added a comment.Jul 13 2020, 5:05 PM

Related: T57912: Language#formatExpiry needs database connection

Ladsgroup claimed this task.Aug 9 2020, 7:34 PM

It seems I need to do this to achieve the abstract schema.

Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptAug 9 2020, 7:34 PM
taavi subscribed.Aug 24 2020, 9:41 AM
gerritbot added a comment.Sep 12 2020, 10:02 PM

Change 626854 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Introduce TimestampType for handling custom db type in doctrine

https://gerrit.wikimedia.org/r/626854

gerritbot added a comment.Sep 17 2020, 7:05 PM

Change 626854 merged by jenkins-bot:
[mediawiki/core@master] Introduce TimestampType for handling custom db type in doctrine

https://gerrit.wikimedia.org/r/626854

ReleaseTaggerBot added a project: MW-1.36-notes (1.36.0-wmf.10; 2020-09-22).Sep 17 2020, 9:00 PM
gerritbot added a comment.Sep 29 2020, 9:39 PM

Change 630955 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate page_restrictions to abstract schema

https://gerrit.wikimedia.org/r/630955

gerritbot added a comment.Oct 3 2020, 6:24 PM

Change 631936 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Trim whitespace from $expiry in Database::decodeExpiry()

https://gerrit.wikimedia.org/r/631936

gerritbot added a comment.Oct 3 2020, 6:24 PM

Change 631937 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate user_groups to abstract schema

https://gerrit.wikimedia.org/r/631937

gerritbot added a comment.Oct 18 2020, 8:55 AM

Change 634767 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Add custom option called "allowInfinite" for Timestamp

https://gerrit.wikimedia.org/r/634767

gerritbot added a comment.Oct 18 2020, 5:24 PM

Change 634767 merged by jenkins-bot:
[mediawiki/core@master] Add custom option called "allowInfinite" for Timestamp

https://gerrit.wikimedia.org/r/634767

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.14; 2020-10-20); removed MW-1.36-notes (1.36.0-wmf.10; 2020-09-22).Oct 18 2020, 6:00 PM
gerritbot added a comment.Oct 19 2020, 9:17 AM

Change 630955 merged by jenkins-bot:
[mediawiki/core@master] Migrate page_restrictions to abstract schema

https://gerrit.wikimedia.org/r/630955

gerritbot added a comment.Oct 19 2020, 7:35 PM

Change 631937 merged by jenkins-bot:
[mediawiki/core@master] Migrate user_groups to abstract schema

https://gerrit.wikimedia.org/r/631937

Ladsgroup mentioned this in T266486: Schema change to turn user_last_timestamp.user_newtalk to binary(14).Oct 26 2020, 4:37 PM
Ladsgroup mentioned this in T267335: Drop default of protected_titles.pt_expiry .Nov 5 2020, 3:14 PM
Ladsgroup mentioned this in T267399: Drop default of ip_changes.ipc_rev_timestamp.Nov 6 2020, 1:10 PM
Ammarpad mentioned this in T267767: Drop default of revactor_timestamp.Nov 12 2020, 4:31 AM
gerritbot added a comment.Nov 18 2020, 10:10 AM

Change 641701 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Migrate job to abstract schema

https://gerrit.wikimedia.org/r/641701

gerritbot added a comment.Nov 21 2020, 10:50 PM

Change 642685 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Add patch to set wl_notificationtimestamp BINARY(14)

https://gerrit.wikimedia.org/r/642685

gerritbot added a comment.Nov 21 2020, 11:02 PM

Change 641701 merged by jenkins-bot:
[mediawiki/core@master] Migrate job to abstract schema

https://gerrit.wikimedia.org/r/641701

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.20; 2020-12-01); removed MW-1.36-notes (1.36.0-wmf.14; 2020-10-20).Nov 22 2020, 12:01 AM
gerritbot added a comment.Nov 22 2020, 12:10 AM

Change 642685 merged by jenkins-bot:
[mediawiki/core@master] Add patch to set wl_notificationtimestamp BINARY(14)

https://gerrit.wikimedia.org/r/642685

Ladsgroup mentioned this in T268391: Schema change for timestamp fields of jobs table.Nov 22 2020, 12:32 AM
Ladsgroup mentioned this in T268392: Schema change for watchlist.wl_notificationtimestamp going binary(14) from varbinary(14).Nov 22 2020, 12:35 AM
Ladsgroup mentioned this in T269463: Drop cl_timestamp.Dec 4 2020, 6:36 PM
gerritbot added a comment.Dec 13 2020, 5:48 PM

Change 649102 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate uploadstash to abstract schema

https://gerrit.wikimedia.org/r/649102

Ladsgroup mentioned this in T270055: Schema change for timestamp field of uploadstash.Dec 13 2020, 7:30 PM
gerritbot added a comment.Dec 13 2020, 7:37 PM

Change 649102 merged by jenkins-bot:
[mediawiki/core@master] Migrate uploadstash to abstract schema

https://gerrit.wikimedia.org/r/649102

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.22; 2020-12-15); removed MW-1.36-notes (1.36.0-wmf.20; 2020-12-01).Dec 13 2020, 8:00 PM
nshahquinn-wmf updated the task description. (Show Details)Dec 18 2020, 6:20 PM
gerritbot added a comment.Dec 20 2020, 10:22 PM

Change 651001 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] [WIP] Migrate filearchive to abstract schema

https://gerrit.wikimedia.org/r/651001

gerritbot added a comment.Dec 23 2020, 4:04 AM

Change 651001 merged by jenkins-bot:
[mediawiki/core@master] Migrate filearchive to abstract schema

https://gerrit.wikimedia.org/r/651001

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.25; 2021-01-05); removed MW-1.36-notes (1.36.0-wmf.22; 2020-12-15).Dec 23 2020, 5:00 AM
Ladsgroup closed subtask T270032: Find a solution for cl_timestamp as Resolved.Jan 3 2021, 12:25 PM
Ammarpad added a subtask: T271062: Force truncation of objectcache table during database schema update.Jan 3 2021, 3:20 PM
Ammarpad closed subtask T271062: Force truncation of objectcache table during database schema update as Resolved.Jan 20 2021, 12:31 PM
Ladsgroup mentioned this in T272511: Drop default of oldimage.oi_timestamp.Jan 20 2021, 5:08 PM
Ladsgroup mentioned this in T272512: Apply outstanding schema changes for "objectcache" tables in production (exptime, flags, modtoken).Jan 20 2021, 5:15 PM
gerritbot added a comment.Jan 23 2021, 5:06 AM

Change 657949 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate ipblocks to abstract schema

https://gerrit.wikimedia.org/r/657949

gerritbot added a comment.Jan 23 2021, 7:14 PM

Change 657949 merged by jenkins-bot:
[mediawiki/core@master] Migrate ipblocks to abstract schema

https://gerrit.wikimedia.org/r/657949

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.28; 2021-01-26); removed MW-1.36-notes (1.36.0-wmf.25; 2021-01-05).Jan 23 2021, 8:00 PM
Ladsgroup mentioned this in T273358: Schema change for dropping defaults of ipb_timestamp and ipb_expiry.Jan 30 2021, 12:31 PM
Ladsgroup mentioned this in T273359: Schema change for renaming name_title_timestamp on archive table.Jan 30 2021, 12:39 PM
Ladsgroup mentioned this in T273360: Schema change for dropping default of img_timestamp and making it binary(14).Jan 30 2021, 12:47 PM
gerritbot added a comment.Feb 26 2021, 12:54 PM

Change 667158 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate recentchanges table to abstract schema

https://gerrit.wikimedia.org/r/667158

gerritbot added a comment.Feb 27 2021, 5:49 PM

Change 667158 merged by jenkins-bot:
[mediawiki/core@master] Migrate recentchanges table to abstract schema

https://gerrit.wikimedia.org/r/667158

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.33; 2021-03-02); removed MW-1.36-notes (1.36.0-wmf.28; 2021-01-26).Feb 27 2021, 6:00 PM
Ladsgroup added a comment.Feb 27 2021, 6:13 PM

So far this happens with timestamp types in MySQL:

  • Most of them are being migrated to use binary(14) instead of varbinary(14)
  • Except the ones that hold expiry dates. These fields hold "infinite" as value and that's a valid Postgres timestamp so it works across platforms *shrugs*
  • We are dropping empty string as default value of these fields, empty string is not a valid timestamp, use null instead.
  • categorylinks uses mysql native datetime datatype which is really really hard to turn into binary(14). We left it like that for now.
  • objectcache was also similar but it gets cleaned up in new installations so we changed it to binary(14).
Ladsgroup mentioned this in T276156: Drop default of rc_timestamp.Mar 1 2021, 7:53 PM
gerritbot added a comment.Mar 4 2021, 8:21 PM

Change 668548 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Add schema change for img_timestamp varbinary(14) to binary(14)

https://gerrit.wikimedia.org/r/668548

gerritbot added a comment.Mar 4 2021, 9:16 PM

Change 668548 merged by jenkins-bot:
[mediawiki/core@master] Add schema change for img_timestamp varbinary(14) to binary(14)

https://gerrit.wikimedia.org/r/668548

DannyS712 mentioned this in T276734: ORES tests failing: Field 'rc_timestamp' doesn't have a default value.Mar 7 2021, 11:50 PM
Ladsgroup mentioned this in T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production.Mar 10 2021, 11:49 PM
gerritbot added a comment.Mar 13 2021, 7:53 PM

Change 671679 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate archive table to abstract schema

https://gerrit.wikimedia.org/r/671679

gerritbot added a comment.Mar 13 2021, 8:57 PM

Change 671683 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Set ar_timestamp in PurgeEntityDataJobTest

https://gerrit.wikimedia.org/r/671683

gerritbot added a comment.Mar 13 2021, 9:31 PM

Change 671683 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Set ar_timestamp in PurgeEntityDataJobTest

https://gerrit.wikimedia.org/r/671683

ReleaseTaggerBot edited projects, added MW-1.36-notes (1.36.0-wmf.35; 2021-03-16); removed MW-1.36-notes (1.36.0-wmf.33; 2021-03-02).Mar 13 2021, 10:00 PM
gerritbot added a comment.Mar 14 2021, 5:53 AM

Change 671679 merged by jenkins-bot:
[mediawiki/core@master] Migrate archive table to abstract schema

https://gerrit.wikimedia.org/r/671679

Ladsgroup mentioned this in T278619: ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007.Mar 27 2021, 12:52 PM
Ladsgroup mentioned this in T282371: Schema change for dropping default of ar_timestamp.May 9 2021, 11:49 AM
Ladsgroup mentioned this in T282372: Schema change for dropping default of page_touched.
Ladsgroup mentioned this in T282373: Schema change for dropping default of user_touched.May 9 2021, 11:52 AM
Ladsgroup mentioned this in T283461: Change datatype of cl_timestamp from timestamp to binary(14).May 23 2021, 1:45 PM
Ladsgroup closed this task as Resolved.May 23 2021, 1:47 PM

With migration of all tables that have timestamp to abstract schema, this is now done (T230428: Migrate tables.sql to abstract schema)

To recap:

  • All timestamps are now binary(14), without empty string as default (some have null, some have 1970lotsofzeros)
  • The exception being timestamps that are used for expiry and then can accept "infinity". They are varbinary(14).
  • Only one exception: categorylinks (cl_timestamp). It's timestamp and hopefully will be tackled once categorylinks is normalized (T222224) and stops being the monster it is now. (T283461: Change datatype of cl_timestamp from timestamp to binary(14))
Ladsgroup mentioned this in T289894: Create proposal for table/schema to store campaign registration data [high priority].Nov 17 2021, 10:39 PM
Maintenance_bot moved this task from Incoming to Done on the User-Ladsgroup board.Nov 17 2021, 11:15 PM
Umherirrender mentioned this in T298212: Research items from Drift tracker report (December 2021) and create specific tasks to fix the issues.Dec 22 2021, 7:52 PM
Umherirrender mentioned this in T298554: Fix mismatching field type of archive.ar_timestamp on wmf wikis.Jan 4 2022, 7:37 PM
Umherirrender mentioned this in T298555: Fix mismatching field type of logging.log_timestamp on wmf wikis.
Umherirrender mentioned this in T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis.
Umherirrender mentioned this in T298557: Fix mismatching field type of page.page_touched on wmf wikis.
Umherirrender mentioned this in T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis.
Umherirrender mentioned this in T298559: Fix mismatching field type of querycache_info.qci_timestamp on wmf wikis.
Umherirrender mentioned this in T298560: Fix mismatching field type of revision.rev_timestamp on wmf wikis.
Umherirrender mentioned this in T298565: Fix mismatching field type of user table for columns user_email_authenticated, user_email_token, user_email_token_expires, user_newpass_time, user_registration, user_token, user_touched on wmf wikis.Jan 4 2022, 8:23 PM
Umherirrender mentioned this in T298652: Standardise type for br_timestamp column.Jan 5 2022, 8:06 PM
Umherirrender mentioned this in T298656: Standardise type for transcode_time_* columns.Jan 5 2022, 8:26 PM
Umherirrender removed a project: Patch-For-Review.
Umherirrender mentioned this in T300446: Standardise type for timestamp columns in OAuth extension.Jan 29 2022, 9:49 PM
Umherirrender mentioned this in T300575: Standardise default for cu_changes.cuc_timestamp on CheckUser extension.Jan 31 2022, 9:14 PM
Umherirrender mentioned this in T300576: Standardise type for timestamp columns in CentralAuth extension.Jan 31 2022, 9:22 PM
Umherirrender mentioned this in T306747: Standardise timestamps globalblocks.gb_expiry/global_block_whitelist.gbw_expiry on GlobalBlocking extension.Apr 23 2022, 7:51 PM
Umherirrender mentioned this in T310420: Standardise type for timestamp column in Flow extension.Jun 11 2022, 5:13 PM
Umherirrender mentioned this in T310423: Standardise type for timestamp columns in LiquidThreads extension.Jun 11 2022, 9:47 PM
Umherirrender mentioned this in T310437: Standardise type for timestamp columns in FlaggedRevs extension.Jun 12 2022, 12:38 PM
Umherirrender mentioned this in T310444: Standardise type for timestamp columns in SecurePoll extension.Jun 12 2022, 7:43 PM
Umherirrender mentioned this in T310447: Standardise type for timestamp columns in CentralNotice extension.Jun 12 2022, 8:20 PM
Umherirrender mentioned this in T310448: Standardise type for timestamp columns in ContentTranslation extension.
Umherirrender mentioned this in T310449: Standardise type for timestamp columns in PageTriage extension.
Umherirrender mentioned this in T310452: Standardise type for change_time column in Wikibase repo.Jun 12 2022, 9:23 PM
gerritbot added a comment.May 9 2023, 2:32 PM

Change 631936 abandoned by Ladsgroup:

[mediawiki/core@master] Trim whitespace from $expiry in Database::decodeExpiry()

Reason:

not needed anymore. Feel free to restore if you need it

https://gerrit.wikimedia.org/r/631936

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL