Elgg site throws fatal error page if we put email address in search member search box

Elgg site throws fatal error page if we put email address in search member search box, how to fix it

  • This is the error its throwing, not able to understand whats the issue from database, can you pls any one let me know if you figure out from below

    ################

    [Sat Sep 15 17:40:37.154272 2018] [php7:notice] [pid 14667] [client 157.49.203.181:16600] Exception at time 1537033237: DatabaseException: An exception occurred while executing 'SELECT COUNT(DISTINCT e.guid) as total FROM elgg_entities e  JOIN elgg_metadata n_table on\n\t\t\te.guid = n_table.entity_guid  JOIN elgg_metastrings msn on n_table.name_id = msn.id  JOIN elgg_users_entity ue ON e.guid = ue.guid  JOIN elgg_metadata md on e.guid = md.entity_guid  JOIN elgg_metastrings msv ON n_table.value_id = msv.id  WHERE  (((MATCH (ue.username,ue.name) AGAINST ('+myemail@gmail.com' IN BOOLEAN MODE))) OR ((((((msn.string IN ('description','briefdescription','location','interests','skills','contactemail','phone','mobile','website','twitter')) AND ((n_table.access_id IN (2)) AND (n_table.enabled = 'yes'))))) AND msv.string LIKE '%myemail@gmail.com%'))) AND  ((e.type = 'user')) AND  (e.site_guid IN (1)) AND ((e.access_id IN (2)) AND (e.enabled = 'yes'))':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected '@', expecting $end\n\nQUERY: SELECT COUNT(DISTINCT e.guid) as total FROM elgg_entities e  JOIN elgg_metadata n_table on\n\t\t\te.guid = n_table.entity_guid  JOIN elgg_metastrings msn on n_table.name_id = msn.id  JOIN elgg_users_entity ue ON e.guid = ue.guid  JOIN elgg_metadata md on e.guid = md.entity_guid  JOIN elgg_metastrings msv ON n_table.value_id = msv.id  WHERE  (((MATCH (ue.username,ue.name) AGAINST ('+myemail@gmail.com' IN BOOLEAN MODE))) OR ((((((msn.string IN ('description','briefdescription','location','interests','skills','contactemail','phone','mobile','website','twitter')) AND ((n_table.access_id IN (2)) AND (n_table.enabled = 'yes'))))) AND msv.string LIKE '%myemail@gmail.com%'))) AND  ((e.type = 'user')) AND  (e.site_guid IN (1)) AND ((e.access_id IN (2)) AND (e.enabled = 'yes')) \n\nPARAMS: Array\n(\n)\n in /var/www/vendor/elgg/elgg/engine/classes/Elgg/Database.php:446\nStack trace:\n#0 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Database.php(387): Elgg\\Database->executeQuery('SELECT COUNT(DI...', Object(Doctrine\\DBAL\\Connection), Array)\n#1 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Database.php(227): Elgg\\Database->getResults('SELECT COUNT(DI...', NULL, true, Array)\n#2 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Database/EntityTable.php(658): Elgg\\Database->getDataRow('SELECT COUNT(DI...')\n#3 /var/www/vendor/elgg/elgg/engine/lib/entities.php(327): Elgg\\Database\\EntityTable->getEntities(Array)\n#4 /var/www/mod/search/search_hooks.php(175): elgg_get_entities(Array)\n#5 [internal function]: search_users_hook('search', 'user', Array, Array)\n#6 /var/www/vendor/elgg/elgg/engine/classes/Elgg/PluginHooksService.php(52): call_user_func_array('search_users_ho...', Array)\n#7 /var/www/vendor/elgg/elgg/engine/lib/elgglib.php(827): Elgg\\PluginHooksService->trigger('search', 'user', Array, Array)\n#8 /var/www/mod/members/views/default/resources/members/search.php(25): elgg_trigger_plugin_hook('search', 'user', Array, Array)\n#9 /var/www/vendor/elgg/elgg/engine/classes/Elgg/ViewsService.php(370): include('/var/www/mod/me...')\n#10 /var/www/vendor/elgg/elgg/engine/classes/Elgg/ViewsService.php(306): Elgg\\ViewsService->renderViewFile('resources/membe...', Array, 'default', true)\n#11 /var/www/vendor/elgg/elgg/engine/lib/views.php(514): Elgg\\ViewsService->renderView('resources/membe...', Array)\n#12 /var/www/mod/members/start.php(185): elgg_view_resource('members/search')\n#13 [internal function]: members_page_handler(Array, 'members')\n#14 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Router.php(100): call_user_func('members_page_ha...', Array, 'members')\n#15 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Application.php(436): Elgg\\Router->route(Object(Elgg\\Http\\Request))\n#16 /var/www/vendor/elgg/elgg/engine/classes/Elgg/Application.php(384): Elgg\\Application->run()\n#17 /var/www/index.php(8): Elgg\\Application::index()\n#18 {main}, referer: https://www.elgg.com/members/newest

    ################

  • bumping this question to see if any one knows about this error, thanks.

  • I am using a vanilla and default install of Elgg 3x with php 7.2
    I do not get any such error when I search with email in the Members page. If your version is not 3x can you please try out with Elgg 3x and let know the results and your hosting environment? Thanks.

  • Are you using any 3rd party plugin for search?

  • I don't find any issue with the SQL query because its running fine if we execute it manually. (Tested in MySQL - MariaDB)

  • Found the issue.. 

    http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
    http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

    "InnoDB full-text search does not support the use of the '@' symbol in boolean full-text searches. The '@' symbol is reserved for use by the '@distance' proximity search operator."

    Source: https://bugs.mysql.com/bug.php?id=74042

     

    Possible Solution: Try changing the table type to MyISAM (This is what I am using)

  • Thanks Rohith thats great answer , on possible solution i cant shift to MyIsam as InnoDB is only compatible for most cloud support, meanwhile i would like to show 404 page, page not found error for all exceptions, because in productiion site fatal error throwing does not looks good, so is there a setting in elgg where we can display gracefully 404 page not found message instead of crash fatal error? thanks!

  • Welcome @worldopensources

    Is there a setting in elgg where we can display gracefully 404 page not found message instead of crash fatal error?

    Unfortunately, there is no "setting" in elgg for this.

  • A fatal error should not be sending 404. That's confuses everyone: from crawlers to regular users. Instead fix the issue with your configuration, or use a hook a rewrite the keyword to strip unsupported characters.