Page MenuHomePhabricator

rebuildtextindex fails when searchindex is on InnoDB (no support for MyISAM)
Closed, ResolvedPublic

Description

Steps to Reproduce

  1. Install MediaWiki in MySQL with no support for MyISAM (eg. Azure Database for MySQL)
  2. Run rebuildtextindex.php
  3. Try to search for a page that do not exists

Actual Results

php rebuildtextindex.php
Clearing searchindex table...Done
Rebuilding index fields for 8361 pages...
500
1000
1500
2000
2500
3000
3500
4000
4500
5000
5500
6000
6500
7000
7500
8000
Rebuild the index...
[336220fa36353baa1133c605] [no req] Wikimedia\Rdbms\DBQueryError from line 1075 of /var/www/html/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: ALTER TABLE mw_searchindex ADD FULLTEXT si_title (si_title), ADD FULLTEXT si_text (si_text)
Function: RebuildTextIndex::createMysqlTextIndex
Error: 1795 InnoDB presently supports one FULLTEXT index creation at a time
Backtrace:
#0 /var/www/html/includes/libs/rdbms/database/Database.php(933): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#1 /var/www/html/maintenance/rebuildtextindex.php(150): Wikimedia\Rdbms\Database->query(string, string)
#2 /var/www/html/maintenance/rebuildtextindex.php(77): RebuildTextIndex->createMysqlTextIndex()
#3 /var/www/html/maintenance/doMaintenance.php(111): RebuildTextIndex->execute()
#4 /var/www/html/maintenance/rebuildtextindex.php(164): require_once(string)
#5 {main}

When I try to search for a page that do not exists, I get following error message:

[8d97b28fd3d3afeccab16c37] /w/index.php?search=foo&title=Special%3AS%C3%B6k&go=G%C3%A5+till Wikimedia\Rdbms\DBQueryError from line 1075 of /var/www/html/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: SELECT page_id,page_namespace,page_title FROM mw_page,mw_searchindex WHERE (page_id=si_page) AND ( MATCH(si_title) AGAINST('+foou800 ' IN BOOLEAN MODE) ) AND page_namespace = '0' LIMIT 20
Function: SearchMySQL::searchInternal
Error: 1191 Can't find FULLTEXT index matching the column list
Backtrace:
#0 /var/www/html/includes/libs/rdbms/database/Database.php(933): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#1 /var/www/html/includes/libs/rdbms/database/Database.php(1269): Wikimedia\Rdbms\Database->query(string, string)
#2 /var/www/html/includes/search/SearchMySQL.php(191): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#3 /var/www/html/includes/search/SearchMySQL.php(178): SearchMySQL->searchInternal(string, boolean)
#4 /var/www/html/includes/specials/SpecialSearch.php(318): SearchMySQL->searchTitle(string)
#5 /var/www/html/includes/specials/SpecialSearch.php(185): SpecialSearch->showResults(string)
#6 /var/www/html/includes/specialpage/SpecialPage.php(522): SpecialSearch->execute(NULL)
#7 /var/www/html/includes/specialpage/SpecialPageFactory.php(578): SpecialPage->run(NULL)
#8 /var/www/html/includes/MediaWiki.php(287): SpecialPageFactory::executePath(Title, RequestContext)
#9 /var/www/html/includes/MediaWiki.php(862): MediaWiki->performRequest()
#10 /var/www/html/includes/MediaWiki.php(523): MediaWiki->main()
#11 /var/www/html/index.php(43): MediaWiki->run()
#12 {main}

Expected Results

The index is rebuild with no errors, and the search continue to work.

Description
As the error message say ("InnoDB presently supports one FULLTEXT index creation at a time"), MySql do not support adding more then one FULLTEXT index in each statement when using InnoDB.
If I try to add the FULLTIME index one by one, the search start to works again:

CREATE FULLTEXT INDEX si_title ON mw_searchindex (si_title);
CREATE FULLTEXT INDEX si_text ON mw_searchindex (si_text);

But if I re-run rebuildtextindex.php, the issue returns and I have to add the FULLTEXT INDEX again.

So to fix this bug, make the script run the SQL statements one by one when the table is created with InnoDB.

Event Timeline

Magol created this task.Oct 5 2017, 11:15 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 5 2017, 11:15 AM
Magol updated the task description. (Show Details)Oct 5 2017, 11:20 AM
Magol updated the task description. (Show Details)
Restricted Application added projects: Discovery, Discovery-Search. · View Herald TranscriptOct 5 2017, 4:32 PM
debt assigned this task to EBernhardson.Oct 5 2017, 5:15 PM
debt triaged this task as Low priority.
debt edited projects, added Discovery-Search (Current work); removed Discovery-Search.
debt added a subscriber: debt.

Are you wanting to run the SQL statements one by one? We'll look at adding a patch to do this.

Ciencia_Al_Poder renamed this task from rebuildtextindex fails when no support for MyISAM to rebuildtextindex fails when searchindex is on InnoDB (no support for MyISAM).Oct 5 2017, 8:41 PM
Magol added a comment.EditedOct 6 2017, 3:45 PM

When I changes rebuildtextindex.php to this, it works for me (but I haven't test for other environments)

	/**
	 * (MySQL only) Adds back fulltext index after populating the table.
	 */
	private function createMysqlTextIndex() {
		$searchindex = $this->db->tableName( 'searchindex' );
		$this->output( "\nRebuild the index...\n" );
		/*$sql = "ALTER TABLE $searchindex ADD FULLTEXT si_title (si_title), " .
			"ADD FULLTEXT si_text (si_text)";*/
		$sql = "CREATE FULLTEXT INDEX si_title ON $searchindex (si_title);";
		$this->db->query( $sql, __METHOD__ );
		$sql = "CREATE FULLTEXT INDEX si_text  ON $searchindex (si_text);";
		$this->db->query( $sql, __METHOD__ );
	}

Change 383379 had a related patch set uploaded (by EBernhardson; owner: EBernhardson):
[mediawiki/core@master] Split sql fulltext index creation into two statements

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

Change 383379 merged by jenkins-bot:
[mediawiki/core@master] Split sql fulltext index creation into two statements

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

debt closed this task as Resolved.Oct 19 2017, 8:02 PM