Page MenuHomePhabricator
Create Task
Maniphest T312708

Cannot update the schema of flaggedtemplates table from 1.37 to 1.38
Closed, ResolvedPublicBUG REPORT
Actions

Description

Steps to replicate the issue:

  • Not certain of the cause. My flaggedtemplates table contains next rows:
ft_rev_idft_namespaceft_titleft_tmp_rev_id
18769140xEB8C80EBACB82FEAB080EC9DB4EB939C0
18769140xEB8C80EBACB82FEC868CEAB09C0
18769140xEB8C80EBACB82FED8E98EBAFB8EC9C84ED82A45FEAB480EBA0A85FEBACB8EC849C0
18769140xEB8C80EBACB82FED9B84EC9B900
18769140xED999CEB8F99ECA081EC9DB85FEC82ACEC9AA9EC9E900
  • Execute update.php for MW 1.37 to 1.38.

What happens?:

Table flaggedtemplates contains ft_title field. Dropping...Wikimedia\Rdbms\DBQueryError from line 1606 of /srv/femiwiki.com/includes/libs/rdbms/database/Database.php: Error 1062: Duplicate entry '187691-0' for key 'flaggedtemplates.PRIMARY' (mysql)
Function: Wikimedia\Rdbms\Database::sourceFile( /srv/femiwiki.com/extensions/FlaggedRevs/backend/schema/mysql/patch-flaggedtemplates-fr_title.sql )
Query: ALTER TABLE `flaggedtemplates`
 DROP PRIMARY KEY,
 ADD PRIMARY KEY (ft_rev_id, ft_tmp_rev_id),
 DROP COLUMN ft_title,
 DROP COLUMN ft_namespace


#0 /srv/femiwiki.com/includes/libs/rdbms/database/Database.php(1590): Wikimedia\Rdbms\Database->getQueryException('Duplicate entry...', 1062, 'ALTER TABLE `fl...', 'Wikimedia\\Rdbms...')
#1 /srv/femiwiki.com/includes/libs/rdbms/database/Database.php(1564): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('Duplicate entry...', 1062, 'ALTER TABLE `fl...', 'Wikimedia\\Rdbms...')
#2 /srv/femiwiki.com/includes/libs/rdbms/database/Database.php(1173): Wikimedia\Rdbms\Database->reportQueryError('Duplicate entry...', 1062, 'ALTER TABLE `fl...', 'Wikimedia\\Rdbms...', false)
#3 /srv/femiwiki.com/includes/libs/rdbms/database/Database.php(4954): Wikimedia\Rdbms\Database->query('ALTER TABLE `fl...', 'Wikimedia\\Rdbms...')
#4 /srv/femiwiki.com/includes/libs/rdbms/database/Database.php(4889): Wikimedia\Rdbms\Database->sourceStream(Resource id #2344, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
#5 /srv/femiwiki.com/includes/libs/rdbms/database/DBConnRef.php(69): Wikimedia\Rdbms\Database->sourceFile('/srv/femiwiki.c...')
#6 /srv/femiwiki.com/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
#7 /srv/femiwiki.com/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile('/srv/femiwiki.c...')
#8 /srv/femiwiki.com/includes/installer/DatabaseUpdater.php(862): DatabaseUpdater->applyPatch('/srv/femiwiki.c...', true, 'Table flaggedte...')
#9 /srv/femiwiki.com/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->dropField('flaggedtemplate...', 'ft_title', '/srv/femiwiki.c...', true)
#10 /srv/femiwiki.com/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates(Array, true)
#11 /srv/femiwiki.com/maintenance/update.php(193): DatabaseUpdater->doUpdates(Array)
#12 /srv/femiwiki.com/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
#13 /srv/femiwiki.com/maintenance/update.php(264): require_once('/srv/femiwiki.c...')
#14 {main}

What should have happened instead?:

The query should be executed.

Software version:

  • FlaggedRevs was firstly installed at MediaWiki 1.34.1 in March 2022 for the wiki.
  • The wiki has been gradually upgraded: 1.34.1 → 1.35.0 → ... → 1.35.2 → 1.36.0 → 1.36.1 → 1.36.2 → 1.37.0 → 1.37.2 → Disabling FlaggedRevs → 1.38.2
  • The database is MySQL v8.

Other information

If it is an edge case and not worth fixing, at least I want to know the manual SQL commands to resolve this problem.

Related Objects

Event Timeline

Lens0021 created this task.Jul 10 2022, 12:50 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 10 2022, 12:50 AM
Lens0021 updated the task description. (Show Details)Jul 10 2022, 1:00 AM
Ladsgroup subscribed.Jul 10 2022, 2:06 PM

I think we had a similar issue in our production, you need to basically clean up rows that have duplicate data. You can also run pruneRevData.php to reduce the size of the table (and chance of corrupt data being there) drastically (run it with --prune --rev-age=31536000 --rev-num=5 as well)

Lens0021 added a comment.Jul 10 2022, 9:31 PM
In T312708#8067886, @Ladsgroup wrote:

I think we had a similar issue in our production, you need to basically clean up rows that have duplicate data. You can also run pruneRevData.php to reduce the size of the table (and chance of corrupt data being there) drastically (run it with --prune --rev-age=31536000 --rev-num=5 as well)

