Page MenuHomePhabricator

beta simplewiki in a bad state due to production tables being imported
Closed, ResolvedPublic

Description

[14:43:23] <anomie> Timestamps on those revisions are from 20020225154311 to 20120104023906. I wonder, did someone just copy the revision table from production simplewiki over to Beta in 2012 without copying the user table?
[14:43:56] <legoktm> that wouldn't surprise me, as simplewiki was a full copy of the prod wiki
[14:44:45] <Reedy> Hmmm
[14:44:49] <Reedy> Yeah, it sounds vaguely familiar
[14:47:08] <anomie> If that's the case, probably the best fix is to manually update the database to set rev_user = 0 and add an "imported>" prefix to rev_user_text for such revisions. Probably also revisions where there is a user table row but rev_user_text != user_name.
[14:48:12] <anomie> And then the same for archive, logging, image, oldimage, filearchive, ipblocks (ipb_by/ipb_by_text). And in theory recentchanges, although any problems there should have long since expired.

Event Timeline

This should be what we need to do. Review would be good.

CREATE TEMPORARY TABLE temp AS SELECT rev_id FROM revision LEFT JOIN user ON(rev_user = user_id) WHERE rev_user != 0 AND (user_id IS NULL OR rev_user_text != user_name);
UPDATE revision SET rev_user = 0, rev_user_text = CONCAT( 'imported>', rev_user_text ) WHERE rev_id IN (SELECT rev_id FROM temp);
DROP TEMPORARY TABLE temp;
TRUNCATE TABLE revision_actor_temp;

CREATE TEMPORARY TABLE temp AS SELECT ar_id FROM archive LEFT JOIN user ON(ar_user = user_id) WHERE ar_user != 0 AND (user_id IS NULL OR ar_user_text != user_name);
UPDATE archive SET ar_user = 0, ar_user_text = CONCAT( 'imported>', ar_user_text ) WHERE ar_id IN (SELECT ar_id FROM temp);
DROP TEMPORARY TABLE temp;
UPDATE archive SET ar_actor = 0;

The other tables all seem ok: logging has no affected rows; image, oldimage, and filearchive are all empty; and the only rows found in ipblocks seem to be due to a rename of some sort.

Then run maintenance/migrateActors.php on simplewiki. You'll probably have to use --force.

Change 466999 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/WikimediaMaintenance@master] Script to fix simplewiki

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

Running mwscript migrateActors.php --wiki=simplewiki --force now

[6bcb7d7ffa8c9ba4b12b6f08] [no req]   Wikimedia\Rdbms\DBQueryError from line 1496 of /srv/mediawiki-staging/php-master/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: INSERT  INTO `actor` (actor_name) VALUES ('imported>Mycittens'),('imported>Behtis')
Function: MigrateActors::addActorsForRows
Error: 1062 Duplicate entry 'imported>Mycittens' for key 'actor_name' (10.68.23.30:3306)

Backtrace:
#0 /srv/mediawiki-staging/php-master/includes/libs/rdbms/database/Database.php(1466): Wikimedia\Rdbms\Database->makeQueryException(string, integer, string, string)
#1 /srv/mediawiki-staging/php-master/includes/libs/rdbms/database/Database.php(1226): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#2 /srv/mediawiki-staging/php-master/includes/libs/rdbms/database/Database.php(2068): Wikimedia\Rdbms\Database->query(string, string)
#3 /srv/mediawiki-staging/php-master/maintenance/migrateActors.php(171): Wikimedia\Rdbms\Database->insert(string, array, string)
#4 /srv/mediawiki-staging/php-master/maintenance/migrateActors.php(351): MigrateActors->addActorsForRows(Wikimedia\Rdbms\DatabaseMysqli, string, array, array, integer)
#5 /srv/mediawiki-staging/php-master/maintenance/migrateActors.php(82): MigrateActors->migrateToTemp(string, string, array, string, string, string, string)
#6 /srv/mediawiki-staging/php-master/maintenance/Maintenance.php(1698): MigrateActors->doDBUpdates()
#7 /srv/mediawiki-staging/php-master/maintenance/doMaintenance.php(94): LoggedUpdateMaintenance->execute()
#8 /srv/mediawiki-staging/php-master/maintenance/migrateActors.php(548): include(string)
#9 /srv/mediawiki-staging/multiversion/MWScript.php(100): include(string)
#10 {main}

Change 467073 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@master] Use INSERT IGNORE when inserting actors

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

It's working now.. I wonder if something run by update.php has just cleared it up. Or, via update.php it ran to completion, and when I was trying to run this it was while update.php was running it, so some race condition where the row didn't exist at the start

Just one this User name "ɑʀʇʉʀɵ" is usable, cannot create an anonymous actor for it to potentially deal with

