I've indeed tested Special:DeletedContributions on REL1_28 causing the error, but this doesn't happen on REL1_27.

The problem is a query trying to use index usertext_timestamp from the archive table, but the archive doesn't have that index, only ar_usertext_timestamp. usertext_timestamp index exists only for the revision table. See tables.sql definition.

In code I only see usage of that index on DeletedContribsPager.php.

The confusing part is that tables.sql on REL1_27 doesn't have usertext_timestamp neither, but ar_usertext_timestamp (REL1_27 tables.sql). Still, the *real* database schema for 1.27 has usertext_timestamp:

MariaDB [(none)]> show index from test128wiki.archive;
+---------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| archive |          0 | PRIMARY               |            1 | ar_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp  |            1 | ar_namespace | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp  |            2 | ar_title     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp  |            3 | ar_timestamp | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | ar_usertext_timestamp |            1 | ar_user_text | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | ar_usertext_timestamp |            2 | ar_timestamp | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | ar_revid              |            1 | ar_rev_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> show index from test127wiki.archive;
+---------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| archive |          0 | PRIMARY              |            1 | ar_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp |            1 | ar_namespace | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp |            2 | ar_title     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | name_title_timestamp |            3 | ar_timestamp | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | usertext_timestamp   |            1 | ar_user_text | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | usertext_timestamp   |            2 | ar_timestamp | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| archive |          1 | ar_revid             |            1 | ar_rev_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

This means, a wiki upgraded from versions before 1.28 won't show this problem, only new 1.28 wikis will have the problem.

There's a patch-archive-user-index.sql that indeed adds the index with that name, but in that case both index should be there instead of just one, shouldn't it?

Any updates on this issue?

This feels dejavu... And potentially something related to the db class librarisation(?)... Ping @aaron

@Reception123: If there were any updates they'd be in this task, so your question is too broad. Do you have a more specific question?

@Aklapper I mean if theres any ETA on when this issue will be fixed and if anyone is thinking of backporting.

I same get error with this and have message "Error: 1176 Key 'usertext_timestamp' doesn't exist in table 'archive' (localhost)". How to fix? @Aklapper @Reception123

@Mbrt: See the comments in this task - if there is a known fix for an issue, it is mentioned in a task. (It's not that I knew more than what is written in a task anyway. :) )

This works for me on MediaWiki 1.29.

Ah, i found this https://github.com/wikimedia/mediawiki/blob/e01fd443887b47c86d5248a4a32eca5e5ed98a97/includes/specials/pagers/DeletedContribsPager.php#L132

The problem would still be persistent on 1.28 and the stable release of 1.29 is only set for June, still making this a problem.

Change 344434 had a related patch set (by Paladox) published:
[mediawiki/core@master] DeletedContribsPager: Replace index usertext_timestamp with ar_usertext_timestamp

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

@Reception123 hi, could you please cherry pick ^^ onto your REL1_28 version to test please?

@Paladox Confirm it fixes the issue.

Thanks.

See also T104756

Change 344452 had a related patch set uploaded (by Paladox):
[mediawiki/core@master] Migrate usertext_timestamp to ar_usertext_timestamp on wiki's lower then 1.28

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

Change 344460 had a related patch set (by Paladox) published:
[mediawiki/core@master] Drop index usertext_timestamp on the archive table

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

Change 344460 abandoned by Paladox:
Drop index usertext_timestamp on the archive table

Reason:
I will do it in one patch instead https://gerrit.wikimedia.org/r/#/c/344452/

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

Change 344601 had a related patch set uploaded (by Reedy):
[mediawiki/core@master] Revert "Remove old remapping hacks from Database::indexName()"

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

Change 344602 had a related patch set uploaded (by Reedy):
[mediawiki/core@REL1_28] Revert "Remove old remapping hacks from Database::indexName()"

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

• The revert patch above needs merging
• We need to replace usages of 'usertext_timestamp' in MW code (not many places use it with the archive table) back to ar_usertext_timestamp, so that they work for sqlite, and then get changed back to usertext_timestamp for at least MySQL
  • Are these remappings needed for postgres/mssql etc?
• We need to rename ar_usertext_timestamp back to usertext_timestamp in the mysql patch files, and tables.sql. Writing an updater to change it back, and fix the old updater to stop converting it in the first place. No point doing usertext_timestamp -> ar_usertext_timestamp -> ar_usertext_timestamp in one updater run

Or, we detect which index name it is at runtime, which feels a bit meh...

Change 344601 merged by jenkins-bot:
[mediawiki/core@master] Revert "Remove old remapping hacks from Database::indexName()"

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

In T154872#3142664, @Reedy wrote:

• Are these remappings needed for postgres/mssql etc?

MSSQL has the below, so doesn't actually want remapping either. That needs fixing

CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);

