Thanks for reporting this. Which database backend is being used?

/home/ebullisc-virgile/wiki/w/maintenance/archives/patch-revision-actor-comment-MCR.sql

Hmm, that file has not been changed for three years...

MySQL 5.7.41

Is there any way to fix it manually?
I tried to execute
ALTER TABLE wiki_revision ADD COLUMN user_timestamp INT;
in phpmyadmin, but it does not work.

Also brought up in https://www.mediawiki.org/wiki/Topic:Xfxb75vk23nmrosd

The bug is still present when I try to update to 1.39.4. Is there any manual intervention which could fix the database ?

In T331608#8728086, @Crashone wrote:

Is there any way to fix it manually?
I tried to execute
ALTER TABLE wiki_revision ADD COLUMN user_timestamp INT;
in phpmyadmin, but it does not work.

This is not about a column, it is about a index named user_timestamp.
To process in such a case the table needs a manual check if the index is missing or just has another name (for example a prefix for the index).
When the index and the other index does not exists, it is valid to remove the line from the patch and retry. If the index still exists the drop of some column could fail as the column is still used in a index.
No idea how to find which update step was missing here in the past to end without that index.

I get this issue when trying to upgrad a MediaWiki 1.34 to 1.39 with "update.php". That wiki was initially installed using MW 1.34 and was never upgraded.

10.3.38-MariaDB-0ubuntu0.20.04.1
7.4.3-4ubuntu2.19

No table prefix is being used.

...index ipb_address_unique already set on ipblocks table.
Table revision contains rev_text_id field. Dropping...Wikimedia\Rdbms\DBQueryError from line 1618 of /../w/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP INDEX `user_timestamp`; check that it exists
Function: Wikimedia\Rdbms\Database::sourceFile( /../w/maintenance/archives/patch-revision-actor-comment-MCR.sql )
Query: ALTER TABLE `revision`
 DROP INDEX user_timestamp,
 DROP INDEX page_user_timestamp,
 DROP INDEX usertext_timestamp,
 DROP COLUMN rev_text_id,
 DROP COLUMN rev_comment,
 DROP COLUMN rev_user,
 DROP COLUMN rev_user_text,
 DROP COLUMN rev_content_model,
 DROP COLUMN rev_content_format

Backtrace

#0 /../w/includes/libs/rdbms/database/Database.php(1602): Wikimedia\Rdbms\Database->getQueryException()
#1 /../w/includes/libs/rdbms/database/Database.php(1576): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /../w/includes/libs/rdbms/database/Database.php(952): Wikimedia\Rdbms\Database->reportQueryError()
#3 /../w/includes/libs/rdbms/database/Database.php(3302): Wikimedia\Rdbms\Database->query()
#4 /../w/includes/libs/rdbms/database/Database.php(3241): Wikimedia\Rdbms\Database->sourceStream()
#5 /../w/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->sourceFile()
#6 /../w/includes/libs/rdbms/database/DBConnRef.php(807): Wikimedia\Rdbms\DBConnRef->__call()
#7 /../w/includes/installer/DatabaseUpdater.php(718): Wikimedia\Rdbms\DBConnRef->sourceFile()
#8 /../w/includes/installer/DatabaseUpdater.php(843): DatabaseUpdater->applyPatch()
#9 /../w/includes/installer/DatabaseUpdater.php(547): DatabaseUpdater->dropField()
#10 /../w/includes/installer/DatabaseUpdater.php(511): DatabaseUpdater->runUpdates()
#11 /../w/maintenance/update.php(209): DatabaseUpdater->doUpdates()
#12 /../w/maintenance/includes/MaintenanceRunner.php(309): UpdateMediaWiki->execute()
#13 /../w/maintenance/doMaintenance.php(85): MediaWiki\Maintenance\MaintenanceRunner->run()
#14 /../w/maintenance/update.php(319): require_once('/var/www/html/m...')
#15 {main}

Everything in the "patch-revision-actor-comment-MCR.sql" file fails. I find this irritating.

Trying to upgrade from MW 1.35 and then moving to MW 1.39 also fails. It looks like the actor migration issue saga continues.

So, let's ask this question: What happens if I skip this step? So far, the wiki appears to work without issues. However, I am not a database wizard to be able to assess the impact fully.

I checked the 1.34.0 tag.

Its maintenance/tables.sql (our old style definitions) has:

-- Logged-in user contributions index
CREATE INDEX /*i*/user_timestamp ON /*_*/revision (rev_user,rev_timestamp);

This means a normal mysql-based install SHOULD have had this index at install time.

