Hi,
I've upgraded from 1.7 to 1.18 (I know...). Everything seems to be working fine except for searching through tickets. I can search using one or two letters, but if I use 3 or more letters the search results are blank. I'm not doing complicated searches. Examples:
p
gets results (including tickets with pdf in the ticket subject)
pd
gets results (including tickets with pdf in the ticket subject)
pdf
no results
Does anyone know of a way to fix this?
Searching for either pd or pdf works just fine for me.
You can try to truncate and re-index your Search table as well as drop and repopulate your Ticket CDATA table. PLEASE NOTE that you shouldn't really do this during peak/production hours. You really want to do this after-hours when there is little to no data moving through the system.
TRUNCATE TABLE prefix__search (where prefix is your actual db prefix). Then you can go to the prefix_config table, find where namespace equals mysqlsearch and key equals reindex, set this record's value to 1, and run cron a bunch of times until that table is fully repopulated with data. Depending on how much data you have this can take a few to many cron runs.DROP TABLE prefix_ticket__cdata (where prefix is your actual db prefix). Then you can run cron a bunch of times until that table is automatically recreated and fully repopulated with data. Depending on how much data you have this can take a few to many cron runs.Cheers.
Hmm. I don't seem to have an prefix_search table. Is there a way to fix that?
MariaDB [osticket17]> show tables;
+--------------------------+
| Tables_in_osticket17 |
+--------------------------+
| ost_api_key |
| ost_attachment |
| ost_audit |
| ost_canned_response |
| ost_config |
| ost_content |
| ost_department |
| ost_draft |
| ost_email |
| ost_email_account |
| ost_email_template |
| ost_email_template_group |
| ost_event |
| ost_faq |
| ost_faq_category |
| ost_faq_topic |
| ost_file |
| ost_file_chunk |
| ost_filter |
| ost_filter_action |
| ost_filter_rule |
| ost_form |
| ost_form_entry |
| ost_form_entry_values |
| ost_form_field |
| ost_help_topic |
| ost_help_topic_form |
| ost_list |
| ost_list_items |
| ost_lock |
| ost_note |
| ost_organization |
| ost_organization__cdata |
| ost_plugin |
| ost_plugin_instance |
| ost_queue |
| ost_queue_column |
| ost_queue_columns |
| ost_queue_config |
| ost_queue_export |
| ost_queue_sort |
| ost_queue_sorts |
| ost_role |
| ost_schedule |
| ost_schedule_entry |
| ost_sequence |
| ost_session |
| ost_sla |
| ost_staff |
| ost_staff_dept_access |
| ost_syslog |
| ost_task |
| ost_task__cdata |
| ost_team |
| ost_team_member |
| ost_thread |
| ost_thread_collaborator |
| ost_thread_entry |
| ost_thread_entry_email |
| ost_thread_entry_merge |
| ost_thread_event |
| ost_thread_referral |
| ost_ticket |
| ost_ticket__cdata |
| ost_ticket_priority |
| ost_ticket_status |
| ost_translation |
| ost_user |
| ost_user__cdata |
| ost_user_account |
| ost_user_email |
+--------------------------+
71 rows in set (0.000 sec)Well, that's a problem. Run the below query and follow the above steps to reindex the table with data.
CREATE TABLE IF NOT EXISTS ost__search (
`object_type` varchar(8) not null,
`object_id` int(11) unsigned not null,
`title` text collate utf8_general_ci,
`content` text collate utf8_general_ci,
primary key `object` (`object_type`, `object_id`),
fulltext key `search` (`title`, `content`)
) ENGINE=MyISAM CHARSET=utf8;Cheers.
Thank you. That's much better. I can search for some things now. Mysteriously PDF still doesn't work (but many other things do), but now I'll continue with dropping CDATA and re-running cron.
For whatever reason searching PDF still doesn't work, but everything else does now. I'll see if cron.d does it's job and this repairs over time. If it does not, I'll retry the procedure provided. Email addresses autofill now again as well.
Thank you very much for the help.
Searching tickets is still much improved; however searching users is broken. This makes it very difficult to create new tickets, since we cannot pick a user from the search box, and if we try and add the user, they already exist. We're left with going to the users page which we cannot search, and clicking through page by page until we find them, and selecting the user and opening a ticket for them.
Of note, the prefix_user__cdata table is empty. Could that be the issue? Also, the prefix__search table does not contain any email addresses or customer names. I am running cron.php via /etc/cron.d/ every 2 minutes.
Search doesn't contain user data; that's what the _user and _usercdata tables are for. If the _usercdata table is empty then you can simply DROP the table and run cron to recreate and repopulate with data (just like the _ticket__cdata table).
Cheers.
Well that did regenerate the table. user_id/phone have data, but email, name and notes are all null. Will that self heal over time? I've re-run cron.php manually about 10 times.
It's weird that the fields are empty like that, but search works now. I can search names and email addresses. ¯_(ツ)_/¯
And to ease your mind typically the email and name are empty in the _user__cdata table; nothing is wrong with your install in that regard.
Cheers.
Thanks for that!
