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

Description

Steps to Reproduce

Install MediaWiki in MySQL with no support for MyISAM (eg. Azure Database for MySQL)
Run rebuildtextindex.php
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.

Details

	Subject	Repo	Branch	Lines +/-
	Split sql fulltext index creation into two statements	mediawiki/core	master	+4 -3

Customize query in gerrit

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)

matmarex edited projects, added MediaWiki-Search; removed MediaWiki-General.Oct 5 2017, 4:32 PM

Restricted Application added projects: Discovery-ARCHIVED, Discovery-Search. · View Herald TranscriptOct 5 2017, 4:32 PM

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

Ciencia_Al_Poder added a project: MediaWiki-Maintenance-system.

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__ );
	}

EBernhardson moved this task from Incoming to not in use - please delete on the Discovery-Search (Current work) board.Oct 10 2017, 4:50 PM

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

gerritbot added a project: Patch-For-Review.Oct 10 2017, 4:53 PM

EBernhardson moved this task from not in use - please delete to Needs review on the Discovery-Search (Current work) board.Oct 10 2017, 4:53 PM

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

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

ReleaseTaggerBot added a project: MW-1.31-release-notes (WMF-deploy-2017-10-17 (1.31.0-wmf.4)).Oct 13 2017, 7:00 PM

EBernhardson moved this task from Needs review to Needs Reporting on the Discovery-Search (Current work) board.Oct 13 2017, 8:17 PM

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