Osticket: A lot of problems in search engine (v1.10)

[daro1337]

After upgrading from 1.9.15 to (v1.10) I've had several issue with search.
Including some "DB errors"

DB Error #1054 [SELECT A3.addressASuser__default_email__address, null AS number, COUNT(DISTINCT A1.ticket_id) ASticketsFROMost_ticketA1 JOINost_userA2 ON (A1.user_id= A2.id) LEFT JOINost_user_emailA3 ON (A2.default_email_id= A3.id) WHERE (A1.staff_id= 8 OR A1.team_idIN (SELECT A1.team_idFROMost_team_memberA1 WHERE A1.staff_id= 8) OR A1.dept_idIN ('1', '5', '6')) GROUP BY A3.address` ORDER BY __relevance__ DESC LIMIT 25] Unknown column '__relevance__' in 'order clause'

---- Backtrace ----
#0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1054', '[SELECT A3.add...') #1 (root)/include/class.orm.php(3133): db_query('SELECT A3.addr...', true, true)
#2 (root)/include/class.orm.php(3180): MySqlExecutor->execute()
#3 (root)/include/class.orm.php(1865): MySqlExecutor->getArray()
#4 (root)/include/class.orm.php(1815): HashArrayIterator->{closure}()
#5 (root)/include/class.orm.php(1794): CallbackSimpleIterator->next()
#6 (root)/include/class.orm.php(1803): CallbackSimpleIterator->rewind()
#7 (root)/include/class.orm.php(1463): CallbackSimpleIterator->valid()
#8 (root)/include/class.orm.php(1480): CachedResultSet->fillTo(9223372036854775807)
#9 (root)/include/class.orm.php(1489): CachedResultSet->asArray()
#10 (root)/include/ajax.tickets.php(79): CachedResultSet->getIterator()
#11 [internal function]: TicketsAjaxAPI->lookup()
#12 (root)/include/class.dispatcher.php(145): call_user_func_array(Array, Array)
#13 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('lookup', Array)
#14 (root)/include/class.dispatcher.php(120): Dispatcher->resolve('lookup', Array)
#15 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/tickets/lookup', NULL)
#16 (root)/scp/ajax.php(262): Dispatcher->resolve('/tickets/lookup')
#17 {main}`

DB Error #1146 [SELECT COUNT(*) FROM (SELECT A1.ticket_id, Z1.relevanceAS__relevance__FROMost_ticketA1 JOINost_userA2 ON (A1.user_id= A2.id) LEFT JOINost_user__cdataA3 ON (A2.id= A3.user_id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) asticket_id, SUM(MATCH (Z1.title, Z1.content) AGAINST ('test' IN NATURAL LANGUAGE MODE)) ASrelevanceFROMost__searchZ1 LEFT JOINost_thread_entryZ2 ON (Z1.object_type= 'H' AND Z1.object_id= Z2.id) LEFT JOINost_threadZ3 ON (Z2.thread_id= Z3.idAND Z3.object_type= 'T') LEFT JOINost_ticketZ5 ON (Z1.object_type= 'T' AND Z1.object_id= Z5.ticket_id) LEFT JOINost_userZ6 ON (Z6.id= Z1.object_idand Z1.object_type= 'U') LEFT JOINost_organizationZ7 ON (Z7.id= Z1.object_idAND Z7.id= Z6.org_idAND Z1.object_type= 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id= Z6.id) WHERE MATCH (Z1.title, Z1.content) AGAINST ('test' IN NATURAL LANGUAGE MODE) GROUP BYticket_id) Z1 WHERE A3.notesIS NOT NULL AND A1.ticket_id= Z1.ticket_id`) __] Table 'osticket.ost_user__cdata' 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 (root)/scp/tickets.php(422): QuerySet->count()
#6 {main}`

Next, in advanced search => add other field => closed date => between => click on calendar icon and change month to left and it switch to November 1899
https://cloud.githubusercontent.com/assets/17786393/20668478/62dd05a8-b56f-11e6-8337-9c8678821293.png

Searching due date, and between also not showing all tickets. Is it possible that upgrade process didn't create all tables?

[ntozier]

Running that query on my system results in the same error. [Err] 1054 - Unknown column 'relevance' in 'order clause'.

Regarding the Adv search, I am unable to replicate your date issue report on my installation.

[daro1337]

How about advanced searching? Searching due date, "n" days between is not showing tickets or it shows wrong tickets. And in advanced search => add other field => closed date => between => click on calendar icon and change month to left and it switch to November 1899, do u have same issues?

[ntozier]

Re: and change month to left and it switch to November 1899, do u have same issues?
-> Regarding the Adv search, I am unable to replicate your date issue report on my installation.

[mengjiann]

I have just upgrade from 1.9.12 to 1.10 yesterday.
I have seen a couples of error under the admin panel:

