Page MenuHomePhabricator
Create Task
Maniphest T104459

Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
Closed, ResolvedPublic
Actions

Description

While replica drift is not a concern, there is no account of schema changes on different hosts due to performance reasons, such as different partitioning system, or usage of the TokuDB engine instead of InnoDB.

Create a script or application to check for unaccounted object differences (tables, triggers, dbs), schema changes in tables and data differences.

While there are open source solutions for that, we need to accommodate those tools to our particular setup:

  • Sharding
  • Heavy use of filters
  • sanitized host with some of the data missing
  • Not yet applied or undocumented schema changes (good changes applied to production only but not to mediawiki; changes applied to mediawiki but never scheduled for production; etc.)

Details

SubjectRepoBranchLines +/-
script to generate lists of db hosts by shard and/or dcoperations/softwaremaster+327 -0
dbtools: check_tables.shoperations/softwaremaster+44 -0
tables_to_check: Add more tables,rename the fileoperations/softwaremaster+3 -0
mariadb: Add main_tables.txt, collection of main mediawiki tablesoperations/softwaremaster+7 -0
[WIP] Unfinished db table check toolsoperations/softwaremaster+146 -0
Add prometheus's mysql-exporter to all jessie's production codfw dbsoperations/puppetproduction+120 -21
Customize query in gerrit

Related Objects
Search...

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
· · ·
ResolvedjcrespoT104310 Create a whitelist of tables to checksum on all wikis
ResolvedMarosteguiT187295 Apply AbuseFilter patch-fix-index
ResolvedMarosteguiT71127 Discrepancies with logging table on different wikis
ResolvedSmalyshevT202764 Wikidata produces a lot of failed requests for recentchanges API
ResolvedMarosteguiT206103 recentchanges table indexes: tmp1, tmp2 and tmp3
ResolvedMarosteguiT51593 Rename two indexes in the Echo extension
ResolvedMarosteguiT153638 Drop echo tables from local wiki databases
ResolvedMarosteguiT205913 Drop ct_ indexes on change_tag
ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
ResolvedNoneT57455 Provide dynamic report of differences between replica databases and production databases
ResolvedMarosteguiT51199 Add index log_type_action
ResolvedMarosteguiT250055 Remove image.img_deleted column from production
ResolvedMarosteguiT250067 user_newtalk has two indexes not renamed in s4
ResolvedMarosteguiT250062 ipb_parent_block_id_2 index on ipblocks table on s8 only
ResolvedMarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
ResolvedMarosteguiT249973 db1110 has 5 important database drifts that are unique to the host
ResolvedMarosteguiT250056 lc_lang_key index is lingering in production
ResolvedMarosteguiT250057 type_acton index in logging table is lingering in production
DeclinedNoneT250058 searchindex indexes are missing in production
ResolvedMarosteguiT250063 inverse_timestamp column exists in text table, it shouldn't
ResolvedMarosteguiT250066 text table still has old_* fields and indexes on some hosts
ResolvedLadsgroupT250071 Rename ipb_address index on ipb_address to ipb_address_unique
ResolvedMarosteguiT252126 tl_from index on templatelinks is lingering in production
ResolvedMarosteguiT256679 text table in db1144 drifts from core considerably
ResolvedMarosteguiT256680 imagelinks has index mismatch on s8
ResolvedMarosteguiT256682 page_restrictions indexes have been majestically drifting from code
ResolvedMarosteguiT256684 pl_from index still lingers in random hosts
ResolvedMarosteguiT256685 pl_namespace index on pagelinks is unique only in s8
ResolvedMarosteguiT260111 All sorts of random drifts in wikis in s3
ResolvedLadsgroupT265321 ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
ResolvedMarosteguiT265349 querycache qc_type and qc_title have different nullabality on s1 only
ResolvedMarosteguiT265445 Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
ResolvedLadsgroupT269348 wikitech database has almost all of its varbinary fields wrong
ResolvedKormatT277118 iw_url in interwiki is varbinary(127) in production but blob in code
ResolvedMarosteguiT277354 "chemical" major mime type was never added to production database
ResolvedKormatT278619 ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
ResolvedMarosteguiT278621 job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
ResolvedKormatT277116 fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
ResolvedMarosteguiT308206 Slow query on echo_event table
· · ·

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Marostegui added a comment.Apr 13 2020, 6:36 AM

Thanks for this Amir - this is super useful feel free to move it to operations/software, better to have it there just in case.
As I mentioned on my email to Operations, let's try to get in a better shape first and fix the already created tickets, before automating it and sending reports that won't have much change over the weeks (as it will take time to fix all the drifts) and we know what happens when there's a weekly (or monthly) email that has no change...people tend to ignore it :-(