And postgres doesn't seem to have any indexes with any name like usertext_timestamp... So that doesn't actually want remapping either.

So Database::indexName wants moving to DatabaseMysqlBase::indexName, and DatabaseSqlite::indexName wants moving to Database::indexName instead

The indexName function comments are pretty crap too though

Change 344602 merged by jenkins-bot:
[mediawiki/core@REL1_28] Revert "Remove old remapping hacks from Database::indexName()"

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

Change 345494 had a related patch set uploaded (by Tim Starling):
[mediawiki/core@master] Update comment for indexName(), explaining why it exists

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

Maybe we can detect the incorrect schema under 1.28 and apply an SQL update to change the index names back to the old names, then do the index name standardization update in 1.29 or 1.30 as part of T161671.

Change 345533 had a related patch set uploaded (by Reedy):
[mediawiki/core@master] Make the indexName functions more obviously laid out

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

Change 345534 had a related patch set uploaded (by Reedy):
[mediawiki/core@master] Revert archive ar_usertext_timestamp back to usertext_timestamp

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

Change 344452 abandoned by Reedy:
Migrate usertext_timestamp to ar_usertext_timestamp on wiki's lower then 1.28

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

Change 345540 had a related patch set uploaded (by Reedy):
[mediawiki/core@master] Add ar_ prefix back to usertext_timestamp USE INDEX

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

why this affect for all mediawiki versions 1.28. And how to solve it? why this not effect (see http://testwiki.wiki/wiki/Special:DeletedContributions) ?

In T154872#3149062, @Mbrt wrote:

why this affect for all mediawiki versions 1.28. And how to solve it? why this not effect (see http://testwiki.wiki/wiki/Special:DeletedContributions) ?

Because some wikis have the right index, some don't. And with the remapping gone, it breaks for some wikis and not others

Change 345533 merged by jenkins-bot:
[mediawiki/core@master] Make the indexName functions more obviously laid out

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

Change 345494 merged by jenkins-bot:
[mediawiki/core@master] Update comment for indexName(), explaining why it exists

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

Change 346090 had a related patch set uploaded (by Tim Starling):
[mediawiki/core@master] Fix incorrect index name in new 1.28.0 installations

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

@Reedy. Oh, but if I download in mediawiki stable versions 1.28. And I have bug it. But I hope it can be resolve in future :)

In T154872#3150148, @Mbrt wrote:

@Reedy. Oh, but if I download in mediawiki stable versions 1.28. And I have bug it. But I hope it can be resolve in future :)

We're trying to. It's not quite so straight forward of a change. If you scroll back through the changes on this bug, you'll see various patches are being uploaded, and are being worked on :)

Change 344434 abandoned by Reedy:
DeletedContribsPager: Replace index usertext_timestamp with ar_usertext_timestamp

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

Change 345534 abandoned by Reedy:
Revert archive ar_usertext_timestamp back to usertext_timestamp

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

Change 346649 had a related patch set uploaded (by Reedy; owner: Tim Starling):
[mediawiki/core@REL1_28] Fix incorrect index name in new 1.28.0 installations

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

Change 346090 merged by jenkins-bot:
[mediawiki/core@master] Fix incorrect index name in new 1.28.0 installations

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

Change 346649 merged by jenkins-bot:
[mediawiki/core@REL1_28] Fix incorrect index name in new 1.28.0 installations

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

Change 345540 merged by jenkins-bot:
[mediawiki/core@master] Add ar_ prefix back to usertext_timestamp USE INDEX

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