[INSERT INTOost_attachmentSETfile_id= 557,type= 'D',object_id= 5922,name` = 'screenshot-O0yi'] Duplicate entry '5922-557-D' for key 'file-type'

---- Backtrace ----
#0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1062', '[INSERT INTO o...') #1 (root)/include/class.orm.php(3133): db_query('INSERT INTOos...', true, true)
#2 (root)/include/class.orm.php(597): MySqlExecutor->execute()
#3 (root)/include/class.attachment.php(161): VerySimpleModel->save()
#4 (root)/include/ajax.draft.php(101): GenericAttachments->upload(Array)
#5 (root)/include/ajax.draft.php(300): DraftAjaxAPI->_uploadInlineImage(Object(Draft))
#6 [internal function]: DraftAjaxAPI->uploadInlineImage('5922')
#7 (root)/include/class.dispatcher.php(145): call_user_func_array(Array, Array)
#8 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('5922/attach', Array)
#9 (root)/include/class.dispatcher.php(120): Dispatcher->resolve('5922/attach', Array)
#10 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/draft/5922/att...', NULL)
#11 (root)/scp/ajax.php(262): Dispatcher->resolve('/draft/5922/att...')
#12 {main}
`

Im also facing difficulties in using the search feature. It is not working normally as the search result is incorrect.

[ntozier]

@mengjiann your post does not appear to have anything to do with the topic in this thread. This thread is about search, not attachments. Perhaps you should visit the forums or add your comment to an issue about your subject, or start your own issue report?

[internet-conception]

I cant really finish an advanced search too :(

After launching a "small" search, like ticket from one agent. I just have the shaking small blank popup.
If I hit "esc" on my keyboard, the popup disappear.
Then if I click on the the search link (or simply refresh the page) the search result page is updated and filter properly...

It look like an ajax trouble (in my console there is no response back) or a session trouble ?

Any idea ? I had few DB Error #1146 while upgrading from 1.9

[djmrsmith]

might help:

https://github.com/osTicket/osTicket/issues/3733#issuecomment-305755642

[cttechcorp]

Does the knowledgebase search support AND or OR operations?

[darrellenns]

I am also seeing DB errors after upgrading from 1.9.12 to 1.10.4. I get the error shown below when I search for a user while creating a ticket in the agent panel. Maybe something is missing in the db upgrade scripts? I have checked my SQL db and confirmed that the ost_user__cdata table is not there. Is that supposed to be created in the upgrade process?

``` [SELECT A1.id, A1.name, A5.addressFROMost_userA1 LEFT JOINost_user_emailA2 ON (A1.id= A2.user_id) LEFT JOINost_organizationA3 ON (A1.org_id= A3.id) LEFT JOINost_user__cdataA4 ON (A1.id= A4.user_id) LEFT JOINost_user_emailA5 ON (A1.default_email_id= A5.id) WHERE (A2.addressLIKE '%someuser@%' OR A1.nameLIKE '%someuser@%' OR A3.nameLIKE '%someuser@%' OR A4.phone` LIKE '%someuser@%') LIMIT 25]

Table 'osticket.ost_user__cdata' doesn't exist


---- Backtrace ----

0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1146', '[SELECT A1.id...')

1 (root)/include/class.orm.php(3136): db_query('SELECT A1.id,...', true, true)

2 (root)/include/class.orm.php(3192): MySqlExecutor->execute()

3 (root)/include/class.orm.php(1845): MySqlExecutor->getRow()

4 (root)/include/class.orm.php(1818): FlatArrayIterator->{closure}()

5 (root)/include/class.orm.php(1797): CallbackSimpleIterator->next()

6 (root)/include/class.orm.php(1806): CallbackSimpleIterator->rewind()

7 (root)/include/class.orm.php(1463): CallbackSimpleIterator->valid()

8 (root)/include/class.orm.php(1480): CachedResultSet->fillTo(9223372036854775807)

9 (root)/include/class.orm.php(1489): CachedResultSet->asArray()

10 (root)/include/ajax.users.php(94): CachedResultSet->getIterator()

11 [internal function]: UsersAjaxAPI->search()

12 (root)/include/class.dispatcher.php(145): call_user_func_array(Array, Array)

13 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch(false, Array)

14 (root)/include/class.dispatcher.php(120): Dispatcher->resolve(false, Array)

15 (root)/include/class.dispatcher.php(38): UrlMatcher->dispatch('/users', NULL)

16 (root)/scp/ajax.php(262): Dispatcher->resolve('/users')

17 {main}

````

[JediKev]

@darrellenns

Just search for any User via the User directory. Once you do this the system should recreate the ost_user__cdata table and all should be fine. If that doesn't recreate the table then create a new fake User account and it should recreate the table for you.

Cheers.

[ntozier]

Unless of course your DB User doesn't have create privs. But I would think that would of course result in a different SQL error in your logs.

[darrellenns]

@JediKev

Searching in user directory did indeed create the table. When I search in the new ticket dialog, it no longer throws a db error. However, it also does not find any users. When I look at the contents of the ost_user__cdata table, it has a row for each user but the email and name columns are all filled with NULL. Is there some trigger that is supposed to populate those values? The name/email data is all there in the other ost_user tables;

I also found references to some other cdata tables (ticket__cdata, task__cdata, organization__cdata, etc) by grepping through the code. Those tables were also missing, and searching for those items in the UI did indeed create the tables.

[JediKev]

@darrellenns

It appears you've ran into some sort of upgrader error as that's not supposed to happen. Any upgrader errors in your logs?

Cheers.

[darrellenns]

@JediKev

There are no upgrader errors in the log. The only errors in there are from the missing user__cdata table.

Interestingly, if I create a new account in osticket, that account does appear in the new ticket search and it also has NULL in the __cdata email and name columns (although it has empty string instead of NULL in the phone and notes columns).

Another possible factor is that I am also using the LDAP plugin.

Is there somewhere other than the ost_user tables where it would be caching data for searches?

[JediKev]

@darrellenns

Ahh okay. Have you re-downloaded and re-installed the plugin? If not you definitely should (you might have to reconfigure it as well).

Cheers.

[darrellenns]

@JediKev

I re-installed with the latest plugin when I did the upgrade. The configuration is all still there and LDAP authentication is working fine.

[JediKev]

@darrellenns

Have you tried applying this patch:
https://github.com/osTicket/osTicket/pull/3456

Cheers.

[darrellenns]

@JediKev

Good find, that's totally it! The new ticket user search is working now, thanks!

Just a thought - maybe the update scripts should create the __cdata tables (so that you don't get db errors if you havn't done a user directory search).