Page MenuHomePhabricator

Apply alter for transcode_time_* columns on wmf wikis
Closed, ResolvedPublic

Description

With T298656: Standardise type for transcode_time_* columns the timestamp columns on the transcode table are altered from varchar(14) to binary(14) , please check production and apply the patch if needed.

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/TimedMediaHandler/+/751797/2/sql/patch-transcode-transcode_timestamp.sql
  2. Where to run those changes: all
  3. When to run those changes: any time
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: beta cluster is running with the new schema
  6. if the data should be made available on the labs replicas and/or dumps: no change of the existing rules
    • s1
    • s2
    • s3
    • s4
    • s5
    • s6
    • s7
    • s8

Related Objects

Mentioned In
rSCHCH9698ccda68e4: Add 2022/change_transcode_T298743.py
Mentioned Here
P22633 dbctl commit (dc=all): 'Repooling after maintenance db1146:3314 (T298743)'
P22626 dbctl commit (dc=all): 'Repooling after maintenance db1146:3314 (T298743)'
P22618 dbctl commit (dc=all): 'Depooling db1146:3314 (T298743)'
P22617 dbctl commit (dc=all): 'Repooling after maintenance db1160 (T298743)'
P22610 dbctl commit (dc=all): 'Repooling after maintenance db1160 (T298743)'
P22608 dbctl commit (dc=all): 'Depooling db1160 (T298743)'
P22607 dbctl commit (dc=all): 'Repooling after maintenance db1149 (T298743)'
P22602 dbctl commit (dc=all): 'Repooling after maintenance db1149 (T298743)'
P22595 dbctl commit (dc=all): 'Depooling db1149 (T298743)'
P22594 dbctl commit (dc=all): 'Repooling after maintenance db1148 (T298743)'
P22585 dbctl commit (dc=all): 'Repooling after maintenance db1148 (T298743)'
P22584 dbctl commit (dc=all): 'Depooling db1148 (T298743)'
P22583 dbctl commit (dc=all): 'Repooling after maintenance db1147 (T298743)'
P22573 dbctl commit (dc=all): 'Repooling after maintenance db1147 (T298743)'
P22572 dbctl commit (dc=all): 'Depooling db1147 (T298743)'
P22571 dbctl commit (dc=all): 'Repooling after maintenance db1144:3314 (T298743)'
P22564 dbctl commit (dc=all): 'Repooling after maintenance db1144:3314 (T298743)'
P22563 dbctl commit (dc=all): 'Depooling db1144:3314 (T298743)'
P22562 dbctl commit (dc=all): 'Repooling after maintenance db1143 (T298743)'
P22556 dbctl commit (dc=all): 'Repooling after maintenance db1143 (T298743)'
P22553 dbctl commit (dc=all): 'Depooling db1143 (T298743)'
P22552 dbctl commit (dc=all): 'Repooling after maintenance db1142 (T298743)'
P22545 dbctl commit (dc=all): 'Repooling after maintenance db1142 (T298743)'
P22542 dbctl commit (dc=all): 'Depooling db1142 (T298743)'
P22541 dbctl commit (dc=all): 'Repooling after maintenance db1141 (T298743)'
P22532 dbctl commit (dc=all): 'Repooling after maintenance db1141 (T298743)'
P22530 dbctl commit (dc=all): 'Depooling db1141 (T298743)'
P22529 dbctl commit (dc=all): 'Repooling after maintenance db1121 (T298743)'
P22526 dbctl commit (dc=all): 'Repooling after maintenance db1121 (T298743)'
P22525 dbctl commit (dc=all): 'Depooling db1121 (T298743)'
T298294: Make primary key filearchive.fa_id unsigned on wmf wikis
T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis
T298656: Standardise type for transcode_time_* columns

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Production tables are varbinary(14) so not sure if this is really needed. Is it? cc @Ladsgroup

Oh yeah, given our charset, anything we have in char or varchar automatically becomes binary or varbinary. It is needed for third parties but not for wmf production (with the exception of wikitech but I fixed its charset a while ago)

The new type in the extension is of fixed length, when production is variable length field, than it may be need fixing as well, like T298558. But invalid is okay as well.

Yup, then needs schema change for removing the var part, otherwise drifttracker gets grumpy + binary(14) is slightly smaller.

So let's add the final schema change needed so it is all clear

@Ladsgroup @Umherirrender do you have the final needed statement? does it require a new patch?

I checked and the same alter table is applicable given that it's modifying a column.

Ladsgroup moved this task from Ready to In progress on the DBA board.

Most of the sections will have empty tables. I run it with replication on these.

Ladsgroup updated the task description. (Show Details)
Ladsgroup updated the task description. (Show Details)

s4 needs to be done manually :/

Change 770461 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/software/schema-changes@master] Add 2022/change_transcode_T298743.py

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

Change 770461 merged by jenkins-bot:

[operations/software/schema-changes@master] Add 2022/change_transcode_T298743.py

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

s4 is under another maintenance work: T298294#7772954 I continue tomorrow.

