Page MenuHomePhabricator

Investigate and restructure SQL directory, and use new MediaWiki abstract schema change features
Open, Needs TriagePublic

Description

In the context of T202265: Add 'tmp1' index that is in WMF production to the Wikibase install SQL files the need for a better structure came up.
Decide upon a better structure for *.sql files, and or use new MediaWiki abstract schema change features where appropriate

Goal should be:

  • Better oversight
  • Bundling of relevant files
  • Error avoidance
  • Remove the old schema changes that we no longer need (things from before the last supported version of MW)(currently 1.31)

Links:

Acceptance criteria:

  • all SQL files are in a sensible location according to their database type
  • all SQL files are generated by [maintenance/generateSchemaSql.php] from JSON sources

Details

Due Date
Tue, Sep 29, 10:00 PM
ProjectBranchLines +/-Subject
mediawiki/extensions/Wikibasemaster+4 -4
mediawiki/extensions/Wikibasemaster+95 -29
mediawiki/coremaster+13 -3
mediawiki/extensions/Wikibasemaster+8 -8
mediawiki/extensions/Wikibasemaster+120 -43
mediawiki/extensions/Wikibasemaster+64 -10
mediawiki/extensions/Wikibasemaster+85 -16
mediawiki/extensions/Wikibasemaster+58 -10
mediawiki/extensions/Wikibasemaster+3 -3
mediawiki/extensions/WikimediaMaintenancemaster+1 -1
mediawiki/extensions/WikimediaMaintenancemaster+1 -1
mediawiki/extensions/Wikibasemaster+9 -9
mediawiki/extensions/Wikibasemaster+378 -97
mediawiki/extensions/Wikibasemaster+59 -16
mediawiki/extensions/Wikibasemaster+89 -17
mediawiki/extensions/Wikibasemaster+20 -65
mediawiki/extensions/Wikibasemaster+25 -13
mediawiki/extensions/Wikibasemaster+7 -16
mediawiki/extensions/Wikibasemaster+6 -6
mediawiki/extensions/Wikibasemaster+11 -2
mediawiki/coremaster+2 -2
Show related patches Customize query in gerrit

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Michael added a subscriber: Michael.EditedAug 4 2020, 1:28 PM
  • Consider naming schema
  • consider sorting files into subdirectories by years or tables or MediaWiki versions
    • MediaWiki core seems to partially sort their changes into subdirectories by database driver, see :https://gerrit.wikimedia.org/r/c/mediawiki/core/+/473601
      • I'm skeptical whether this way is the best approach for Wikibase or whether we should keep representing that as part of the naming scheme, similar to what we have now.
  • investigate which parts of DatabaseSchemaUpdater can maybe be deleted because they are very old
  • we probably can mark everything for deletion older than MediaWiki 1.31

We still need to confirm whether abstract schema _changes_ is actually already available. The docs indicate that it is not done yet, but they might just be outdated.

This seems to be blocked by T230420: Introduce JSON-based Doctrine-DBAL-based SchemaDiffBuilder

  • Consider naming schema
  • consider sorting files into subdirectories by years or tables or MediaWiki versions

One important lack of distinction in wikibase sql files are the fact that it doesn't distinguish between the sql file for a fresh install (the "current schema" i.e. "the CREATE TABLE"s) and upgrade path from an old installation (i.e. "ALTER TABLES"). I think Wikibase.sql and AddNormalizedTermsTablesDDL.sql should go to a separate directory (and possibly merged to one)

  • investigate which parts of DatabaseSchemaUpdater can maybe be deleted because they are very old
  • we probably can mark everything for deletion older than MediaWiki 1.31

Mediawiki core actually supports upgrading from basically any version. A couple of months ago I got a ticket that I broke upgrading from 1.10 (and people are really upgrading from 1.10 to 1.33), I honestly think this is stupid and people should upgrade in batches (1..10 -> 1.20 -> 1.25 -> 1.33 for example) but for now, that's how it works (maybe we should change or don't comply to it)