1.39.6 (which I'm assuming you tried to update to)

ALTER TABLE /*_*/revision
	DROP INDEX /*i*/user_timestamp,
	DROP INDEX /*i*/page_user_timestamp,
	DROP INDEX /*i*/usertext_timestamp,
	DROP COLUMN rev_text_id,
	DROP COLUMN rev_comment,
	DROP COLUMN rev_user,
	DROP COLUMN rev_user_text,
	DROP COLUMN rev_content_model,
	DROP COLUMN rev_content_format;

And I can't find other references. HOWEVER. If this script for some reason is run TWICE and it, or something failed the FIRST time, then this is the error (Can't DROP INDEX user_timestamp; check that it exists) you would expect, if the update were to run a SECOND time !

The execution check for this script is on rev_text_id, so only if that column is still present, it will run this script. But if those indexes were already dropped, then it will fail on the first line when run a second time..

So can you check if you still have a rev_text_id column on the revision table ?

What happens if I skip this step?

As far as I can tell in 1.39 this script only does cleanup of old data in the tables.

What is interesting however is that newer versions of mediawiki have changes in this migration script.. That is... uncommon, as this script was for the 1.35 update.

If you access the database, and run SHOW INDEX FROM revision, what does it show for that ?

Thanks a lot for your comment. Let's see what we have.

For the wiki now on MW 1.39:

MariaDB [wikidb]> SHOW INDEX FROM revision;
+----------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                 | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| revision |          0 | PRIMARY                  |            1 | rev_id        | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_timestamp            |            1 | rev_timestamp | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_actor_timestamp      |            1 | rev_actor     | A         |          64 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_actor_timestamp      |            2 | rev_timestamp | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_actor_timestamp      |            3 | rev_id        | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_page_actor_timestamp |            1 | rev_page      | A         |        2704 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_page_actor_timestamp |            2 | rev_actor     | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_page_actor_timestamp |            3 | rev_timestamp | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_page_timestamp       |            1 | rev_page      | A         |        2704 |     NULL | NULL   |      | BTREE      |         |               |
| revision |          1 | rev_page_timestamp       |            2 | rev_timestamp | A         |        5409 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+--------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.000 sec)

It looks like the intended modifications from "patch-revision-actor-comment-MCR.sql" were successful. I may be wrong, though.

This is after the second attempt. Using the original 1.34 database backup again this time, I did not try to update from 1.34 to 1.39 directly but did 1.34 --> 1.35 --> 1.39. Still, "update.php" failed for the update from 1.35 to 1.39. If I am right and the table looks as intended, it may be an issue about the file failing to detect that all of the work was done.

Regardless, this would also mean that the wiki is cool, and users can confidently continue editing?!

I created a secret Gist with two files, which holds what "update.php" printed for both runs. It also includes my action commenting the patch file.

@Kghbln ok. and DESCRIBE revision matches the 1.39 state of https://www.mediawiki.org/wiki/Manual:Revision_table#Schema_summary ?

Or does it still include rev_text_id ? Cause if it doesn't, then I don't understand why it is even running that migration...

It seems that the 1.39 branch is slightly special. It had some refactoring done that is not fully aligned with other branches it seems. The work was done in https://gerrit.wikimedia.org/r/c/mediawiki/core/+/874878

as part of T215466 and T326071, by @Func

Possibly this causes a problem for people coming from 1.34 version, even if you have gone through 1.35 ?

In T331608#9534092, @TheDJ wrote:

@Kghbln ok. and DESCRIBE revision matches the 1.39 state of https://www.mediawiki.org/wiki/Manual:Revision_table#Schema_summary ?

Or does it still include rev_text_id ? Cause if it doesn't, then I don't understand why it is even running that migration...

MariaDB [wikidb]> DESCRIBE revision;
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| rev_id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| rev_page           | int(10) unsigned    | NO   | MUL | NULL    |                |
| rev_comment_id     | bigint(20) unsigned | NO   |     | 0       |                |
| rev_actor          | bigint(20) unsigned | NO   | MUL | 0       |                |
| rev_timestamp      | binary(14)          | NO   | MUL | NULL    |                |
| rev_minor_edit     | tinyint(3) unsigned | NO   |     | 0       |                |
| rev_deleted        | tinyint(3) unsigned | NO   |     | 0       |                |
| rev_len            | int(10) unsigned    | YES  |     | NULL    |                |
| rev_parent_id      | int(10) unsigned    | YES  |     | NULL    |                |
| rev_sha1           | varbinary(32)       | NO   |     |         |                |
| rev_text_id        | int(10) unsigned    | NO   |     | 0       |                |
| rev_content_format | varbinary(64)       | YES  |     | NULL    |                |
| rev_content_model  | varbinary(32)       | YES  |     | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+
13 rows in set (0.001 sec)

