All hosts are on eqiad. Did I missed a task where that is done after a switch over?

For example T282372 for page_touched is closed and the dbs are listed on the task

I run drift tracker per dc. I ran it several times on codfw (usually before switchovers) but it's a different issue altogether.
The thing is that this current list is too big to be tackled in one task (it used to be around 15 times bigger, I have been cleaning these up since 2017). Each set of drifts need a separate investigation. Sometimes, you need to change production, sometimes you need to change the code. Sometimes it's partial schema changes, sometimes people actually forgot to change production at all and new needs to be checked slowly to make sure nothing breaks. It's quite a mess. I suggest grouping those on drift type and create a ticket for each one to do the investigation. e.g. one for "Mismatching field size of page_restrictions.pr_type"

In T298212#7586497, @Ladsgroup wrote:

e.g. one for "Mismatching field size of page_restrictions.pr_type"

For that the last change to the fields is f38344aba87edbdb5dba23d6433bf3c7cbbb0b51, but I cannot see the old length.

Mismatching field nullability of recentchanges.rc_params

Possible 79eb0fe88d35aac3dce2b6fbefc652b123414066, but that's also removes DEFAULT, which is not reported here.

Mismatching field type of page.page_restrictions

Thats a tinyblob since begin 64ac6b8e775dd8582ea9cc5f24235488f076c5b3, not sure which types differ here

Most of the other mismatch are related to timestamps, which gets some fixed on the abstract schema changes.
It does not looks like current schema changes missing here. Only 5 wikis, not sure about the age of them.
I do not thing there are code changes needed here. If there are code changes it means all the other wikis needs updating. Only performance could be a reason for that.

In T298212#7586515, @Umherirrender wrote:

It does not looks like current schema changes missing here. Only 5 wikis, not sure about the age of them.

The current drift tracker picks one wiki randomly from each section. That makes it less great but otherwise, any drift on s3 would overwhelm the report (and it would be really slow). I do run it on all of s3 from time to time separately: T260111: All sorts of random drifts in wikis in s3

I do not thing there are code changes needed here. If there are code changes it means all the other wikis needs updating. Only performance could be a reason for that.

It really depends on the case. We should do git log -S on each one, create a ticket separately, run a full check on the system for each drift. It's a long and tedious work but we are getting there. It is important to take our time because in 2017, an index named tmp1 was removed as a drift (and it was marked as unused index in database reports) caused a full outage of all wikis.

• Need help for the drifts related to user table. It is possible to get the definition of a user table from s3 (thwikiquote)? The reported columns are all binary since the begin (only char() binary rewrite to binary() in f38344aba87edbdb5dba23d6433bf3c7cbbb0b51 is shown in the history). Not sure about the current type of the fields and what the difference is.

• Also the current type of text.old_flags on s3 (thwikiquote) would be nice to know (tinyblob since addition 64ac6b8e775dd8582ea9cc5f24235488f076c5b3)

• Should "Mismatching field type of page.page_restrictions" fixed or waiting for drop? T218446 (Just to keep my result from research: The type was not changed - added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3, not sure why it is a varbinary(255) on some wikis and tinyblob on others)