reedy@deployment-deploy01:/srv/mediawiki-staging$ mwscript migrateActors.php --wiki=simplewiki --force
Creating actor entries for all registered users
... 1 - 101
... 102 - 202
... 203 - 303
... 304 - 404
... 405 - 505
... 506 - 606
... 607 - 707
... 708 - 808
... 809 - 909
... 910 - 1010
... 1011 - 1111
... 1112 - 1212
... 1213 - 1313
... 1314 - 1414
... 1415 - 1515
... 1516 - 1616
... 1617 - 1717
... 1718 - 1818
... 1819 - 1919
... 1920 - 2020
... 2021 - 2121
... 2122 - 2208
Completed actor creation, added 0 new actor(s)
Beginning migration of revision.rev_user and revision.rev_user_text to revision_actor_temp.revactor_actor
User name "ɑʀʇʉʀɵ" is usable, cannot create an anonymous actor for it. Run maintenance/cleanupUsersWithNoId.php to fix this situation.

... rev_id=2984125
Completed migration, updated 0 row(s) with 0 new actor(s), 1 error(s)
Beginning migration of archive.ar_user and archive.ar_user_text to archive.ar_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of ipblocks.ipb_by and ipblocks.ipb_by_text to ipblocks.ipb_by_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of image.img_user and image.img_user_text to image.img_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of oldimage.oi_user and oldimage.oi_user_text to oldimage.oi_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of filearchive.fa_user and filearchive.fa_user_text to filearchive.fa_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of recentchanges.rc_user and recentchanges.rc_user_text to recentchanges.rc_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of logging.log_user and logging.log_user_text to logging.log_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of log_search
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
reedy@deployment-deploy01:/srv/mediawiki-staging$

Change 467073 abandoned by Reedy:
Use INSERT IGNORE when inserting actors

Reason:
Meh, it's passing now

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

MariaDB [simplewiki]> select * from revision where rev_id=2984125;
+---------+----------+-------------+-------------+----------+---------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+
| rev_id  | rev_page | rev_text_id | rev_comment | rev_user | rev_user_text | rev_timestamp  | rev_minor_edit | rev_deleted | rev_len | rev_parent_id | rev_sha1                        | rev_content_format | rev_content_model |
+---------+----------+-------------+-------------+----------+---------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+
| 2984125 |   126413 |     2984125 |             |        0 | ɑʀʇʉʀɵ        | 20070731210757 |              0 |           0 |     690 |       2984124 | tpeu2yic22ceb6x061k4evimljctvm8 | NULL               | NULL              |
+---------+----------+-------------+-------------+----------+---------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+

That should be fixed by cleanupUsersWithNoId.php.

anomie@deployment-deploy01:/srv/mediawiki-staging/php-master$ mwscript maintenance/cleanupUsersWithNoId.php --wiki=simplewiki --force --prefix=imported --batch-size=10000
Beginning cleanup of revision
[...]
Completed cleanup, assigned 0 and prefixed 1 row(s)
MariaDB [simplewiki]> select * from revision where rev_id=2984125;
+---------+----------+-------------+-------------+----------+-----------------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+
| rev_id  | rev_page | rev_text_id | rev_comment | rev_user | rev_user_text         | rev_timestamp  | rev_minor_edit | rev_deleted | rev_len | rev_parent_id | rev_sha1                        | rev_content_format | rev_content_model |
+---------+----------+-------------+-------------+----------+-----------------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+
| 2984125 |   126413 |     2984125 |             |        0 | imported>ɑʀʇʉʀɵ       | 20070731210757 |              0 |           0 |     690 |       2984124 | tpeu2yic22ceb6x061k4evimljctvm8 | NULL               | NULL              |
+---------+----------+-------------+-------------+----------+-----------------------+----------------+----------------+-------------+---------+---------------+---------------------------------+--------------------+-------------------+
anomie@deployment-deploy01:/srv/mediawiki-staging/php-master$ mwscript migrateActors.php --wiki=simplewiki --force
Creating actor entries for all registered users
... 1 - 101
... 102 - 202
... 203 - 303
... 304 - 404
... 405 - 505
... 506 - 606
... 607 - 707
... 708 - 808
... 809 - 909
... 910 - 1010
... 1011 - 1111
... 1112 - 1212
... 1213 - 1313
... 1314 - 1414
... 1415 - 1515
... 1516 - 1616
... 1617 - 1717
... 1718 - 1818
... 1819 - 1919
... 1920 - 2020
... 2021 - 2121
... 2122 - 2208
Completed actor creation, added 0 new actor(s)
Beginning migration of revision.rev_user and revision.rev_user_text to revision_actor_temp.revactor_actor
... rev_id=2984125
Completed migration, updated 1 row(s) with 1 new actor(s), 0 error(s)
Beginning migration of archive.ar_user and archive.ar_user_text to archive.ar_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of ipblocks.ipb_by and ipblocks.ipb_by_text to ipblocks.ipb_by_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of image.img_user and image.img_user_text to image.img_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of oldimage.oi_user and oldimage.oi_user_text to oldimage.oi_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of filearchive.fa_user and filearchive.fa_user_text to filearchive.fa_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of recentchanges.rc_user and recentchanges.rc_user_text to recentchanges.rc_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of logging.log_user and logging.log_user_text to logging.log_actor
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Beginning migration of log_search
Completed migration, updated 0 row(s) with 0 new actor(s), 0 error(s)
Reedy claimed this task.

Cheers Brad! :)

Change 466999 abandoned by Reedy:
Script to fix simplewiki

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