I've tried pruneRevData.php and cleaning up with the next query:

UPDATE flaggedtemplates
  LEFT JOIN page ON
    flaggedtemplates.ft_namespace = page.page_namespace &&
    flaggedtemplates.ft_title = page.page_title
  SET flaggedtemplates.ft_tmp_rev_id = page.page_latest
  WHERE flaggedtemplates.ft_tmp_rev_id = 0;

And it succeeded to execute update.php. Thank you!

Ladsgroup closed this task as Resolved.Jul 10 2022, 10:21 PM
Ladsgroup claimed this task.

I close this then :)

adrelanos reopened this task as Open.Jul 11 2022, 10:09 PM
adrelanos subscribed.

I have the same issue as initially reported here.

I've run that command first:

php /var/www/public/wiki/w/extensions/FlaggedRevs/maintenance/pruneRevData.php --prune --rev-age=31536000 --rev-num=5

Wasn't enough. So did run this mysql statement too.

MariaDB [(none)]> use wiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [wiki]> UPDATE flaggedtemplates
    ->   LEFT JOIN page ON
    ->     flaggedtemplates.ft_namespace = page.page_namespace &&
    ->     flaggedtemplates.ft_title = page.page_title
    ->   SET flaggedtemplates.ft_tmp_rev_id = page.page_latest
    ->   WHERE flaggedtemplates.ft_tmp_rev_id = 0;
Query OK, 1272 rows affected (0.066 sec)
Rows matched: 1272  Changed: 1272  Warnings: 0

MariaDB [wiki]> exit

Issue still not resolved.

php /var/www/public/wiki/w/maintenance/update.php still failing:

...index fr_user already set on flaggedrevs table.
Table flaggedtemplates contains ft_title field. Dropping...Wikimedia\Rdbms\DBQueryError from line 1606 of /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php: Error 1062: Duplicate entry '42748-0' for key 'PRIMARY' (127.0.0.1)
Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/public/wiki/w/extensions/FlaggedRevs/backend/schema/mysql/patch-flaggedtemplates-fr_title.sql )
Query: ALTER TABLE `flaggedtemplates`
 DROP PRIMARY KEY,
 ADD PRIMARY KEY (ft_rev_id, ft_tmp_rev_id),
 DROP COLUMN ft_title,
 DROP COLUMN ft_namespace


#0 /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php(1590): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php(1564): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php(1173): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php(4954): Wikimedia\Rdbms\Database->query()
#4 /var/www/public/wiki/w/includes/libs/rdbms/database/Database.php(4889): Wikimedia\Rdbms\Database->sourceStream()
#5 /var/www/public/wiki/w/includes/libs/rdbms/database/DBConnRef.php(69): Wikimedia\Rdbms\Database->sourceFile()
#6 /var/www/public/wiki/w/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call()
#7 /var/www/public/wiki/w/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile()
#8 /var/www/public/wiki/w/includes/installer/DatabaseUpdater.php(862): DatabaseUpdater->applyPatch()
#9 /var/www/public/wiki/w/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->dropField()
#10 /var/www/public/wiki/w/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates()
#11 /var/www/public/wiki/w/maintenance/update.php(193): DatabaseUpdater->doUpdates()
#12 /var/www/public/wiki/w/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
#13 /var/www/public/wiki/w/maintenance/update.php(264): require_once('/var/www/public...')
#14 {main}

How can I fix this?

Ladsgroup added a comment.Jul 12 2022, 12:37 PM

The fix is to remove all of the corrupted data, you need to query what has duplicate entries for ft_rev_id and ft_tmp_rev_id and remove the extra lines via a db query.

adrelanos added a comment.Jul 13 2022, 8:48 AM

I don't have any mysql database modifications skills. I can go to the mysql shell and USE wiki but I wouldn't know what commands to run to query for duplicate entries or to delete them.

Could you please kindly provide the commands required to fix this?

adrelanos added a comment.Jul 18 2022, 4:38 PM

I managed to delete all the flaggedrevs related wiki tables.

show tables like '%flagged%';

+----------------------------+

Tables_in_wiki (%flagged%)

+----------------------------+

flaggedpage_config
flaggedpage_pending
flaggedpages
flaggedrevs
flaggedrevs_promote
flaggedrevs_statistics
flaggedrevs_tracking
flaggedtemplates

+----------------------------+

Warning: This deletes all saves revisions. Accepting revisions has to be re-done for all pages.

use wiki;
DROP TABLE flaggedpage_config;
DROP TABLE flaggedpage_pending;
DROP TABLE flaggedpages;
DROP TABLE flaggedrevs;
DROP TABLE flaggedrevs_promote;
DROP TABLE flaggedrevs_statistics;
DROP TABLE flaggedrevs_tracking;
DROP TABLE flaggedtemplates;

(Re-accepting all revisions I'll script with mediawiki-shell.)

Would be nice if this could be fixed in the extension so this manual magic isn't required as this is very difficult if not speaking mysql.

Ladsgroup closed this task as Resolved.Jul 25 2022, 3:18 PM

I have told you about issues of FR before. It is highly recommend not to use this extension unless you have deep understanding of it: https://phabricator.wikimedia.org/T276782#7529378

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL