Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[resolved] Dropped search table not rebuilding

Our search is not working correctly. The system is unable to find email addresses which we 100% can verify are in the system.  Single word searches appear to be working.

So... like in the old version (1.9.15) i dropped the search table in hopes that it would rebuild and reindex.  But it's been 30 minutes and the search table does not appear in PhpMyAdmin.  And now when I go into the Agent Panel and search I get 0 results for any search.  So clearly I screwed up the system my dropping the search table. To drop the table I was in PhpMyAdmin and I clicked the red "Drop" button next to the ost search table, not sure if that's the wrong method?

What do I need to do to get the search index table rebuild and working again?

Here is the error log when I try and search:

DB Error #1146

[SELECT COUNT(*) FROM (SELECT A1.`ticket_id`, Z1.`relevance` AS `__relevance__` FROM `ost_ticket` A1 JOIN (SELECT COALESCE(Z3.`object_id`, Z5.`ticket_id`, Z8.`ticket_id`) as `ticket_id`, SUM(MATCH (Z1.title, Z1.content) AGAINST ('corsair nut' IN NATURAL LANGUAGE MODE)) AS `relevance` FROM `ost__search` Z1 LEFT JOIN `ost_thread_entry` Z2 ON (Z1.`object_type` = 'H' AND Z1.`object_id` = Z2.`id`) LEFT JOIN `ost_thread` Z3 ON (Z2.`thread_id` = Z3.`id` AND Z3.`object_type` = 'T') LEFT JOIN `ost_ticket` Z5 ON (Z1.`object_type` = 'T' AND Z1.`object_id` = Z5.`ticket_id`) LEFT JOIN `ost_user` Z6 ON (Z6.`id` = Z1.`object_id` and Z1.`object_type` = 'U') LEFT JOIN `ost_organization` Z7 ON (Z7.`id` = Z1.`object_id` AND Z7.`id` = Z6.`org_id` AND Z1.`object_type` = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.`user_id` = Z6.`id`) WHERE MATCH (Z1.title, Z1.content) AGAINST ('corsair nut' IN NATURAL LANGUAGE MODE) GROUP BY `ticket_id`) Z1 WHERE A1.`ticket_id` = Z1.`ticket_id`) __] Table 'osticket.ost__search' doesn't exist

---- Backtrace ----
#0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1146', '[SELECT COUNT(*...')
#1 (root)/include/class.orm.php(3133): db_query('SELECT COUNT(*)...', true, true)
#2 (root)/include/class.orm.php(3189): MySqlExecutor->execute()
#3 (root)/include/class.orm.php(2644): MySqlExecutor->getRow()
#4 (root)/include/class.orm.php(1193): MySqlCompiler->compileCount(Object(QuerySet))
#5 [internal function]: QuerySet->count()
#6 (root)/include/staff/tickets.inc.php(146): count(Object(QuerySet))
#7 (root)/scp/tickets.php(492): require_once('/var/www/vhosts...')
#8 {main}


Server Information
osTicket Versionv1.10 (901e5ea) —  Up to date
Web Server SoftwareApache
MySQL Version5.5.52
PHP Version7.0.14

Comments

  • So I haven't tried to do this... but I think that you would want to:

    UPDATE `%TABLE_PREFIX%config` SET `value` = '1'
      WHERE `key` = 'reindex' and `namespace` = 'mysqlsearch';

    And then run cron.php.  That SQL command should of course be changed to your installation.  Substitute "%TABLE_PREFIX%" for your table prefix (which from your post looks to be "ost_".  This should tell osTicket that it needs to reindex, and then it should trigger on the next cron run.  
  • That worked.  Search Table has been created.  I ran "php -f cron.php" in SSH which appears to have run the cron.php file, although it did take quite some time to finish which is strange.

    The search table is there now, it is about 5mb while my entire DB is about 700mb, not sure if that sound right.  Unfortunately, I still cannot search for email addresses. I am going to start a new thread on that.
This discussion has been closed.