The Beta-Cluster-Infrastructure database update fails with:
Error 1091: Can't DROP INDEX `afl_filter_timestamp`; check that it exists
https://integration.wikimedia.org/ci/job/beta-update-databases-eqiad/
Due to:
Remove afl_filter entirely
https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/488482
Make mysql/patch-remove-afl_filter.sql valid SQL
https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/723662
Looks like we can get paste the issue by recreating the index which let the fixed sql patch to be applied. Example for aawiki:
hashar@deployment-deploy01:~$ echo 'CREATE INDEX /*i*/afl_filter_timestamp ON /*$wgDBprefix*/abuse_filter_log (afl_filter,afl_timestamp);'|sql aawiki --write
I ran for all labs wiki with:
for wiki in $(grep -v '#' dblists/all-labs.dblist); do echo "CREATE INDEX /*i*/afl_filter_timestamp ON /*$wgDBprefix*/abuse_filter_log (afl_filter,afl_timestamp);"|sql $wiki --write; done;
ERROR 1072 (42000) at line 1: Key column 'afl_filter' doesn't exist in table Failed. ERROR 1061 (42000) at line 1: Duplicate key name 'afl_filter_timestamp' Failed. ERROR 1061 (42000) at line 1: Duplicate key name 'afl_filter_timestamp' Failed.
Since I ran the command previously on aawiki and apiportalwiki. One wiki does not have AbuseFilter apparently.
I then ran https://integration.wikimedia.org/ci/job/beta-update-databases-eqiad/ and it passed.
Thank you @Majavah and @DannyS712 for the lead about it this morning ;)
Just to point out, it was not broken by my fix in "Make mysql/patch-remove-afl_filter.sql valid SQL" (made at nearly 4am).
That made the patch able to finish successfully (ie being able to, but because the original patch did multiple things, it left the wikis in an inconsistent state, so it would then try and drop the index again (because of the guard on how the extension ran the patch) etc.
Hence no change was needed to it, just to fix the state of beta so it could run successfully
We hit the same issue when updating translatewiki.net from 1.37 to master. I don't think it's acceptable to expect all wikis using AbuseFilter to figure out how to apply this workaround.
Feel free to re-close if this doesn't affect all wikis.
I totally agree, but the workaround above shouldn't have been necessary at all. The index in question was originally called "filter_timestamp", then renamed to "afl_filter_timestamp" in r593906 (REL1_37), and then dropped with r488482 (REL1_38). I think there might be a bug in the updater logic, I'll check later.
I'm guessing the problem is still that the patch partially executed on translatewiki too. Or was never properly renamed?
So the DROP INDEX /*i*/afl_filter_timestamp ON /*_*/abuse_filter_log; worked, but then the second command failed..
$updater->dropExtensionField( 'abuse_filter_log', 'afl_filter', "$dir/$dbType/patch-remove-afl_filter.sql" );
So as the guard is whether abuse_filter_log.afl_filter still exists... It's probably easiest to either split the file in two (one to drop the index, one to do the column updates), and then add a seperate/extra DatabaseUpdater call for it.
@Nikerabbit Do you happen to still have the console output of the updater? Also, would it be possible to post the result of show index from abuse_filter_log?
That's a possible explanation, but it means that the updater was executed between my patch and your patch, i.e. between 3:39 and 4:59 on Sept. 25. Is that the case?
Partial one:
...abuse_filter table already exists. ...abuse_filter_log table does not contain afl_log_id field. ...have afl_filter_id field in abuse_filter_log table. ...index afl_wiki_timestamp already set on abuse_filter_log table. Table abuse_filter_log contains afl_filter field. Dropping ...Wikimedia\Rdbms\DBQueryError from line 1809 of /srv/mediawiki/workdir/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP INDEX `afl_filter_timestamp`; check that it exists (127.0.0.1:3306) Function: Wikimedia\Rdbms\Database::sourceFile( /srv/mediawiki/workdir/extensions/AbuseFilter/includes/Hooks/Handlers/../../../db_patches/mysql/patch-remove-afl_filter.sql ) Query: DROP INDEX afl_filter_timestamp ON `bw_abuse_filter_log`
After upgrade (database prefix added manually):
MariaDB [translatewiki_net]> show index from bw_abuse_filter_log; +---------------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | bw_abuse_filter_log | 0 | PRIMARY | 1 | afl_id | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | filter_timestamp | 1 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | user_timestamp | 1 | afl_user | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | user_timestamp | 2 | afl_user_text | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | user_timestamp | 3 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | afl_timestamp | 1 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | page_timestamp | 1 | afl_namespace | A | 4 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | page_timestamp | 2 | afl_title | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | page_timestamp | 3 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | ip_timestamp | 1 | afl_ip | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | ip_timestamp | 2 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | afl_rev_id | 1 | afl_rev_id | A | 2 | NULL | NULL | YES | BTREE | | | | bw_abuse_filter_log | 1 | filter_timestamp_full | 1 | afl_global | A | 2 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | filter_timestamp_full | 2 | afl_filter_id | A | 6 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | filter_timestamp_full | 3 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | | bw_abuse_filter_log | 1 | afl_wiki_timestamp | 1 | afl_wiki | A | 2 | NULL | NULL | YES | BTREE | | | | bw_abuse_filter_log | 1 | afl_wiki_timestamp | 2 | afl_timestamp | A | 333 | NULL | NULL | | BTREE | | | +---------------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 17 rows in set (0.001 sec)
We do have a database backup from before the update if needed (just let me know if I should preserve it before it's overwritten).
For context, we were for a long time at REL1_36 (since 2021-05-05), which we started using when we hit compatibility issues with some extensions (we rolled back a bit from master that had some changes after REL1_36 was branched). One week ago we updated to REL1_37 and week ago to master again.
Yes, it'd be helpful to see the indexes there. I don't think it needs to be preserved, I assume the indexes can be recreated at any time if something goes horribly wrong.
This is from the backup before latest database update:
DROP TABLE IF EXISTS `bw_abuse_filter_log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bw_abuse_filter_log` ( `afl_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `afl_filter` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `afl_global` tinyint(1) NOT NULL DEFAULT 0, `afl_filter_id` bigint(20) unsigned NOT NULL DEFAULT 0, `afl_user` bigint(20) unsigned NOT NULL, `afl_user_text` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin 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(11) NOT NULL, `afl_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `afl_wiki` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `afl_deleted` tinyint(1) NOT NULL DEFAULT 0, `afl_patrolled_by` int(10) unsigned NOT NULL DEFAULT 0, `afl_rev_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`afl_id`), KEY `filter_timestamp` (`afl_filter`,`afl_timestamp`), KEY `user_timestamp` (`afl_user`,`afl_user_text`,`afl_timestamp`), KEY `afl_timestamp` (`afl_timestamp`), KEY `page_timestamp` (`afl_namespace`,`afl_title`,`afl_timestamp`), KEY `ip_timestamp` (`afl_ip`,`afl_timestamp`), KEY `afl_rev_id` (`afl_rev_id`), KEY `filter_timestamp_full` (`afl_global`,`afl_filter_id`,`afl_timestamp`), KEY `afl_wiki_timestamp` (`afl_wiki`,`afl_timestamp`) ) ENGINE=InnoDB AUTO_INCREMENT=334 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */;
Ughhhh there's something wrong in there...
KEY `filter_timestamp` (`afl_filter`,`afl_timestamp`), ... KEY `afl_wiki_timestamp` (`afl_wiki`,`afl_timestamp`)
My guess is that https://phabricator.wikimedia.org/diffusion/EABF/browse/master/db_patches/mysql/patch-rename-indexes.sql failed to run because the index already had the new name. So I guess it might've been an older DB patch causing this... I'll take a look.
@Nikerabbit Do you also happen to have a backup from before the 1.37 upgrade? I'm not sure why the afl_wiki_timestamp index is already correct. I can't see anything obviously wrong in the db patches.
I'd just like to see the abuse_filter_log indexes there, to understand when the drift might have originated.
Looking at a backup of two weeks ago, the table definition is the same (except auto increment is lower). Is it possible this could have happened before REL1_36?
Yes, totally. I think at this point, the best thing to do is to split the renaming of the afl_wiki_timestamp index and the others.
Change 731151 had a related patch set uploaded (by Daimona Eaytoy; author: Daimona Eaytoy):
[mediawiki/extensions/AbuseFilter@master] Move renaming of the afl_wiki_timestamp index to its own file
Change 731151 merged by jenkins-bot:
[mediawiki/extensions/AbuseFilter@master] Move renaming of the afl_wiki_timestamp index to its own file
Change 731285 had a related patch set uploaded (by Daimona Eaytoy; author: Daimona Eaytoy):
[mediawiki/extensions/AbuseFilter@master] Remove references to afl_filter
Change 731285 merged by jenkins-bot:
[mediawiki/extensions/AbuseFilter@master] Remove references to afl_filter
This is failing for me locally as well (and still failing on master).
Table abuse_filter_log contains afl_filter field. Dropping ...Wikimedia\Rdbms\DBQueryError from line 1809 of /var/www/html/w/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP 'afl_filter_timestamp'; check that column/key exists (localhost)
Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/w/extensions/AbuseFilter/includes/Hooks/Handlers/../../../db_patches/mysql/patch-remove-afl_filter.sql )
Query: DROP INDEX afl_filter_timestamp ON `abuse_filter_log`
#0 /var/www/html/w/includes/libs/rdbms/database/Database.php(1793): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/html/w/includes/libs/rdbms/database/Database.php(1768): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/html/w/includes/libs/rdbms/database/Database.php(1327): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/html/w/includes/libs/rdbms/database/Database.php(5407): Wikimedia\Rdbms\Database->query()
#4 /var/www/html/w/includes/libs/rdbms/database/Database.php(5340): Wikimedia\Rdbms\Database->sourceStream()
#5 /var/www/html/w/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile()
#6 /var/www/html/w/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call()
#7 /var/www/html/w/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile()
#8 /var/www/html/w/includes/installer/DatabaseUpdater.php(862): DatabaseUpdater->applyPatch()
#9 /var/www/html/w/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->dropField()
#10 /var/www/html/w/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates()
#11 /var/www/html/w/maintenance/update.php(193): DatabaseUpdater->doUpdates()
#12 /var/www/html/w/maintenance/doMaintenance.php(108): UpdateMediaWiki->execute()
#13 /var/www/html/w/maintenance/update.php(264): require_once('/var/www/html/w...')
#14 {main}(I just removed the DROP INDEX statement and everything else worked)
Change 732317 had a related patch set uploaded (by Daimona Eaytoy; author: Daimona Eaytoy):
[mediawiki/extensions/AbuseFilter@master] Rename filter_timestamp index in a different file
Hopefully fixed in the patch above... I really really really hate the level of inconsistency reached by this schema over the years, and how much work needed to be done in order to fix it. If only T191231 had been a thing 10 years ago...
Change 732317 merged by jenkins-bot:
[mediawiki/extensions/AbuseFilter@master] Rename filter_timestamp index in a different file
Now I think you've broken my dev wiki... xD
Renaming index filter_timestamp into afl_filter_timestamp to table abuse_filter_log ...Wikimedia\Rdbms\DBQueryError from line 1809 of /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php: Error 1072: Key column 'afl_filter' doesn't exist in table (10.13.37.212:3306)
Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/wiki/mediawiki/extensions/AbuseFilter/includes/Hooks/Handlers/../../../db_patches/mysql/patch-rename-filter_timestamp-index.sql )
Query: CREATE INDEX afl_filter_timestamp ON `mw_abuse_filter_log` (afl_filter,afl_timestamp)
#0 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php(1793): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php(1768): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php(1327): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php(5407): Wikimedia\Rdbms\Database->query()
#4 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/Database.php(5340): Wikimedia\Rdbms\Database->sourceStream()
#5 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile()
#6 /var/www/wiki/mediawiki/core/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call()
#7 /var/www/wiki/mediawiki/core/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile()
#8 /var/www/wiki/mediawiki/core/includes/installer/DatabaseUpdater.php(951): DatabaseUpdater->applyPatch()
#9 /var/www/wiki/mediawiki/core/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->renameIndex()
#10 /var/www/wiki/mediawiki/core/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates()
#11 /var/www/wiki/mediawiki/core/maintenance/update.php(193): DatabaseUpdater->doUpdates()
#12 /var/www/wiki/mediawiki/core/maintenance/doMaintenance.php(108): UpdateMediaWiki->execute()
#13 /var/www/wiki/mediawiki/core/maintenance/update.php(264): require_once('/var/www/wiki/m...')
#14 {main}:O
I'm not sure how this could be possible. The DB updater ensures that the old index (filter_timestamp) exists, and that index already uses the afl_filter column. Would you mind pasting the table structure and details on its indexes? (describe mw_abuse_filter_log, show index from mw_abuse_filter_log)
MariaDB [wikidb]> describe mw_abuse_filter_log; +------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------------+------+-----+---------+----------------+ | afl_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | afl_global | tinyint(1) | NO | MUL | 0 | | | afl_filter_id | bigint(20) unsigned | NO | | 0 | | | afl_user | bigint(20) unsigned | NO | MUL | NULL | | | afl_user_text | varbinary(255) | NO | | NULL | | | afl_ip | varbinary(255) | NO | MUL | NULL | | | afl_action | varbinary(255) | NO | | NULL | | | afl_actions | varbinary(255) | NO | | NULL | | | afl_var_dump | blob | NO | | NULL | | | afl_timestamp | binary(14) | NO | MUL | NULL | | | afl_namespace | int(11) | NO | MUL | NULL | | | afl_title | varbinary(255) | NO | | NULL | | | afl_wiki | varbinary(64) | YES | MUL | NULL | | | afl_deleted | tinyint(1) | NO | | 0 | | | afl_patrolled_by | int(10) unsigned | NO | | 0 | | | afl_rev_id | int(10) unsigned | YES | MUL | NULL | | +------------------+---------------------+------+-----+---------+----------------+ 16 rows in set (0.002 sec) MariaDB [wikidb]> show index from mw_abuse_filter_log; +---------------------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | mw_abuse_filter_log | 0 | PRIMARY | 1 | afl_id | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_timestamp | 1 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_rev_id | 1 | afl_rev_id | A | 0 | NULL | NULL | YES | BTREE | | | | mw_abuse_filter_log | 1 | afl_wiki_timestamp | 1 | afl_wiki | A | 0 | NULL | NULL | YES | BTREE | | | | mw_abuse_filter_log | 1 | afl_wiki_timestamp | 2 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_filter_timestamp_full | 1 | afl_global | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_filter_timestamp_full | 2 | afl_filter_id | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_filter_timestamp_full | 3 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_user_timestamp | 1 | afl_user | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_user_timestamp | 2 | afl_user_text | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_user_timestamp | 3 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_page_timestamp | 1 | afl_namespace | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_page_timestamp | 2 | afl_title | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_page_timestamp | 3 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_ip_timestamp | 1 | afl_ip | A | 0 | NULL | NULL | | BTREE | | | | mw_abuse_filter_log | 1 | afl_ip_timestamp | 2 | afl_timestamp | A | 0 | NULL | NULL | | BTREE | | | +---------------------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 16 rows in set (0.001 sec)
Or if it's easier to read...
MariaDB [wikidb]> show index from mw_abuse_filter_log\G *************************** 1. row *************************** Table: mw_abuse_filter_log Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: afl_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_timestamp Seq_in_index: 1 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_rev_id Seq_in_index: 1 Column_name: afl_rev_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_wiki_timestamp Seq_in_index: 1 Column_name: afl_wiki Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_wiki_timestamp Seq_in_index: 2 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_filter_timestamp_full Seq_in_index: 1 Column_name: afl_global Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_filter_timestamp_full Seq_in_index: 2 Column_name: afl_filter_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 8. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_filter_timestamp_full Seq_in_index: 3 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 9. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_user_timestamp Seq_in_index: 1 Column_name: afl_user Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 10. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_user_timestamp Seq_in_index: 2 Column_name: afl_user_text Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 11. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_user_timestamp Seq_in_index: 3 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 12. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_page_timestamp Seq_in_index: 1 Column_name: afl_namespace Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 13. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_page_timestamp Seq_in_index: 2 Column_name: afl_title Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 14. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_page_timestamp Seq_in_index: 3 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 15. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_ip_timestamp Seq_in_index: 1 Column_name: afl_ip Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 16. row *************************** Table: mw_abuse_filter_log Non_unique: 1 Key_name: afl_ip_timestamp Seq_in_index: 2 Column_name: afl_timestamp Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 16 rows in set (0.002 sec)
Right, on second thought, the index rename failed at the recreation step, but the old index got deleted anyway, so of course it doesn't show up... I think perhaps it might be due to some past run of update.php which failed to properly delete the index.
Why is even trying to create an index afl_filter_timestamp that no longer exists in the table definitions? Had the same trouble at translatewiki.net
The renaming of filter_timestamp index to afl_filter_timestamp happened in a previous release (1.36, I think). Now we're dropping the index, but the db patch which drops it only looks for the new name, so we want to rename it first.
During the update 1.31 → 1.35 → 1.39 of Vikidia, this issue was triggered, either in the original form Can't DROP 'filter_timestamp_full' with some upgrade path either in the form Duplicate key name 'afl_filter_timestamp_full' in another upgrade path (this second one because afl_filter_timestamp_full is created just before by patch-split-afl_filter.sql when there is no column abuse_filter_log.afl_filter_id in 1.39 updater).
→ It was mostly solved by a patch where patch-rename-indexes.sql was entirely splitted into unitary units: one renaming per file, similarly to what was done for wiki_timestamp and filter_timestamp.
We (me and @NavidBoyWiki) find it is safer to do so, because, depending of the upgrade paths and the history of each index, some indexes may be created and some other may not. E.g. if indexA is created in 1.35 and renamed in 1.39 into indexA2, and indexB is created in 1.37 and renamed in 1.39 into indexB2, and if you jointly rename both indexes in 1.39 after checking the existence of indexA, then in an upgrade path 1.35 → 1.39, the updater will try to rename the non-existing indexB and it will hard-fail like described here with Can't DROP (alternatively if you check the existence of indexB, then indexA will not be renamed and indexB2 will not exist, meaning a degraded performance to due missing indexes and possible DB-update-errors in further updates or even data corruption if the index has a UNIQUE constraint).
One supplementary issue with this solution (split the global patch) is that, at the end in 1.39, there is no index (e.g.) ip_timestamp, because it was it was non-existing in 1.35, so it was not renamed, so it is non-existing in 1.39, although it would be in a new installation in 1.39. → A proper solution would be a PHP function renameOrCreateIndex instead of renameIndex in DatabaseUpdater to be sure that at the end, the old index no more exists and the new index does exist (sadly it seems there is no SQL parameter DROP INDEX [IF EXISTS] or CREATE INDEX [IF NOT EXISTS]).