Possibly schema drift? af_hidden is defined as NOT NULL but perhaps that's not the case in all wikis.

Ye ole SNAFU:

wikiadmin2023@10.192.0.100(mediawikiwiki)> select afl_filter_id from abuse_filter_log where afl_id=18673;
+---------------+
| afl_filter_id |
+---------------+
|             0 |
+---------------+
1 row in set (0.001 sec)

Now trying to figure out why.

The timestamp for that entry is 20130709031858, so somewhat old. Back then, the schema had a single column, afl_filter, that included both the filter ID and an optional global- prefix. This was split into two columns in T220791 (~2021).

For mediawikiwiki, we have a few affected entries from the same day:

wikiadmin2023@10.192.0.100(mediawikiwiki)> select afl_id,afl_timestamp from abuse_filter_log where afl_filter_id=0;
+--------+----------------+
| afl_id | afl_timestamp  |
+--------+----------------+
|  18670 | 20130709031855 |
|  18673 | 20130709031858 |
|  18687 | 20130709084504 |
|  18694 | 20130709114805 |
|  18697 | 20130709114807 |
+--------+----------------+

I also checked enwiki, testwiki, metawiki, frwiki, and itwiki, but found nothing. I guess I could run it for all wikis, but for now it doesn't seem useful. Like with T388183, my first question would be whether the value has always been bad, or if it became bad as part of the migration. The update script is relatively simple and I don't see how it could've failed, assuming the data wasn't malformed in the first place. Also, the temporal proximity of the affected entries is suspicious.

Checking the SAL, there are interesting entries for 2013-07-08:

23:53 logmsgbot: catrope Finished syncing Wikimedia installation... : Updating VE and AbuseFilter
23:43 logmsgbot: catrope Started syncing Wikimedia installation... : Updating VE and AbuseFilter

And then on the next day:

16:50 logmsgbot: anomie synchronized php-1.22wmf9/extensions/AbuseFilter 'Update AbuseFilter to master for global filter bug fixes'

A quick git blame led me to https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/72716:

Don't trigger filter hits for nonexisting filter 0

AbuseFilter::getConsequencesForFilters uses filter ids as
array keys, that causes problems if both local and global
filters were hit, because array_merge reindexes arrays.

Example:
https://www.mediawiki.org/wiki/Special:AbuseLog/18687

It's a real shame that the exception URL for this task and the example given in that commit message are different. That would've been so poetic, 12 years later, the circularity, etc.

So, since this issue only existed for a few hours, I'm guessing we could just fix whatever broken entries we have around. We only need to find them. They can only be in wikis that had global filters enabled at the time, which is why my initial search wasn't fruitful. There's also the question of what filter originally caused those entries, but I think that should be answerable by checking the AbuseLog on meta.

In T388732#10631242, @Daimona wrote:

There's also the question of what filter originally caused those entries, but I think that should be answerable by checking the AbuseLog on meta.

ez

wikiadmin2023@10.192.23.13(metawiki)> select afl_filter_id,afl_wiki,afl_timestamp from abuse_filter_log where afl_timestamp in (20130709031855, 20130709031858, 20130709084504, 20130709114805, 20130709114807);
+---------------+---------------+----------------+
| afl_filter_id | afl_wiki      | afl_timestamp  |
+---------------+---------------+----------------+
|            72 | mediawikiwiki | 20130709031855 |
|            72 | mediawikiwiki | 20130709031858 |
|            72 | mediawikiwiki | 20130709084504 |
|            72 | mediawikiwiki | 20130709114805 |
|            72 | mediawikiwiki | 20130709114807 |
+---------------+---------------+----------------+
5 rows in set (1.289 sec)

So, for mw.org, we would need:

update abuse_filter_log set afl_global=1, afl_filter_id=72 where afl_filter_id=0;

And what's even better, there don't seem to be affected rows in any other wiki:

