Out of interest I looked to see what schema drifts are present for the GlobalBlocking extension.
From the globalblocks table on db2150 I saw:
From the global_block_whitelist table on enwiki db2174:
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T313251 Collect and fix schema drifts of wmf-deployed extension tables in production | |||
| Open | None | T381502 Fix GlobalBlocking database schema drifts in production | |||
| Resolved | Dreamy_Jazz | T381517 Older wikis with GlobalBlocking installed have an incorrect type for gbw_expiry | |||
| Resolved | Ladsgroup | T381521 Update type of gbw_expiry on the global_block_whitelist table | |||
| Open | None | T381877 Older wikis with GlobalBlocking installed have an incorrect type for gb_id |
Thanks for the task @Dreamy_Jazz, it would be amazing if you could also be so kind to provide the alter tables! If not, that is of course fine.
I created https://github.com/Ladsgroup/db-analyzor-tools/pull/4 to add the GlobalBlocking tables to the drift tracker, so that it can be run for the extension for a more comprehensive check.
Sure. Would it make sense for the globalblocks table to be updated to use a BIGINT for the primary key while the drift is fixed?
If so, I can update the table in the extension and generate the ALTER TABLE through the schema change maintenance scripts.
I'd say let's run the script and use the results to have a more comprehensive list of issues we are dealing with.
So far I'm seeing a lot of:
"global_block_whitelist gbw_expiry field-type-mismatch": {
"s3": [
"db2209:advisorswiki",
"db2177:advisorswiki",
"db2205:advisorswiki",
"db2190:advisorswiki",
"db2149:advisorswiki",
"db2194:advisorswiki",
"db2227:advisorswiki",
"db2156:advisorswiki",and in many other wikis too. I'd say, let's create a subtask for this, get it cleaned up and then run it again. Would you mind doing that @Dreamy_Jazz ?
I'd say, let's create a subtask for this, get it cleaned up and then run it again.
Looking into this further, it appears that third-party wikis will likely also see this issue. This is because the drift was introduced in fcdd3a5800bab0f5d1999022e69771bdfce9734c when the extension was converted to use an abstract schema. The type was changed in the source file from binary to varbinary.
Given that this could also affect third-party wikis, I think it's best if the schema change is made in the extension and then applied to production using a Schema-change-in-production ticket.
I've just run the drift tracker over the global_block_whitelist table again, and it appears that the drifts have been fixed for it (except for the default value mismatches).
I'm not sure I've ran the drift checker for the globalblocks table correctly. I used python3 db_drift_checker.py --prod --wiki centralauth --dc codfw globalblocking_central "sql {wiki} -- ". It produced the mismatch for the gb_id column but seemed to miss the gb_address mismatch.
I don't think I've made the script work with central tables since it's usually easy to check them by eye (at least that's easy for one replica). It's probably not too hard to make it work with central tables too. Maybe try this?
python3 db_drift_checker.py --prod --wiki arwiki --dc codfw globalblocking_central "sql centralauth -- "
It gives empty result to me.
Running the command I detailed above gave:
{ "_metadata": { "time_end": 1733327830.7797713, "time_start": 1733327460.0453901 }, "globalblocks gb_id field-unsigned-mismatch": { "s7": [ "db2218:arwiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:cawiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:eswiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:fawiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:frwiktionary", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:hewiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:huwiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:kowiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:metawiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:rowiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:ukwiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth", "db2218:viwiki", "db2168:centralauth", "db2208:centralauth", "db2182:centralauth", "db2220:centralauth", "db2222:centralauth", "db2150:centralauth", "db2159:centralauth", "db2221:centralauth" ] } }
Trying your command now.
It appears that fcdd3a5800bab0f5d1999022e69771bdfce9734c also cause the unsigned vs signed discrepancy, so we should probably create a schema patch for it in the extension too.