I would suggest we start working on fixing the drifts first and once we are in a much cleaner state, we can talk about scheduling and sending a report every once in XX days.

Again, thank you so much!

Marostegui mentioned this in T250055: Remove image.img_deleted column from production.Apr 13 2020, 8:58 AM
Marostegui added a subtask: T250067: user_newtalk has two indexes not renamed in s4.Apr 13 2020, 11:36 AM
Marostegui added a subtask: T250062: ipb_parent_block_id_2 index on ipblocks table on s8 only.
Marostegui added a subtask: T250060: tl_namespace index on templatelinks is unique only in s8.Apr 13 2020, 11:49 AM
Marostegui closed subtask T250062: ipb_parent_block_id_2 index on ipblocks table on s8 only as Resolved.Apr 13 2020, 12:04 PM
Ladsgroup added a comment.Apr 13 2020, 12:39 PM

For the sake of documentation and entertainment, I list all of tickets created because of this report:

Marostegui closed subtask T250067: user_newtalk has two indexes not renamed in s4 as Resolved.Apr 13 2020, 4:05 PM
Marostegui closed subtask T250060: tl_namespace index on templatelinks is unique only in s8 as Resolved.Apr 20 2020, 7:15 AM
Marostegui reopened subtask T250060: tl_namespace index on templatelinks is unique only in s8 as Open.
Marostegui closed subtask T250060: tl_namespace index on templatelinks is unique only in s8 as Resolved.Apr 20 2020, 7:37 AM
Krinkle edited projects, added Sustainability (Incident Followup); removed Wikimedia-Incident.Apr 28 2020, 9:50 PM
Ladsgroup mentioned this in T251613: Change the abusefilter database index definition.May 1 2020, 4:07 PM
Marostegui added subtasks: T249972: Wikitech has lots of database drifts with core and rest of the databases, T249973: db1110 has 5 important database drifts that are unique to the host, T250056: lc_lang_key index is lingering in production, T250057: type_acton index in logging table is lingering in production, T250058: searchindex indexes are missing in production, T250063: inverse_timestamp column exists in text table, it shouldn't, T250066: text table still has old_* fields and indexes on some hosts, T250071: Rename ipb_address index on ipb_address to ipb_address_unique.May 4 2020, 7:07 AM
Marostegui changed the status of subtask T250055: Remove image.img_deleted column from production from Open to Stalled.May 6 2020, 10:07 AM
Marostegui changed the status of subtask T250063: inverse_timestamp column exists in text table, it shouldn't from Open to Stalled.May 7 2020, 6:02 AM
Marostegui closed subtask T252126: tl_from index on templatelinks is lingering in production as Resolved.May 8 2020, 5:18 AM
Marostegui changed the status of subtask T250055: Remove image.img_deleted column from production from Stalled to Open.May 29 2020, 5:19 AM
Marostegui closed subtask T250055: Remove image.img_deleted column from production as Resolved.May 29 2020, 10:01 AM
Marostegui changed the status of subtask T250063: inverse_timestamp column exists in text table, it shouldn't from Stalled to Open.Jun 12 2020, 4:53 AM
Marostegui closed subtask T250066: text table still has old_* fields and indexes on some hosts as Resolved.Jun 23 2020, 11:48 AM
Marostegui closed subtask T250063: inverse_timestamp column exists in text table, it shouldn't as Resolved.
Ladsgroup reopened subtask T252126: tl_from index on templatelinks is lingering in production as Open.Jun 29 2020, 7:26 PM
Marostegui closed subtask T252126: tl_from index on templatelinks is lingering in production as Resolved.Jun 29 2020, 7:39 PM
Marostegui closed subtask T256679: text table in db1144 drifts from core considerably as Resolved.Jun 29 2020, 8:00 PM
Marostegui closed subtask T256684: pl_from index still lingers in random hosts as Resolved.Jun 30 2020, 4:59 AM
Marostegui closed subtask T256680: imagelinks has index mismatch on s8 as Resolved.Jul 6 2020, 11:36 AM
Marostegui changed the status of subtask T256685: pl_namespace index on pagelinks is unique only in s8 from Open to Stalled.Jul 22 2020, 8:15 AM
Marostegui closed subtask T256682: page_restrictions indexes have been majestically drifting from code as Resolved.Jul 30 2020, 5:22 AM
Marostegui reopened subtask T250071: Rename ipb_address index on ipb_address to ipb_address_unique as Open.Sep 4 2020, 5:17 AM
Marostegui closed subtask T250071: Rename ipb_address index on ipb_address to ipb_address_unique as Resolved.Sep 4 2020, 5:56 AM
Marostegui changed the status of subtask T256685: pl_namespace index on pagelinks is unique only in s8 from Stalled to Open.Sep 7 2020, 4:43 AM
Marostegui closed subtask T256685: pl_namespace index on pagelinks is unique only in s8 as Resolved.Sep 7 2020, 8:02 AM
LSobanski subscribed.Sep 25 2020, 3:02 PM
Ladsgroup mentioned this in T205094: Investigate and restructure SQL directory, and use new MediaWiki abstract schema change features.Oct 7 2020, 4:40 PM
Marostegui closed subtask T260111: All sorts of random drifts in wikis in s3 as Resolved.Oct 8 2020, 6:57 AM
Marostegui changed the status of subtask T265349: querycache qc_type and qc_title have different nullabality on s1 only from Open to Stalled.Oct 13 2020, 1:00 PM
Marostegui closed subtask T265445: Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5 as Resolved.Oct 14 2020, 7:30 AM
jcrespo mentioned this in T265866: Run check table periodically on backup source hosts.Oct 22 2020, 10:33 AM
Marostegui changed the status of subtask T265349: querycache qc_type and qc_title have different nullabality on s1 only from Stalled to Open.Oct 28 2020, 11:21 AM
Marostegui closed subtask T265349: querycache qc_type and qc_title have different nullabality on s1 only as Resolved.Nov 3 2020, 6:47 AM
Ladsgroup closed subtask T265321: ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production as Resolved.Nov 16 2020, 5:45 PM
jcrespo mentioned this in T274636: Develop a web dashboard or a command line tool to help inventory and/or monitor database and backup objects.Feb 12 2021, 12:14 PM
Umherirrender unsubscribed.Feb 27 2021, 9:43 PM
Rohitesh-Kumar-Jain subscribed.Feb 28 2021, 4:24 PM
Ladsgroup added a comment.May 10 2021, 5:57 AM

