Topic on Project:Support desk

Produkt	Version
MediaWiki	1.29.0
PHP	7.1.8 (apache2handler)
MySQL	5.7.18-log
ICU	52.1
Lua	5.1.5

When I try to search for an article that do not exist, 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}

I have test to run the update script suggested by the error message, but it do not solve it.

I think the the problem started when I did use Special:Import to import some templates from en.wikipedia.org, and accidentally closed the web browser before it was ready. I do not know if this has anything to do with the problem. But after I did that, the wiki became much slower. :-(

You may try to run rebuildtextindex.php

When I try to run rebuildtextindex.php, I got following error:

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}

Are you using the right table engine for the mw_searchindex table? Currently, this table has to use the MyISAM table engine.

However, I think that MySQL 5.6 and newer (you said you are using 5.7) would support FULLTEXT indeces. So it would be interesting to know, why the queries are failing... Maybe this should be fixed as a bug?!

I have recently migrated to "Azure Database for MySQL" and now I remember that I hade to change to InnoDB to get it to work. Apparently, that was no good decision. What alternatives do I have now?

I found the error. When I did the migration, I forgot the FULLTEXT INDEX.

So when I did run following SQL, the problem was solved. What is strange is, however, that it actually worked a month before the problem began.

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

When I did run rebuildtextindex.php, the problem returns. I had to again run the SQL statements to make it work.

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. So to fix this bug, make the script run the SQL statements one row at a time when the table is created with InnoDB.

Is it any bug report on this issue?

Apparently not, feel free to bugreport it.

Ok, it is now reported: https://phabricator.wikimedia.org/T177477