Page MenuHomePhabricator

Special:DeletedContributions query error on wikis created after Mediawiki 1.28 upgrade
Closed, ResolvedPublic

Description

When trying to access anything on Special:DeletedContributions. @Ciencia_Al_Poder confirms that this error is happening on a local 1.28 wiki too. This seems to be caused by a database index problem.

Logs:
[d1c95e99d39f7fbf7e9ca074] 2017-01-08 02:36:12: Fatal exception of type "DBQueryError"
mw2 elementswiki IndexPager::buildQueryIn fo (DeletedContribsPager) 1176 Key 'usertext_timestamp' doesn't exist in table 'archive' (-) SELECT ar_rev_id,ar_nam espace,ar_title,ar_timestamp,ar_comment,ar_minor_edit,ar_user,ar_user_text,ar_de leted FROM archive FORCE INDEX (usertext_timestamp) WHERE ar_user_text = 'D eltaQuad' AND ((ar_deleted & 12) != 12) ORDER BY ar_timestamp DESC LIMIT 51

Related patches:
https://phabricator.wikimedia.org/diffusion/MW/browse/master/maintenance/archives/patch-archive-user-index.sql
https://phabricator.wikimedia.org/diffusion/MW/browse/master/maintenance/tables.sql;d6423137639590a19580198429d7845388943e39$376

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJan 8 2017, 3:56 PM
Reception123 triaged this task as High priority.Jan 8 2017, 3:56 PM

I don't know if the priority is right, but this affects any 1.28 wiki.

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?

Amanda added a subscriber: Amanda.Jan 8 2017, 5:40 PM

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.

Mbrt added a subscriber: Mbrt.Mar 6 2017, 1:30 AM

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.

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

Paladox added a comment.EditedMar 23 2017, 5:47 PM

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

@Paladox Confirm it fixes the issue.

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

Reedy added a comment.Mar 30 2017, 1:57 AM
  • 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

Reedy added a comment.Mar 30 2017, 2:43 AM
  • 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

Mbrt added a comment.Apr 2 2017, 2:55 PM

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) ?

Reedy added a comment.Apr 2 2017, 3:02 PM

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

Mbrt added a comment.Apr 3 2017, 10:15 AM

@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 :)

Reedy added a comment.Apr 3 2017, 11:58 AM

@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

Reedy closed this task as Resolved.Apr 5 2017, 11:52 PM
Reedy claimed this task.

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

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