[resolved] DB Error #1191

ReneWiedemann

Has anyone a solution for that?

[SELECT COUNT(*) FROM (SELECT A1.ticket_id FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_department A6 ON (A4.object_type = 'D' AND A4.object_id = A6.id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('Bull\'s' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('Bull\'s' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) 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)) Z1 WHERE ((A2.state = 'open' AND (A1.staff_id = 9 OR A5.staff_id = 9)) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8, 9) OR A6.id IN (1, 2, 4, 5, 6, 7, 8, 9)) AND A1.ticket_id = Z1.ticket_id ORDER BY Z1.relevance LIMIT 25) __]

Can't find FULLTEXT index matching the column list 
---- Ablaufverfolgung ---- 
#0 C:\inetpub\wwwroot\support\include\mysqli.php(199): osTicket->logDBError('DB Error #1191', '[SELECT COUNT(...') 
#1 C:\inetpub\wwwroot\support\include\class.orm.php(3455): db_query('SELECT COUNT()...', true, true) 
#2 C:\inetpub\wwwroot\support\include\class.orm.php(3511): MySqlExecutor->execute() 
#3 C:\inetpub\wwwroot\support\include\class.orm.php(2941): MySqlExecutor->getRow() 
#4 C:\inetpub\wwwroot\support\include\class.orm.php(1372): MySqlCompiler->compileCount(Object(QuerySet)) 
#5 C:\inetpub\wwwroot\support\include\ajax.tickets.php(66): QuerySet->count() 
#6 C:\inetpub\wwwroot\support\include\class.dispatcher.php(145): TicketsAjaxAPI->lookup() 
#7 C:\inetpub\wwwroot\support\include\class.dispatcher.php(38): UrlMatcher->dispatch('lookup', Array) 
#8 C:\inetpub\wwwroot\support\include\class.dispatcher.php(120): Dispatcher->resolve('lookup', Array) 
#9 C:\inetpub\wwwroot\support\include\class.dispatcher.php(38): UrlMatcher->dispatch('/tickets/lookup', NULL) 
#10 C:\inetpub\wwwroot\support\scp\ajax.php(289): Dispatcher->resolve('/tickets/lookup') 
#11 {main}

ntozier

asked the devs to take a look at this thread.

KevinTheJedi

@ReneWiedemann

Is this a fresh install? If so, did you install with a language pack? If so, this is most likely your issue, you cannot install in anything other than English at this time. You will need to make a completely fresh install of English then add the language pack afterwards.

If this is not a new install, then your environment is messed up as it's saying ost__search.title and ost__search.content are not FULLTEXT indexed. As you can see from the below lines of code that when we create the ost__search table it puts the FULLTEXT index on both title and content:

https://github.com/osTicket/osTicket/blob/develop-next/include/class.search.php#L447-L454

You need to address this issue to get rid of this error.

Cheers.

ReneWiedemann

KevinTheJedi

That was an Upgrade from 1.10 up to 1.12.2 inkl. German Language.
I also have make this Command "ALTER TABLE ost__search ADD FULLTEXT(title,content);" but that resolve not the Problem.
What is the easy way to fix that Problem? Delete the "ost__search" Table complete?

ReneWiedemann

Problem found. There were duplicate entries in the database in the ostsearch table under Structure. These were reported as errors and proposed for deletion. I did that and then executed the command again on the ostsearch under SQL ALTER TABLE ost__search ADD FULLTEXT(title,content);
After that the Search ist working again! 😄

KevinTheJedi

@ReneWiedemann

Nice. If you have any further issues please re-open.

Cheers.