Page MenuHomePhabricator

Running updateSearchIndex.php gives Error 1100: Table 'temp_rev_comment' was not locked with LOCK TABLES
Closed, DuplicatePublic

Description

After upgrading from MW1.29 to MW1.35, I performed:

  1. php maintenance/update.php (no issues)
  2. php maintenance/updateSearchIndex.php (gives issue as shown below)

Due to this issue, the search functionality in MW is crippled. Apart from the search functionality the wiki apparently works fine. Search only returns results on some keywords. Especially text that existed before the update seems still searchable, while text that was entered after the MW update does not seem to be searched. But even that seems not consistent.

Updating searchindex between 20201206054433 and 20201207054433
   --- Waiting for lock ---
Wikimedia\Rdbms\DBQueryError from line 1699 of /var/www/wiki/public/includes/libs/rdbms/database/Database.php: Error 1100: Table 'temp_rev_comment' was not locked with LOCK TABLES (localhost)
Function: MediaWiki\Revision\RevisionStore::fetchRevisionRowFromConds
Query: SELECT  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name  FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user))   WHERE page_id = 524 AND (rev_id=page_latest)  LIMIT 1 

#0 /var/www/wiki/public/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/wiki/public/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/wiki/public/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/wiki/public/includes/libs/rdbms/database/Database.php(1907): Wikimedia\Rdbms\Database->query()
#4 /var/www/wiki/public/includes/libs/rdbms/database/Database.php(2007): Wikimedia\Rdbms\Database->select()
#5 /var/www/wiki/public/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->selectRow()
#6 /var/www/wiki/public/includes/libs/rdbms/database/DBConnRef.php(331): Wikimedia\Rdbms\DBConnRef->__call()
#7 /var/www/wiki/public/includes/Revision/RevisionStore.php(2373): Wikimedia\Rdbms\DBConnRef->selectRow()
#8 /var/www/wiki/public/includes/Revision/RevisionStore.php(2315): MediaWiki\Revision\RevisionStore->fetchRevisionRowFromConds()
#9 /var/www/wiki/public/includes/Revision/RevisionStore.php(1146): MediaWiki\Revision\RevisionStore->loadRevisionFromConds()
#10 /var/www/wiki/public/maintenance/includes/Maintenance.php(1519): MediaWiki\Revision\RevisionStore->getRevisionByPageId()
#11 /var/www/wiki/public/maintenance/updateSearchIndex.php(121): Maintenance->updateSearchIndexForPage()
#12 /var/www/wiki/public/maintenance/includes/Maintenance.php(1499): UpdateSearchIndex->searchIndexUpdateCallback()
#13 /var/www/wiki/public/maintenance/updateSearchIndex.php(115): Maintenance->updateSearchIndex()
#14 /var/www/wiki/public/maintenance/updateSearchIndex.php(81): UpdateSearchIndex->doUpdateSearchIndex()
#15 /var/www/wiki/public/maintenance/doMaintenance.php(107): UpdateSearchIndex->execute()
#16 /var/www/wiki/public/maintenance/updateSearchIndex.php(126): require_once('/var/www/wiki/p...')
#17 {main}

Installed software (Ubuntu 20.04):
Product Version
MediaWiki 1.35.0
PHP 7.4.13 (fpm-fcgi)
MariaDB 10.3.25-MariaDB-0ubuntu0.20.04.1

Event Timeline

Hi, does your user account have the LOCK TABLES and SELECT privilege?
Anyway, this sounds like T252853: updateSearchIndex.php sql error not all tables locked so I'm going to merge this task into the older one.

Another small update.
The results of two experiments:

  1. Upgrade from MW 1.29 to MW 1.31. Database upgrade without problems. php maintenance/updateSearchIndex.php runs without stack trace.
  2. Upgrade from MW 1.31 to MW 1.35. Database upgrade shows errors (see below). php maintenance/updateSearchIndex.php gives stack trace as reported above (with temp_rev_comment).

...
User name "MusikBot II" is usable, cannot create an anonymous actor for it. Run maintenance/cleanupUsersWithNoId.php to fix this situation.

User name "Ahechtbot" is usable, cannot create an anonymous actor for it. Run maintenance/cleanupUsersWithNoId.php to fix this situation.

... rev_id=801
... rev_id=825
Completed migration, updated 526 row(s) with 3 new actor(s), 298 error(s)

P.S. I'll stick with MW1.31 for the time being, as MW1.35 seems to contain breaking changes.