I just made a dashboard to keep track of drifts reported by my tool: https://drift-tracker.toolforge.org/

LSobanski mentioned this in T282857: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and replicas.May 14 2021, 10:55 AM
LSobanski renamed this task from Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and slaves to Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.May 14 2021, 10:59 AM
LSobanski updated the task description. (Show Details)
LSobanski added a comment.May 14 2021, 11:01 AM

I updated the task to limit it to drift detection and spun automation into a separate task: T282857. These are effectively two separate tasks of different complexity and priority.

LSobanski moved this task from Meta/Epic to In progress on the DBA board.May 14 2021, 11:03 AM
Ladsgroup closed this task as Resolved.May 15 2021, 11:43 AM
Ladsgroup claimed this task.

I just rewrote the dashboard in python to make it more maintainable and created Tool-drift-tracker (and created some tickets to tackle later, specially some tickets to automate the work much more.)

With https://drift-tracker.toolforge.org/report/core/

We can basically call this done ^^. Of course there are much more to be done but I consider this a good start.

Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptMay 15 2021, 11:43 AM
Maintenance_bot moved this task from Incoming to Done on the User-Ladsgroup board.May 15 2021, 12:15 PM
Marostegui added a comment.May 17 2021, 4:43 AM

Very nice @Ladsgroup!!!
I have documented this on: https://wikitech.wikimedia.org/wiki/MariaDB#Tables_drift_between_code_and_production so it doesn't get lost on phabricator tickets

LSobanski added a comment.May 17 2021, 11:03 AM

A feature request for data differences (narrowly scoped) is tracked in https://phabricator.wikimedia.org/T207253.