This means the table still contains rev_text_id, rev_content_format, and rev_content_mondel even though they should not.

Can the wiki still safely be used with these three fields present, or should I somehow drop them? If yes, how? I am still unsure if the wiki is unstable or not.

In T331608#9534126, @TheDJ wrote:

It seems that the 1.39 branch is slightly special. It had some refactoring done that is not fully aligned with other branches it seems. The work was done in https://gerrit.wikimedia.org/r/c/mediawiki/core/+/874878

as part of T215466 and T326071, by @Func

Possibly this causes a problem for people coming from 1.34 version, even if you have gone through 1.35 ?

Bringing in @Zabe, too, since the user was also involved in that change. I guess the more eyes on database issues the better. Anyhow, it would be very cool if this issue here could be resolved for people potentially confronted with it. This will be much appreciated. :)

In T331608#9525699, @Kghbln wrote:

This is after the second attempt. Using the original 1.34 database backup again this time, I did not try to update from 1.34 to 1.39 directly but did 1.34 --> 1.35 --> 1.39. Still, "update.php" failed for the update from 1.35 to 1.39. If I am right and the table looks as intended, it may be an issue about the file failing to detect that all of the work was done.

Regardless, this would also mean that the wiki is cool, and users can confidently continue editing?!

I created a secret Gist with two files, which holds what "update.php" printed for both runs. It also includes my action commenting the patch file.

This line from the 1.34 to 1.35 update log said that your database for 1.34 is not in the original state, the rev_actor field is only created since 1.35 (otherwise the 1.35 updater would have dropped both the affecting indexes and fields for us, and nothing left to do for the 1.39 updater).

...have rev_actor field in revision table.

I can not reproduce the bug with a clean 1.34.4->1.35.14->1.39.6 install and update path, but given that we have multiple cases reported, we may change DROP INDEX to DROP INDEX IF EXISTS to unblock the update for now.

In T331608#9534356, @Kghbln wrote:

This means the table still contains rev_text_id, rev_content_format, and rev_content_mondel even though they should not.

Eh, I thought I knew why your database only has these 3 fields left behind without other fields and indexes. They are re-added by the 1.34 updater which didn't check for the condition for adding them when you run it after reverting from 1.35 (which you shouldn't run), after they were dropped altogether with the indexes by the 1.35 updater.

This makes me recall a wiki where I am providing tech support, the owner upgraded the site after the 1.35 release, but reverted to 1.34 because of many complaints from the community. I think reverting to an older version and running its updater at least once happened for all the reported cases.

I will check back and ask if somebody tried to update this wiki created with MW 1.34. I do not understand why a MW 1.34 should work with a MW 1.35 schema in this situation. If it does, well ... If not, I would assume that the old backup with the MW 1.34 schema was used again, which does not explain why MW 1.35 schema changes should be in there. I will ask what they did to have background information about it.

Another question I already asked twice or thrice: Is this wiki, now on MW 1.39, unstable if it has these three extra fields? Can this wiki be used, or is this situation detrimental?

In T331608#9535285, @Kghbln wrote:

Another question I already asked twice or thrice: Is this wiki, now on MW 1.39, unstable if it has these three extra fields? Can this wiki be used, or is this situation detrimental?

Yes, the wiki should be safe to use. These fields should be almost empty and no code would ever try to use them during normal operation in 1.39. But you may want to fix the schema drift by dropping them before further updating, in case some more or less hacky maintenance scripts are checking their existence to determine something (e.g. implicit marker for determining the previous version in update.php).

Thanks a lot for this information. I will get back here once I have an answer about the past of that wiki, which could further help in identifying the cause. This should bring some clarity.

Change 1002419 had a related patch set uploaded (by Func; author: Func):

[mediawiki/core@REL1_39] installer: Bear with schema drift caused by running old updater

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

For documentation purposes and others that run into the issu. I just dropped the three fields with:

ALTER TABLE revision
    DROP COLUMN rev_text_id,
    DROP COLUMN rev_content_model,
    DROP COLUMN rev_content_format;

