- Affected components: Mediawiki core
- Engineer(s) or team for initial implementation: @Ladsgroup (In volunteer capacity)
- Code steward: Platform team
Motivation
Currently, MysqlUpdater class on master has (at least on paper) support for upgrading from 1.2 (Released on 2004-05-24, which predates birth of some of our volunteer devs) to 1.35 (hasn't released yet) and it seems there is no plan to stop it and it will continue forever.
As the result:
- Number of database checks in MySQL is reaching 500 and size of the class to 1,500 lines of code.
- The archive of sql patches (directory of maintenance/archives/) is really large and unorganized
- In every update.php run, the system has to run all of these checks (updatelog stores that some of them are done but it's not sql checks and it's only maintenance script runs as far as I see in my localhost)
- Due to lots of reasons, update.php can never be ran in production but it manages to sneak in and get ran and cause outages: T157651: sql.php must not run LoadExtensionSchemaUpdates. This means reducing the probability of causing issues in case this kind of problem happen again would be nice.
- Keeping ability to upgrade from any point in time makes schema change logic quite complex:
- You can't reintroduce an index (with different columns for example) and it has to have a different name, otherwise the update.php in every run (on master) removes the index and re-add it again. Same goes with changing field data type twice, if you change it from blob to varchar and a couple years later to varbinary, the system on master sees that the field type is not varchar, thinks it's blob, change it to varchar, and then the next check sees that it's not varbinary, and turns it to varbinary and it happens in every update.php run. You might argue that we can remove the first schema change but what if one of the updates in between depends on this certain data type?
- If you remove a table, you need to remove all updates related to it, otherwise the update logic will break (like T230317: Error: 1146 Table 'valid_tag' doesn't exist when upgrading from an ancient MediaWiki version)
- This complex logic was one of reasons behind one of our biggest outages when a really important table got dropped because Wikibase assumed due to lack of wb_terms table which was meant to be dropped from the code soon, the system is upgrading from a version that's 8 years old (predating wb_terms) so dropped several tables to rebuild them. i.e. Wikibase mistook the future with the far past: T249565: Wikidata's wb_items_per_site table has suddenly disappeared, creating DBQueryErrors on page views
- This logic never worked properly from old versions anyway and it's famous in third party users that for big jumps and large databases, it's unreliable. They usually upgrade from one LTS to another multiple times instead and given that we have VCS, it makes sense.
- Due this complex logic, properly writing test for it is hard, there are some snapshots (in sqlite) that build the system, run upgrade on it and check if it matches with the current system but it's pretty limited and doesn't cover upgrade from all releases.
- The complex logic is not documented and stored as institutional knowledge with low bus factor which lots of devs have to explain and repeat for every new person doing a schema change for the first time (here's an example)
- Technically upgrading from 1.2 is impossible because the MySQL version that mediawiki 1.2 needs is so different from 1.35 that MySQL upgrade (with lots of data) would be non-trivial
Requirements
- No current functionality for upgrading from an LTS to another should break
- The developer productivity and onboarding cost of doing schema changes in core and extensions should improve.
- The *Updater classes should stop growing non-stop
Exploration
Proposal:
On master, only support upgrading from the last LTS release that's not EOL'd yet (basically meaning two LTS releases). Remove all of old updates and their .sql files and make it clear in RELEASE-NOTES (add a dedicated section that upgrades from which releases are supported).
The only downsides with proposal:
- It would be harder for people to upgrade from really really old versions and need to do it in jumps but update.php is unreliable in that regard anyway.
- The current archives of .sql files is actually a good library to find the most similar alter table to copy-paste, specially for DBMS engines that are different from the ones the dev is familiar with (like Postgres or Oracle to me). But this will be fully addressed with abstract schema changes (T191231: RFC: Abstract schemas and schema changes) which will come in the next couple of months.