• All the timestamp fields differ between binary(14) and varbinary(14), but there is no code change in the history (related T42626). There are also tasks in the past which changed this (T277116, T266486, etc.). Should I create one task per table?
  • Mismatching field type of archive.ar_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of logging.log_timestamp (Added in 41e752879cd906767c8473bb124345e8beb190e6#change-pVGjpnYAcwz0)
  • Mismatching field type of oldimage.oi_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of page.page_touched (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)
  • Mismatching field type of protected_titles.pt_timestamp (Added in 34d22f6edc19de43263a3f4ce9502cab7d270585#change-pVGjpnYAcwz0)
  • Mismatching field type of querycache_info.qci_timestamp (Added in 2cda98321d7f03d4d17a545aef9e2e6599ada42f#change-pVGjpnYAcwz0)
  • Mismatching field type of revision.rev_timestamp (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)

• While looking through old commits it seems necessary that the drift tracker also looks for DEFAULTs, because some columns gets defaults removed and not sure if that all was done in production as well. Sounds that necessary?

In T298212#7589838, @Umherirrender wrote:

• Need help for the drifts related to user table. It is possible to get the definition of a user table from s3 (thwikiquote)? The reported columns are all binary since the begin (only char() binary rewrite to binary() in f38344aba87edbdb5dba23d6433bf3c7cbbb0b51 is shown in the history). Not sure about the current type of the fields and what the difference is.

root@db1112.eqiad.wmnet[thwikiquote]> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varbinary(255) NOT NULL DEFAULT '',
  `user_real_name` varbinary(255) NOT NULL DEFAULT '',
  `user_password` varbinary(255) DEFAULT NULL,
  `user_newpassword` varbinary(255) DEFAULT NULL,
  `user_email` varbinary(255) DEFAULT NULL,
  `user_touched` varbinary(14) NOT NULL,
  `user_token` varbinary(32) NOT NULL DEFAULT '',
  `user_email_authenticated` varbinary(14) DEFAULT NULL,
  `user_email_token` varbinary(32) DEFAULT NULL,
  `user_email_token_expires` varbinary(14) DEFAULT NULL,
  `user_registration` varbinary(14) DEFAULT NULL,
  `user_newpass_time` varbinary(14) DEFAULT NULL,
  `user_editcount` int(11) DEFAULT NULL,
  `user_password_expires` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_email_token` (`user_email_token`),
  KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB AUTO_INCREMENT=5135 DEFAULT CHARSET=binary
1 row in set (0.001 sec)

• Also the current type of text.old_flags on s3 (thwikiquote) would be nice to know (tinyblob since addition 64ac6b8e775dd8582ea9cc5f24235488f076c5b3)

root@db1112.eqiad.wmnet[thwikiquote]> show create table text\G
*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` varbinary(255) NOT NULL,
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42791 DEFAULT CHARSET=binary PACK_KEYS=1
1 row in set (0.001 sec)

• Should "Mismatching field type of page.page_restrictions" fixed or waiting for drop? T218446 (Just to keep my result from research: The type was not changed - added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3, not sure why it is a varbinary(255) on some wikis and tinyblob on others)

If it is going to be eventually dropped, I would rather ignore this. Doing a schema change that changes datatype is costly and requires master switchovers.

• All the timestamp fields differ between binary(14) and varbinary(14), but there is no code change in the history (related T42626). There are also tasks in the past which changed this (T277116, T266486, etc.). Should I create one task per table?
  • Mismatching field type of archive.ar_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of logging.log_timestamp (Added in 41e752879cd906767c8473bb124345e8beb190e6#change-pVGjpnYAcwz0)
  • Mismatching field type of oldimage.oi_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of page.page_touched (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)
  • Mismatching field type of protected_titles.pt_timestamp (Added in 34d22f6edc19de43263a3f4ce9502cab7d270585#change-pVGjpnYAcwz0)
  • Mismatching field type of querycache_info.qci_timestamp (Added in 2cda98321d7f03d4d17a545aef9e2e6599ada42f#change-pVGjpnYAcwz0)
  • Mismatching field type of revision.rev_timestamp (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)

Sure!

• While looking through old commits it seems necessary that the drift tracker also looks for DEFAULTs, because some columns gets defaults removed and not sure if that all was done in production as well. Sounds that necessary?

@Ladsgroup ^ thoughts?

In T298212#7595840, @Marostegui wrote:

• While looking through old commits it seems necessary that the drift tracker also looks for DEFAULTs, because some columns gets defaults removed and not sure if that all was done in production as well. Sounds that necessary?

@Ladsgroup ^ thoughts?

I think fixing defaults and unifying them is important but not in any way high priority. It should be tackled eventually but that can be during the switchover time.

In T298212#7595840, @Marostegui wrote:

• Should "Mismatching field type of page.page_restrictions" fixed or waiting for drop? T218446 (Just to keep my result from research: The type was not changed - added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3, not sure why it is a varbinary(255) on some wikis and tinyblob on others)

If it is going to be eventually dropped, I would rather ignore this. Doing a schema change that changes datatype is costly and requires master switchovers.

If T298557: Fix mismatching field type of page.page_touched on wmf wikis needs a master switchover maybe make it as well, at least some of the section of that task also affected with that schema drift. But I am linking the schema drift to the drop task instead and treat it as declined from this task view.

• All the timestamp fields differ between binary(14) and varbinary(14), but there is no code change in the history (related T42626). There are also tasks in the past which changed this (T277116, T266486, etc.). Should I create one task per table?
  • Mismatching field type of archive.ar_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of logging.log_timestamp (Added in 41e752879cd906767c8473bb124345e8beb190e6#change-pVGjpnYAcwz0)
  • Mismatching field type of oldimage.oi_timestamp (Added in d82c14fb4fbac288b42ca5918b0a72f33ecb1e69#change-pVGjpnYAcwz0)
  • Mismatching field type of page.page_touched (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)
  • Mismatching field type of protected_titles.pt_timestamp (Added in 34d22f6edc19de43263a3f4ce9502cab7d270585#change-pVGjpnYAcwz0)
  • Mismatching field type of querycache_info.qci_timestamp (Added in 2cda98321d7f03d4d17a545aef9e2e6599ada42f#change-pVGjpnYAcwz0)
  • Mismatching field type of revision.rev_timestamp (Added in 64ac6b8e775dd8582ea9cc5f24235488f076c5b3#change-pVGjpnYAcwz0)

Sure!

Created (linked as subtasks here)

• While looking through old commits it seems necessary that the drift tracker also looks for DEFAULTs, because some columns gets defaults removed and not sure if that all was done in production as well. Sounds that necessary?

@Ladsgroup ^ thoughts?

T298562: Change drift tracker to report DEFAULT differences as well

Thank you for creating all those tasks!