Osticket: How to rebuild / recreate ost__search table?

[e3eli3h]

osTicket v1.9.9

We have 2 tickets with a distinct word (our search term) in each. Simple/Advanced search yielded only 1 match. In the ost__search table, there is in fact only one of the 2 tickets indexed.

I read somewhere that if the ost__search table is truncated, it should rebuild itself. This however is not happening.

So, how can I "force" a rebuild of the ost__search table please?

Thank you.

[mckaygerhard]

this its a well know in forums, please use forums firts, the issue tab in github its for issue only.. i think this its:

REPAIR TABLE '<nametable>' QUICK

[greezybacon]

Actually, I don't know where that code came from. DROP the table and it will be recreated and repopulated. As long as it exists the system will only add and update its contents

[e3eli3h]

@mckaygerhard Thank you for your creative feedback. Whilst this may not be an "issue" for you, it is for us.

@greezybacon Yes, I am aware that the table would be added to for new content.

Our question here is how to re-populate it with all previous content too. As explained in OP, the data in this table was incomplete to begin with.

[mckaygerhard]

hi @ergatides its not and issue due table are populates with history actions.. so u want to repopulate or reindexing, if u dont want to lost the cache run the query at the database engine and indexing table will be recreate...

and of course if u drop the table all the index cache will be made again, but for huge system like in my production system this will decrease a lot the searchs..

_but in the ticket u said "how " so that's why its not and issue... u made a question!_

if the indexing of the search are invalid (u only see one result and must be two) u must made more descriptive erro with all settings and environment variables paste in the issue text (that are not provided)

in any case both solutions are valit and works for u case.. the repair if u dont want to lost performance and the drop if u want more force ...

[e3eli3h]

Hello @mckaygerhard. I fully appreciate your enthusiasm and effort to help us with our problem, but I do not think you have received a solid understanding of anything I have said so far.

If there was a known documented way to do what needs to be done, I would not have started topic with "How" but instead with "Why". For this sir, I truly apologise.

Is there anyone else who can shed some light if anything can be done in our case please?
Has anyone out there written an addon/plugin to re-create the search index from history?

[ntozier]

Q: So, how can I "force" a rebuild of the ost__search table please?

A: @greezybacon reply "DROP the table and it will be recreated and repopulated. As long as it "[.The table']." exists the system will only add and update its contents"

The command to drop a table is:
'DROP TABLE ost__search;'

[mckaygerhard]

@ergatides u have right in that part.. i have an issue opened around creation of users using sql commands due documentation are missing.. but firts i made that question in forums and later here..

i have an idea, i started an documentation about ost tables.. but i noted the api changed too much.. so i propose start a wiki here for tables in osticket..

[greezybacon]

@ergarides, did you get your search index rebuilt?

[e3eli3h]

Guys, seriously, the only thing which has been suggested here are MySQL commands which I do not need help with. I had explained that I have TRUNCATED the ost__search table. For those who do not know, this empties a table from all of its data retaining its schema and resetting any autoincrement to 0 (if present).

Great, so you told me to DROP it instead and it will be recreated, which yes, it does, great... can we move along now?

But none of you are reading my question.

After dropping the table and creating a new ticket with title "Issue Summary" and body of "Issue Details", the table now contains 2 rows:

| object_type | object_id | title | content |
| --- | --- | --- | --- |
| H | 99243 | | Issue Details |
| T | 25715 | 858378 Issue Summary | Issue Summary |

English isn't my first language either but is it really that hard to understand that my question is what can I do to now populate this table with the search data of all previous tickets? I really don't know how else to word this.

If there is no existing solution then I will make one... only I have no time for this right now and was wandering if it is possible already.

[mckaygerhard]

i anwered u'r question, u must repair event truncate the data, the other two gouys are too busy to reading inside u'r lines, i'll explain to u my point (if i missing please ilustrate me)

when u truncate the table must construc again all the data, and with repair the idex search will be corrected. When u truncate the data all will be threath as new and its not the case.. due u already have tickets ...

if with reapir still the missing ticket does not show in results, the its confirmed its an issue... are u not reading me?

[greezybacon]

@ergatides Sorry about the confusion. There is a setting int he config table called reindex, I thought it would be reset when you drop the table. I'll check the code again to make sure it works that way. You can force reindexing with

UPDATE `ost_config` set `value` = 1 WHERE `key` = `reindex` AND `namespace` = `mysqlsearch`;

Or just edit the database with your favorite editor and change the value.

[e3eli3h]

@greezybacon, there is no reason to apologize whatsoever.

Your last post is exactly what we were looking for.
The moment I updated this value from 0 to 1, the ost__search table is incrementally re-populated with each and every single new search performed... and our missing entries are now being returned correctly.

Thank you very very much for your time!

[greezybacon]

@ergatides I've created #2250 to automatically reindex the table if it is dropped. Thanks for your patience and glad you got everything working.

[mckaygerhard]

i added a note about older versions of mariadb.. https://github.com/osTicket/osTicket-1.8/pull/2250/files#r34730398

mariadb 5.3 supports InnoDB but not over FULLINDEX searchs, so that's why this feature need mysql 5.6 or mariadb 10.X

note that this only its valid if the columns are text and varchar.

[greezybacon]

The system uses MyISAM automatically for MySQL versions before 5.6

Am Mittwoch, 15. Juli 2015 schrieb PICCORO Lenz McKAY :

i added a note about older versions of mariadb..
https://github.com/osTicket/osTicket-1.8/pull/2250/files#r34730398

mariadb 5.3 supports InnoDB but not over FULLINDEX searchs, so that's why
this feature need mysql 5.6 or mariadb 10.X

note that this only its valid if the columns are text and varchar.

—
Reply to this email directly or view it on GitHub
https://github.com/osTicket/osTicket-1.8/issues/2208#issuecomment-121751764
.