wikiadmin2023@10.192.23.13(metawiki)> select afl_filter_id,afl_wiki,afl_timestamp from abuse_filter_log where afl_wiki is not null and afl_timestamp > '20130708235000' and afl_timestamp < '20130709170000';
+---------------+---------------+----------------+
| afl_filter_id | afl_wiki      | afl_timestamp  |
+---------------+---------------+----------------+
|            72 | mediawikiwiki | 20130709031855 |
|            72 | mediawikiwiki | 20130709031858 |
|            72 | mediawikiwiki | 20130709084504 |
|            72 | mediawikiwiki | 20130709114805 |
|            72 | mediawikiwiki | 20130709114807 |
+---------------+---------------+----------------+
5 rows in set (0.008 sec)

Should I tag DBA to run the UPDATE query above, or what is the process for these one-off things?

Probably just fix it yourself tbh. Updating 5 rows isn't going to cause replag or anything.

You could do the update based on where afl_id IN ( id1, id2...idn) if you wanted to be extra specific... As I guess afl_filter_id isn't indexed, and you didn't mention how long the where afl_filter_id=0 query took.

Not that at 546953 rows, it's that big of a table... but still...

Mentioned in SAL (#wikimedia-operations) [2025-03-13T01:13:55Z] <Daimona> Manually fixing 5 bad abuse_filter_log rows in mediawikiwiki for T388732

If you're bored... Beta-Cluster-Infrastructure has the same issue....

Seemingly on https://ar.wikipedia.beta.wmflabs.org

In T388732#10654388, @Reedy wrote:

If you're bored... Beta-Cluster-Infrastructure has the same issue....

Seemingly on https://ar.wikipedia.beta.wmflabs.org

Indeed, and it can be reproduced by just visiting the unfiltered AbuseLog: https://ar.wikipedia.beta.wmflabs.org/wiki/%D8%AE%D8%A7%D8%B5:%D8%B3%D8%AC%D9%84_%D8%A7%D9%84%D8%A5%D8%B3%D8%A7%D8%A1%D8%A9

This doesn't tell us which entry is broken, and select afl_id,afl_timestamp from abuse_filter_log where afl_filter_id=0; yields 0 rows. As quick workaround, I messed with the URL, bisecting the limit parameter. limit=47 works fine, 48 throws. So I checked the entry in question, it is for local filter 43. Seems fine, so let's check the filter: https://ar.wikipedia.beta.wmflabs.org/wiki/%D8%AE%D8%A7%D8%B5:%D9%85%D8%B1%D8%B4%D8%AD_%D8%A7%D9%84%D8%A5%D8%B3%D8%A7%D8%A1%D8%A9/43 it does not exist. Great. And the fun doesn't end here:

MariaDB [arwiki]> select af_id from abuse_filter order by af_id asc;
+-------+
| af_id |
+-------+
|     1 |
|     4 |
|     5 |
|     6 |
|     8 |
|     9 |
|    10 |
|    11 |
|    12 |
|    14 |
|    18 |
|    19 |
|    20 |
|    21 |
|    22 |
|    26 |
|    29 |
|    32 |
|    33 |
|    35 |
|    38 |
|    45 |
|    48 |
+-------+
23 rows in set (0.001 sec)

MariaDB [arwiki]> select * from abuse_filter_history;
Empty set (0.002 sec)

So I'm guessing someone messed with the beta DB manually and left it in an inconsistent state. I don't think there's much value in trying to fix it. It could be truncated, but I don't think it's necessary.

We've just run into this at ShoutWiki after upgrading to REL1_43. Did anyone ever work out what caused this, or if it is patchable? In my search for an existing fix, I ran into this thread where another third-party user caught this error.

El T388732#11661364, @lcawte escribió:

We've just run into this at ShoutWiki after upgrading to REL1_43. Did anyone ever work out what caused this, or if it is patchable? In my search for an existing fix, I ran into this thread where another third-party user caught this error.

If entries reference filter #0, you may force it to be an existing one. See T349562#11094238