I suggest creating a subticket for each one, see the FR ticket T313253

• cu_changes.cuc_private was added in ede4937ffbd887530c01785a95be349236990ecb
• index cuc_actor_ip_time is also part of the schema change (ef2c3c3df5205a0b0b62a6dca1efc5d57b931825), but it seems missed on column addition in T303603, while it is showing up in the script
• cu_changes.cuc_timestamp should be clean now, the schema update was in progress - T310011
• cu_log.cul_timestamp was binary(14) not null from the begin (6cb7f881835019e5d5d2e3c16546e440e0ab61a0), not sure what the difference is
• index cuc_user_time is just an old name, was renamed in 09a2c75156faf03fd1d12f7ba34366b1c91613ee
• cu_changes.cuc_agent exists in the intial schema (13d27f3f3c06bbadcf8b367de0c6e5dd09904006) and it showing up in the table description under T321041#8324226
• cu_log.cul_range_end exists in the inital schema (6cb7f881835019e5d5d2e3c16546e440e0ab61a0) and is used unconditional in the code
• index cul_actor_time is part of the schema change for the column (48ea3bfb4771fd0e353abfc22b7264900dcf5791), but I cannot find the task where the column is added to prod

The type of cul_timestamp would be nice to known from enwiki
cuc_agent and cul_range_end are the last columns on the production tables and both are part of the initial schema, where I would not expect them as missing. It is possible the schema drift checker makes something wrong here?

In T321063#8326828, @Umherirrender wrote:

cuc_agent and cul_range_end are the last columns on the production tables and both are part of the initial schema, where I would not expect them as missing. It is possible the schema drift checker makes something wrong here?

It is quite possible that for connection issues, the replica has responded with empty response and the script interpreted that as "nothing here" and marked everything as missing. I have seen it happened before. I need to fix it :D

Thanks for creating the subtasks.

@Ladsgroup would it be possible to run the drift checker again? From my understanding all drifts should now be fixed, but it would be good to check before this is closed.

Started it, it'll take a bit

Thanks!

I'm seeing this:

"cu_changes cuc_timestamp field-type-mismatch": {
    "s7": [
        "db1158:arwiki",
        "db1158:cawiki",
        "db1158:eswiki",
        "db1158:fawiki",
        "db1158:frwiktionary",
        "db1158:hewiki",
        "db1158:huwiki",
        "db1158:kowiki",
        "db1158:metawiki",
        "db1158:rowiki",
        "db1158:ukwiki",
        "db1158:viwiki"
    ]
},

and this on basically every wiki:

"cu_log cul_timestamp field-type-mismatch": {
    "s3": [
        "db1157:aawiki",
        "db1112:aawiki",
        "db1123:aawiki",
        "db1166:aawiki",
        "db1175:aawiki",
        "db1179:aawiki",
        "db1189:aawiki",
        "db1198:aawiki",

Hmm.

Could you get the type of the timestamp fields which it says are a mismatch?

db1158 is listed in the schema-change task at T310011#7991330

The type of cul_timestamp is also unclear to me, from the code it was the correct type from begin (see T321063#8326828 above)

db1158 was repooled exactly one hour later meaning the depool failed to drain. I don't know if it's after the reload config or before but it can happen from time to time (if the calling script doesn't have the reload call, etc.). Redoing it should be fine and easy though.

Regarding the cul_timestamp, that's what I'm getting at aawiki:

`cul_timestamp` varbinary(14) NOT NULL DEFAULT '',

the schema change missing on db1158 was done before the reload config being deployed. That's why. Anyway, I restarted it on that host only. That'll be done.

@Ladsgroup, can the schema drift code be run again? Would be useful to see if any other wikis are missing cuc_only_for_read_old.

Started it.

In eqiad it's this https://drift-tracker.toolforge.org/report/checkuser/

It doesn't have cuc_only_for_read_old in it though. Weird.

Might I suggest then adding cuc_only_for_read_old on testcommonswiki, moving back to write new on group0 and seeing if any other wikis present the error?

Unfortunately not, because if the drift is more complicated, and the most dangerously, the column existing on master but not on replicas, it can break replication leading to a full set of wikis going read-only for extended period of time. We have had incidents like that before.

Thanks for the explanation. I'm guessing this is not easy to fix, especially as the drift tracker isn't saying where the issues are.

Yeah, I think there might be a bug in the analyzor which is worth investigating and fixing regardless. The code is here: https://github.com/Ladsgroup/db-analyzor-tools/blob/master/db_drift_checker.py

I'll try to debug it ASAP. If you feel adventurous, you can take a look too.

I've had a look, but I think I'd have to debug it when it's running to find the issue.

I think I found the issue. Fixed it and running it again.

Updated the report. I confirm it's only testcommonswiki (and some other schema changes are only missing there too)

Great. Thanks.

I don't think there is anything left from DBAs at the moment. Please create a subticket for each drift and we will take care of it.

The new run:
(beside T356631: Column cu_log.cul_timestamp has an incorrect type and incorrectly has a default value set)

"cu_changes cuc_actiontext field-mismatch-prod-extra": {
    "s1": [
        "db1163:enwiki"
    ],
    "s4": [
        "db1160:commonswiki",
        "db1160:testcommonswiki"
    ],
    "s8": [
        "db1193:wikidatawiki"
    ]
},
"cu_changes cuc_agent_id field-mismatch-codebase-extra": {
    "s8": [
        "db1178:wikidatawiki"
    ]
},
"cu_changes cuc_only_for_read_old field-mismatch-prod-extra": {
    "s1": [
        "db1163:enwiki"
    ],
    "s4": [
        "db1160:commonswiki",
        "db1160:testcommonswiki"
    ],
    "s8": [
        "db1193:wikidatawiki"
    ]
},
"cu_changes cuc_private field-mismatch-prod-extra": {
    "s1": [
        "db1163:enwiki"
    ],
    "s4": [
        "db1160:commonswiki",
        "db1160:testcommonswiki"
    ],
    "s8": [
        "db1193:wikidatawiki"
    ]
},
"cu_log_event cule_agent_id field-mismatch-codebase-extra": {
    "s8": [
        "db1178:wikidatawiki"
    ]
},
"cu_private_event cupe_agent_id field-mismatch-codebase-extra": {
    "s8": [
        "db1178:wikidatawiki"
    ]
}

Checked eqiad only for now.

Fixed eqiad ones (except T370903 on masters). Now running the script on codfw.

Thanks for finishing up work on this!

@Ladsgroup, out of interest I was checking the wiki DBs to see the changes and I've found another mismatch on db2219 for testcommonswiki:

MariaDB [testcommonswiki]> show create table cu_changes\G
*************************** 1. row ***************************
       Table: cu_changes
Create Table: CREATE TABLE `cu_changes` (
  ...
  `cuc_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  ...
) ...
1 row in set (0.000 sec)

It seems that there is a default where there is not supposed to be one. I don't think it's particularly a big concern, but wanted to flag it.

let me check

I think we don't check for default value for now. That's why. We probably need to start a round of "less important" drifts in a year or so once we are done with more important ones in all extensions.