After that DESCRIBE revision;gives the expected

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| rev_id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| rev_page       | int(10) unsigned    | NO   | MUL | NULL    |                |
| rev_comment_id | bigint(20) unsigned | NO   |     | 0       |                |
| rev_actor      | bigint(20) unsigned | NO   | MUL | 0       |                |
| rev_timestamp  | binary(14)          | NO   | MUL | NULL    |                |
| rev_minor_edit | tinyint(3) unsigned | NO   |     | 0       |                |
| rev_deleted    | tinyint(3) unsigned | NO   |     | 0       |                |
| rev_len        | int(10) unsigned    | YES  |     | NULL    |                |
| rev_parent_id  | int(10) unsigned    | YES  |     | NULL    |                |
| rev_sha1       | varbinary(32)       | NO   |     |         |                |
+----------------+---------------------+------+-----+---------+----------------+

In T331608#9535470, @Kghbln wrote:

Thanks a lot for this information. I will get back here once I have an answer about the past of that wiki, which could further help identify the cause. This should bring some clarity.

I got feedback in the meantime. The wiki was originally installed with MW 1.32 and was upgraded straight to MW 1.34. There was a MariaDB upgrade from 10.3.14 to 11.1.2, while the wiki was on version MW 1.34. This is all that was done.

Change #1002419 merged by jenkins-bot:

[mediawiki/core@REL1_39] installer: Bear with schema drift caused by running old updater

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

Thanks a bunch for the patch! I also think it should be forward-ported from REL1_40 to master.

@Ladsgroup We are talking about issues with MW as young as 1.32 which is not a really old version.

1.32 is 5 years old, which is pretty old...

Change #1015052 had a related patch set uploaded (by Reedy; author: Func):

[mediawiki/core@REL1_40] installer: Bear with schema drift caused by running old updater

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

In T331608#9666361, @Reedy wrote:

1.32 is 5 years old, which is pretty old...

On top, we have the policy not to support upgrades from older versions than two LTS releases: T259771: RFC: Drop support for older database upgrades

In T331608#9666353, @Kghbln wrote:

Thanks a bunch for the patch! I also think it should be forward-ported from REL1_40 to master.

This gets harder/more complicated. MW-1.39-release had rMW8280ca956344: installer: Split drop action out of the SQL patch for actor migration for T215466: Remove revision_comment_temp and revision_actor_temp, and T326071: Upgrade from 1.31.2 to 1.39 results in pages no longer being usable with "The revision #0 of the page named "x" does not exist." error. So the code differs from what is in other branches anyway...

Change #1015052 abandoned by Reedy:

[mediawiki/core@REL1_40] installer: Bear with schema drift caused by running old updater

Reason:

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

1.32 is 5 years old, which is pretty old...

I have no general objection. But when looking at what is out in the wild, I am still regularly confronted with 1.2x, including early versions here, and with a pile of 1.31, which completely failed to upgrade due to the epical MCR upgrade disaster. Anyhow, forward-ports will be nice if possible. :)

On top, we have the policy not to support upgrades from older versions than two LTS releases: T259771: RFC: Drop support for older database upgrades

I am fully aware. 1.32 is just a little behind. :)

In T331608#9666379, @Reedy wrote:

In T331608#9666353, @Kghbln wrote:

Thanks a bunch for the patch! I also think it should be forward-ported from REL1_40 to master.

This gets harder/more complicated. MW-1.39-release had rMW8280ca956344: installer: Split drop action out of the SQL patch for actor migration for T215466: Remove revision_comment_temp and revision_actor_temp, and T326071: Upgrade from 1.31.2 to 1.39 results in pages no longer being usable with "The revision #0 of the page named "x" does not exist." error. So the code differs from what is in other branches anyway...

Well, in this case, 1.39 needs to be enough. If one does an intermediate step via 1.35 -> 1.39, things should be fine.

In T331608#9666434, @Kghbln wrote:

1.32 is 5 years old, which is pretty old...

I have no general objection. But when looking at what is out in the wild, I am still regularly confronted with 1.2x, including early versions here, and with a pile of 1.31, which completely failed to upgrade due to the epical MCR upgrade disaster. Anyhow, forward-ports will be nice if possible. :)

According to that policy, you have to first upgrade to 1.35/1.39 and then to a recent version. Otherwise, you're going to miss on important schema changes and corrupt your databases. (because we remove old schema updates altogether).

In T331608#9666662, @Ladsgroup wrote:

In T331608#9666434, @Kghbln wrote:

1.32 is 5 years old, which is pretty old...

I have no general objection. But when looking at what is out in the wild, I am still regularly confronted with 1.2x, including early versions here, and with a pile of 1.31, which completely failed to upgrade due to the epical MCR upgrade disaster. Anyhow, forward-ports will be nice if possible. :)

According to that policy, you have to first upgrade to 1.35/1.39 and then to a recent version. Otherwise, you're going to miss on important schema changes and corrupt your databases. (because we remove old schema updates altogether).

This is what I am always doing. I did not mean to insult your policy.