We still need to confirm whether abstract schema _changes_ is actually already available. The docs indicate that it is not done yet, but they might just be outdated.

This seems to be blocked by T230420: Introduce JSON-based Doctrine-DBAL-based SchemaDiffBuilder

Some parts of it is not blocked :D

Izno added a subscriber: Izno.Aug 5 2020, 8:16 PM

Mediawiki core actually supports upgrading from basically any version. A couple of months ago I got a ticket that I broke upgrading from 1.10 (and people are really upgrading from 1.10 to 1.33), I honestly think this is stupid and people should upgrade in batches (1..10 -> 1.20 -> 1.25 -> 1.33 for example) but for now, that's how it works (maybe we should change or don't comply to it)

Slightly off-topic... (The handful of upgraders who have stopped in the MediaWiki Discord usually make smaller, jumps indeed, such as LTS to LTS.)

Mediawiki core actually supports upgrading from basically any version. A couple of months ago I got a ticket that I broke upgrading from 1.10 (and people are really upgrading from 1.10 to 1.33), I honestly think this is stupid and people should upgrade in batches (1..10 -> 1.20 -> 1.25 -> 1.33 for example) but for now, that's how it works (maybe we should change or don't comply to it)

Slightly off-topic... (The handful of upgraders who have stopped in the MediaWiki Discord usually make smaller, jumps indeed, such as LTS to LTS.)

Started a RFC to drop such support anyway T259771: RFC: Drop support for database upgrade older than two LTS releases

darthmon_wmde raised the priority of this task from Medium to Needs Triage.Aug 11 2020, 12:22 PM
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptAug 11 2020, 7:11 PM

Change 619555 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Reorganize client schema scripts

https://gerrit.wikimedia.org/r/619555

Change 619720 had a related patch set uploaded (by Lucas Werkmeister (WMDE); owner: Lucas Werkmeister (WMDE)):
[mediawiki/extensions/Wikibase@master] Remove duplicate code in SqlUsageTrackerSchemaUpdater

https://gerrit.wikimedia.org/r/619720

Change 619850 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Reorganize repo schema scripts

https://gerrit.wikimedia.org/r/619850

Change 619555 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Reorganize client schema scripts

https://gerrit.wikimedia.org/r/619555

Change 619720 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Remove duplicate code in SqlUsageTrackerSchemaUpdater

https://gerrit.wikimedia.org/r/619720

Change 619850 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Reorganize repo schema scripts

https://gerrit.wikimedia.org/r/619850

@Ladsgroup As far as I understand, the “use new MediaWiki abstract schema change features” part of this is not done yet. Should we still do that, or postpone it for now?

Yeah, it's not done now, I'm fine either way, I can start with client which is simpler

Change 621037 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate entity_usage table in client to abstract schema

https://gerrit.wikimedia.org/r/621037

Change 621037 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate entity_usage table in client to abstract schema

https://gerrit.wikimedia.org/r/621037

Change 621554 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_property_info to abstract schema

https://gerrit.wikimedia.org/r/621554

Change 621559 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate the new term store to abstract schema

https://gerrit.wikimedia.org/r/621559

Change 621554 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_property_info to abstract schema

https://gerrit.wikimedia.org/r/621554

Change 621559 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate the new term store to abstract schema

https://gerrit.wikimedia.org/r/621559

Change 621717 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Rename AddNormalizedTermsTablesDDL to term_store

https://gerrit.wikimedia.org/r/621717

Change 621717 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Rename AddNormalizedTermsTablesDDL to term_store

https://gerrit.wikimedia.org/r/621717

Change 621911 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/WikimediaMaintenance@master] Fix addWiki.php for moved sql files in Wikibase

https://gerrit.wikimedia.org/r/621911

Change 621911 merged by jenkins-bot:
[mediawiki/extensions/WikimediaMaintenance@master] Fix addWiki.php for moved sql files in Wikibase

