Upgrading from 1.35 to 1.39 causes AbuseLog entries to reference filter 0 instead of the actual filter (data loss during upgrade)
Open, Needs TriagePublicBUG REPORT
Actions

Assigned To

None

Authored By

	Ciencia_Al_Poder
	Oct 23 2023, 9:01 PM

Description

Steps to replicate the issue

Have a wiki on MediaWiki 1.35 (an LTS version) with AbuseFilter installed and Special:AbuseLog displaying entries for various filters
Upgrade to MediaWiki 1.39 (next LTS version) and run update.php

What happens?:

Special:AbuseLog now displays all entries referencing filter 0. Clicking on "examine" will throw an error: MediaWiki\Extension\AbuseFilter\Filter\FilterNotFoundException: Filter 0 does not exist

What should have happened instead?:

Special:AbuseLog should work correctly.

Software version 1.39

Other information

The abuse_filter_log table was storing the related abuse filter id on the afl_filter, but now it stores it on the afl_filter_id. See change https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/459818/ - REL1_36

The data should be migrated with the maintenance/migrateAflFilter.php script.

However, this script was deleted in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/723659 - REL1_38 - Only 2 versions after it was introduced, and both between the same LTS versions. This violates the Compatibility policy for upgrades

Event Timeline

Ciencia_Al_Poder created this task.Oct 23 2023, 9:01 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 23 2023, 9:01 PM

This is unfortunate. As a general extension, AbuseFilter wasn't covered by the LTS policy until it was added to the group of bundled extensions in 1.38. Not quite sure what policy here would be.

For fixing your immediate problem, I'd strongly recommend you upgrade to 1.38 first and then 1.39.

If anyone else encounters this problem and wants to recover the data from backup (only this data, and not rollback the entire database), follow this steps:

I created a ~/greppatterns_af file with the contents:

^-- Current Database:
^USE `
^-- Dumping data for table `[a-z_]*abuse_filter_log`
^INSERT INTO `[a-z_]*abuse_filter_log`

Then grepped the sql dump file for those inserts. This will include only the "use" statements to switch to a database, and the INSERT INTO statements to the abuse_filter_log table (including random prefixes unique to each db, if any):

nice zcat alldb_pre139update.sql.gz | nice grep -f ~/greppatterns_af > ~/abusefilterlogs20231021.sql

Use sed to replace the tables with abuse_filter_log_old, into a new file:

cat ~/abusefilterlogs20231021.sql | nice sed 's/abuse_filter_log`/abuse_filter_log_old`/g' > ~/abusefilterlogs20231021_suffixed.sql

Create a file ~/abusefilter-patch-aflog135.sql with the definition of the table abuse_filter_log in 1.35 (found in the extension's sql file), removing index definitions, and adding a _old suffix:

CREATE TABLE /*$wgDBprefix*/abuse_filter_log_old (
        afl_id BIGINT unsigned NOT NULL AUTO_INCREMENT,
        afl_filter varchar(64) binary NOT NULL DEFAULT '',
        afl_global tinyint(1) NOT NULL DEFAULT 0,
        afl_filter_id BIGINT unsigned NOT NULL DEFAULT 0,
        afl_user BIGINT unsigned NOT NULL,
        afl_user_text varchar(255) binary NOT NULL,
        afl_ip varchar(255) not null,
        afl_action varbinary(255) not null,
        afl_actions varbinary(255) not null,
        afl_var_dump BLOB NOT NULL,
        afl_timestamp binary(14) NOT NULL,
        afl_namespace int NOT NULL,
        afl_title varchar(255) binary NOT NULL,
        afl_wiki varchar(64) binary NULL,
        afl_deleted tinyint(1) NOT NULL DEFAULT 0,
        afl_patrolled_by int unsigned NOT NULL DEFAULT 0,
        afl_rev_id int unsigned,

        PRIMARY KEY (afl_id)
) /*$wgDBTableOptions*/;

Create the table on all databases:

nice sudo -u www-data php7.4 maintenance/sql.php ~/abusefilter-patch-aflog135.sql

Create a file ~/abusefilter-patch-aflog135-fixdata.sql to update the data:

update /*$wgDBprefix*/abuse_filter_log as new, /*$wgDBprefix*/abuse_filter_log_old as old set 
  new.afl_filter_id = cast(REPLACE(old.afl_filter,'global-','') as int), new.afl_global = (CASE WHEN old.afl_filter LIKE 'global-%' THEN 1 ELSE 0 END) where 
  new.afl_id = old.afl_id and new.afl_filter_id = 0
;

Run on all databases:

nice sudo -u www-data php7.4 maintenance/sql.php ~/abusefilter-patch-aflog135-fixdata.sql

Create a file ~/abusefilter-patch-aflog135-droptable.sql to delete the old table:

DROP TABLE /*$wgDBprefix*/abuse_filter_log_old;

Run on all databases:

nice sudo -u www-data php7.4 maintenance/sql.php ~/abusefilter-patch-aflog135-droptable.sql

All filter data should be fixed now.

Upgrading from 1.35 to 1.39 causes AbuseLog entries to reference filter 0 instead of the actual filter (data loss during upgrade)Open, Needs TriagePublicBUG REPORTActions

Description

Event Timeline

Upgrading from 1.35 to 1.39 causes AbuseLog entries to reference filter 0 instead of the actual filter (data loss during upgrade)
Open, Needs TriagePublicBUG REPORT
Actions