Mentioned in SAL (#wikimedia-operations) [2022-03-15T11:23:08Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1121 (T298743)', diff saved to https://phabricator.wikimedia.org/P22525 and previous config saved to /var/cache/conftool/dbconfig/20220315-112308-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T11:27:55Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1121 (T298743)', diff saved to https://phabricator.wikimedia.org/P22526 and previous config saved to /var/cache/conftool/dbconfig/20220315-112754-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T12:13:10Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1121 (T298743)', diff saved to https://phabricator.wikimedia.org/P22529 and previous config saved to /var/cache/conftool/dbconfig/20220315-121309-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T12:13:19Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1141 (T298743)', diff saved to https://phabricator.wikimedia.org/P22530 and previous config saved to /var/cache/conftool/dbconfig/20220315-121317-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T12:27:48Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1141 (T298743)', diff saved to https://phabricator.wikimedia.org/P22532 and previous config saved to /var/cache/conftool/dbconfig/20220315-122748-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T13:13:04Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1141 (T298743)', diff saved to https://phabricator.wikimedia.org/P22541 and previous config saved to /var/cache/conftool/dbconfig/20220315-131303-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T13:13:12Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1142 (T298743)', diff saved to https://phabricator.wikimedia.org/P22542 and previous config saved to /var/cache/conftool/dbconfig/20220315-131311-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T13:17:37Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1142 (T298743)', diff saved to https://phabricator.wikimedia.org/P22545 and previous config saved to /var/cache/conftool/dbconfig/20220315-131736-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T14:02:52Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1142 (T298743)', diff saved to https://phabricator.wikimedia.org/P22552 and previous config saved to /var/cache/conftool/dbconfig/20220315-140252-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T14:03:00Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1143 (T298743)', diff saved to https://phabricator.wikimedia.org/P22553 and previous config saved to /var/cache/conftool/dbconfig/20220315-140259-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T14:07:24Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1143 (T298743)', diff saved to https://phabricator.wikimedia.org/P22556 and previous config saved to /var/cache/conftool/dbconfig/20220315-140723-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T14:52:39Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1143 (T298743)', diff saved to https://phabricator.wikimedia.org/P22562 and previous config saved to /var/cache/conftool/dbconfig/20220315-145238-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T14:52:47Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1144:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22563 and previous config saved to /var/cache/conftool/dbconfig/20220315-145246-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T15:01:17Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1144:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22564 and previous config saved to /var/cache/conftool/dbconfig/20220315-150116-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T15:46:32Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1144:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22571 and previous config saved to /var/cache/conftool/dbconfig/20220315-154631-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T15:46:40Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1147 (T298743)', diff saved to https://phabricator.wikimedia.org/P22572 and previous config saved to /var/cache/conftool/dbconfig/20220315-154639-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T15:51:03Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1147 (T298743)', diff saved to https://phabricator.wikimedia.org/P22573 and previous config saved to /var/cache/conftool/dbconfig/20220315-155102-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T16:36:19Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1147 (T298743)', diff saved to https://phabricator.wikimedia.org/P22583 and previous config saved to /var/cache/conftool/dbconfig/20220315-163618-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T16:36:27Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1148 (T298743)', diff saved to https://phabricator.wikimedia.org/P22584 and previous config saved to /var/cache/conftool/dbconfig/20220315-163626-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T16:40:53Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1148 (T298743)', diff saved to https://phabricator.wikimedia.org/P22585 and previous config saved to /var/cache/conftool/dbconfig/20220315-164053-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T17:26:09Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1148 (T298743)', diff saved to https://phabricator.wikimedia.org/P22594 and previous config saved to /var/cache/conftool/dbconfig/20220315-172608-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T17:26:17Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1149 (T298743)', diff saved to https://phabricator.wikimedia.org/P22595 and previous config saved to /var/cache/conftool/dbconfig/20220315-172616-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T18:08:50Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1149 (T298743)', diff saved to https://phabricator.wikimedia.org/P22602 and previous config saved to /var/cache/conftool/dbconfig/20220315-180850-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T18:54:06Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1149 (T298743)', diff saved to https://phabricator.wikimedia.org/P22607 and previous config saved to /var/cache/conftool/dbconfig/20220315-185405-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T18:54:14Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1160 (T298743)', diff saved to https://phabricator.wikimedia.org/P22608 and previous config saved to /var/cache/conftool/dbconfig/20220315-185413-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T19:11:42Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1160 (T298743)', diff saved to https://phabricator.wikimedia.org/P22610 and previous config saved to /var/cache/conftool/dbconfig/20220315-191140-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T19:56:57Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1160 (T298743)', diff saved to https://phabricator.wikimedia.org/P22617 and previous config saved to /var/cache/conftool/dbconfig/20220315-195657-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T19:59:35Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Depooling db1146:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22618 and previous config saved to /var/cache/conftool/dbconfig/20220315-195934-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T20:56:18Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1146:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22626 and previous config saved to /var/cache/conftool/dbconfig/20220315-205618-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-15T21:41:33Z] <ladsgroup@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1146:3314 (T298743)', diff saved to https://phabricator.wikimedia.org/P22633 and previous config saved to /var/cache/conftool/dbconfig/20220315-214133-ladsgroup.json

Mentioned in SAL (#wikimedia-operations) [2022-03-16T07:49:11Z] <Amir1> dbmaint on master of s4@eqiad (T298743)

Ladsgroup updated the task description. (Show Details)
Ladsgroup moved this task from In progress to Done on the DBA board.

Done on master of s4.

Query OK, 5196027 rows affected (2 min 38.711 sec)

since this table doesn't get that many writes, it should be fine.