https://gerrit.wikimedia.org/r/621911

Change 621921 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/WikimediaMaintenance@master] Fix createExtensionTables.php for moved sql files in Wikibase

https://gerrit.wikimedia.org/r/621921

Change 621921 merged by jenkins-bot:
[mediawiki/extensions/WikimediaMaintenance@master] Fix createExtensionTables.php for moved sql files in Wikibase

https://gerrit.wikimedia.org/r/621921

Goes back to TODO, I guess, for futher schema migrations.

Addshore updated the task description. (Show Details)Tue, Aug 25, 12:25 PM
Addshore updated the task description. (Show Details)
Addshore updated the task description. (Show Details)
Addshore removed Ladsgroup as the assignee of this task.Tue, Aug 25, 12:27 PM

Change 622659 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Move changes_* files out of sql archives

https://gerrit.wikimedia.org/r/622659

Ladsgroup claimed this task.Mon, Sep 7, 1:03 PM

This patch needs review ^ :D

Change 622659 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Move changes_* files out of sql archives

https://gerrit.wikimedia.org/r/622659

Change 627277 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_id_counters to abstract schema

https://gerrit.wikimedia.org/r/627277

Change 627281 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_items_per_site to abstract schema

https://gerrit.wikimedia.org/r/627281

Change 627285 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_changes_subscription to abstract schema

https://gerrit.wikimedia.org/r/627285

Change 627347 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_changes table to abstract schema

https://gerrit.wikimedia.org/r/627347

This is mostly done, All tables are migrated except two:

  • wb_terms, it has some complexities (float data type) that is not yet doable in abstract schema. We can wait for core to implement it (which hopefully happen in a month or two) or just wait until we remove wb_terms altogether.
  • wb_changes_dispatch: It's waiting for timestamp data type getting handled properly in core (https://gerrit.wikimedia.org/r/c/mediawiki/core/+/626854)

Once these are done, we should clean up the file names but that's easy.

Change 627277 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_id_counters to abstract schema

https://gerrit.wikimedia.org/r/627277

Change 627281 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_items_per_site to abstract schema

https://gerrit.wikimedia.org/r/627281

Change 627285 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_changes_subscription to abstract schema

https://gerrit.wikimedia.org/r/627285

Change 627347 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_changes table to abstract schema

https://gerrit.wikimedia.org/r/627347

Change 627836 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Rename changes_* schema files

https://gerrit.wikimedia.org/r/627836

Change 627836 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Rename changes_* schema files

https://gerrit.wikimedia.org/r/627836

Now waiting on the core patch getting merged so we can pick up wb_changes_dispatch and dropping wb_terms.

Michael set Due Date to Tue, Sep 29, 10:00 PM.Wed, Sep 16, 3:33 PM

Added (default?) 2 week due date to see if https://gerrit.wikimedia.org/r/c/mediawiki/core/+/626854 is merged, then we can proceed as described above (T205094#6460185).

Added (default?) 2 week due date to see if https://gerrit.wikimedia.org/r/c/mediawiki/core/+/626854 is merged, then we can proceed as described above (T205094#6460185).

It's merged now, picking it up again.

Change 628469 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Improve TinyIntType

https://gerrit.wikimedia.org/r/628469

Change 628470 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Migrate wb_changes_dispatch to abstract schema

https://gerrit.wikimedia.org/r/628470

Change 628471 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Rename changes_dispatch to wb_changes_dispatch

https://gerrit.wikimedia.org/r/628471

Change 628469 merged by jenkins-bot:
[mediawiki/core@master] Improve TinyIntType

https://gerrit.wikimedia.org/r/628469

Change 628470 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Migrate wb_changes_dispatch to abstract schema

https://gerrit.wikimedia.org/r/628470

Change 628471 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Rename changes_dispatch to wb_changes_dispatch

https://gerrit.wikimedia.org/r/628471

The only thing left is wb_terms and I assume this will be handled on the wb_terms clean up hike.