Schema
- Move the target-related fields in ipblocks to a separate table.
- Rename ipblocks to block.
- bt_address will be null for user blocks.
- bt_user_text will be null for IP blocks.
CREATE TABLE block_target ( bt_id INT UNSIGNED AUTO_INCREMENT NOT NULL, bt_address TINYBLOB, bt_user INT UNSIGNED, bt_user_text VARBINARY(255), bt_auto TINYINT(1) DEFAULT 0 NOT NULL, bt_range_start TINYBLOB, bt_range_end TINYBLOB, bt_ip_hex TINYBLOB, bt_count int NOT NULL default 0, INDEX bt_address ( bt_address(42) ), INDEX bt_user (bt_user), INDEX bt_range ( bt_range_start(35), bt_range_end(35) ), INDEX bt_ip_user_text ( bt_ip_hex(35), bt_user_text(255) ), PRIMARY KEY(bt_id) ); CREATE TABLE block ( bl_id INT UNSIGNED AUTO_INCREMENT NOT NULL, -- New foreign key bl_target INT UNSIGNED NOT NULL, bl_by_actor BIGINT UNSIGNED NOT NULL, bl_reason_id BIGINT UNSIGNED NOT NULL, bl_timestamp BINARY(14) NOT NULL, bl_anon_only TINYINT(1) DEFAULT 0 NOT NULL, bl_create_account TINYINT(1) DEFAULT 1 NOT NULL, bl_enable_autoblock TINYINT(1) DEFAULT 1 NOT NULL, bl_expiry VARBINARY(14) NOT NULL, bl_deleted TINYINT(1) DEFAULT 0 NOT NULL, bl_block_email TINYINT(1) DEFAULT 0 NOT NULL, bl_allow_usertalk TINYINT(1) DEFAULT 0 NOT NULL, bl_parent_block_id INT UNSIGNED DEFAULT NULL, bl_sitewide TINYINT(1) DEFAULT 1 NOT NULL, -- Index on new field INDEX bl_target (bl_target), INDEX bl_timestamp (bl_timestamp), INDEX bl_expiry (bl_expiry), INDEX bl_parent_block_id (bl_parent_block_id), PRIMARY KEY(bl_id) );
Query review
Searched for /['"]ipblocks['"]/, DatabaseBlock::getQueryInfo
- Writers
- DatabaseBlockStore
- CentralAuthUser::doLocalSuppression (cross-wiki)
- MergeUser::mergeBlocks
- RenameuserSQL
- CleanupBlocks
- CleanupUsersWithNoId (probably broken already)
- Queries only for ipb_deleted
- ApiQueryBlockInfoTrait branch 2
- ActiveUsersPager
- UsersPager
- UserNamePrefixSearch
- UserSelectQueryBuilder
- LocalIdLookup
- Flow: OneStepUserNameQuery, TwoStepUserNameQuery
- WMCS maintain_views.yaml user, user_old, actor
- Readers
- ApiQueryBlocks
- ApiQueryBlockInfoTrait branch 1
- DatabaseBlockStore
- BlockListPager
- CentralAuthUser::localUserData (cross-wiki)
- \MediaWiki\CheckUser\Investigate\Services\PreliminaryCheckService::isUserBlocked
- UncachedMenteeOverviewDataProvider::getFilteredMenteesForMentor
- \MediaWiki\Extension\PageTriage\ArticleCompile\ArticleCompileUserData::compile
- PageTriage ArticleCompileUserData
- WMCS maintain_views.yaml
- Referring to the table but probably not affected
- BlockRestrictionStore::updateByParentBlockId
- findMissingActors
- removeUnusedAccounts
Migration
CentralAuth does cross-wiki read and write queries on ipblocks. This would complicate fast single-flag migration.
Multi-stage migration would have the following configuration in MW core: T346671
- $wgBlockTargetMigrationStage: a combination of SCHEMA_COMPAT_xxx flags.
- $wgEnableMultiBlocks: when this is false, the number of blocks of a given target is limited to 1.
The old Special:Block UI is shown. When this is true, multiple blocks of a target are permitted.
The deployment procedure would be as follows. Each step is global, affecting all wikis.
- Deploy the new code in read-old/write-old mode.
- Create block and block_target tables.
- Switch to write-both mode. Assume the migration script will copy each ipblocks row atomically, with ipb_id=bl_id. When updating a block, update it in ipblocks, and if there is a corresponding row in the block table, update it there too. When inserting a block, insert it into both tables. The number of blocks of a given target is artificially limited to 1. When deleting a block, delete the block_target row if the number of blocks on the target goes to zero.
- Run a script to fully populate block_target/block.
- Migrate WMCS and other miscellaneous readers.
- Switch to read-new/write-new mode.
- Drop the ipblocks tables.