Ladsgroup closed subtask T269348: wikitech database has almost all of its varbinary fields wrong as Resolved.Jun 2 2021, 10:28 PM
Marostegui closed subtask T278621: job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007 as Resolved.Jul 7 2021, 6:24 AM
Kormat changed the status of subtask T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production from Open to Stalled.Jul 13 2021, 11:36 AM
Kormat changed the status of subtask T277118: iw_url in interwiki is varbinary(127) in production but blob in code from Open to Stalled.Jul 14 2021, 1:10 PM
Kormat changed the status of subtask T278619: ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007 from Open to Stalled.Jul 15 2021, 11:56 AM
Marostegui changed the status of subtask T277118: iw_url in interwiki is varbinary(127) in production but blob in code from Stalled to Open.Oct 19 2021, 5:36 AM
Marostegui changed the status of subtask T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production from Stalled to Open.
Kormat closed subtask T277118: iw_url in interwiki is varbinary(127) in production but blob in code as Resolved.Oct 20 2021, 8:06 AM
Kormat closed subtask T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production as Resolved.Oct 20 2021, 3:10 PM
Kormat changed the status of subtask T278619: ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007 from Stalled to In Progress.Oct 21 2021, 11:46 AM
Kormat closed subtask T278619: ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007 as Resolved.Oct 22 2021, 8:35 AM
Marostegui closed subtask T57455: Provide dynamic report of differences between replica databases and production databases as Resolved.Nov 5 2021, 7:58 AM
Marostegui closed subtask T277354: "chemical" major mime type was never added to production database as Resolved.Dec 14 2021, 1:58 PM
Marostegui reopened subtask T277354: "chemical" major mime type was never added to production database as Open.Dec 21 2021, 9:58 AM
Marostegui closed subtask T277354: "chemical" major mime type was never added to production database as Resolved.Jan 5 2022, 7:14 AM
Umherirrender added subtasks: T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis, T298316: Fix nullability of column recentchanges.rc_params on wmf wikis, T298554: Fix mismatching field type of archive.ar_timestamp on wmf wikis, T298555: Fix mismatching field type of logging.log_timestamp on wmf wikis, T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis, T298557: Fix mismatching field type of page.page_touched on wmf wikis, T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis, T298559: Fix mismatching field type of querycache_info.qci_timestamp on wmf wikis, T298560: Fix mismatching field type of revision.rev_timestamp on wmf wikis, T298563: Fix mismatching field type of column text.old_flags on wmf wikis, 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 5 2022, 6:16 PM
Marostegui closed subtask T298559: Fix mismatching field type of querycache_info.qci_timestamp on wmf wikis as Resolved.Feb 1 2022, 5:51 AM
Umherirrender added a subtask: T300662: Make wikilove_log.wll_sender/wll_receiver unsigned on wmf wikis.Feb 1 2022, 6:49 PM
Ladsgroup added a subscriber: Umherirrender.Feb 2 2022, 8:13 AM

@Umherirrender you don't need to add subtasks if you ask me. This task is in itself done.

Marostegui closed subtask T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis as Resolved.Feb 4 2022, 6:22 AM
Ladsgroup changed the status of subtask T298554: Fix mismatching field type of archive.ar_timestamp on wmf wikis from Open to Stalled.Feb 14 2022, 11:04 PM
Marostegui closed subtask T300662: Make wikilove_log.wll_sender/wll_receiver unsigned on wmf wikis as Resolved.Feb 15 2022, 5:33 AM
Ladsgroup mentioned this in T300774: Drop fr_img_* columns.Feb 24 2022, 9:16 AM
jcrespo removed a parent task: T205628: Handle object metadata backups and compare it with stored database object inventory.Mar 7 2022, 1:54 PM
Marostegui closed subtask T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis as Resolved.Mar 10 2022, 5:32 AM
Maintenance_bot moved this task from In progress to Done on the DBA board.Mar 10 2022, 6:15 AM
Marostegui closed subtask T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis as Resolved.Apr 12 2022, 9:09 AM
Marostegui closed subtask T298563: Fix mismatching field type of column text.old_flags on wmf wikis as Resolved.Apr 19 2022, 7:49 AM
Ladsgroup reopened subtask T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis as Open.Apr 26 2022, 3:32 PM
Ladsgroup closed subtask T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis as Resolved.Apr 27 2022, 2:09 PM
Umherirrender reopened subtask T298563: Fix mismatching field type of column text.old_flags on wmf wikis as Open.Apr 27 2022, 4:27 PM
Umherirrender reopened subtask T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis as Open.
Umherirrender reopened subtask T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis as Open.
Ladsgroup closed subtask T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis as Resolved.Apr 28 2022, 9:09 PM
Ladsgroup closed subtask T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis as Resolved.May 3 2022, 9:00 PM
Umherirrender removed subtasks: T300662: Make wikilove_log.wll_sender/wll_receiver unsigned on wmf wikis, 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, T298563: Fix mismatching field type of column text.old_flags on wmf wikis, T298560: Fix mismatching field type of revision.rev_timestamp on wmf wikis, T298559: Fix mismatching field type of querycache_info.qci_timestamp on wmf wikis, T298558: Fix mismatching field type of protected_titles.pt_timestamp on wmf wikis, T298557: Fix mismatching field type of page.page_touched on wmf wikis, T298556: Fix mismatching field type of oldimage.oi_timestamp on wmf wikis, T298555: Fix mismatching field type of logging.log_timestamp on wmf wikis, T298554: Fix mismatching field type of archive.ar_timestamp on wmf wikis, T298316: Fix nullability of column recentchanges.rc_params on wmf wikis, T298295: Fix length of columns page_restrictions.pr_level/pr_type on wmf wikis.May 3 2022, 9:04 PM
Maintenance_bot removed a project: Patch-For-Review.May 3 2022, 9:31 PM
Marostegui added a subtask: T308206: Slow query on echo_event table.May 12 2022, 7:41 AM
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