HOW TO FIX YOUR SUITECRM GLOBAL SEARCH BY RE-INDEXING
Has your Suite CRM global search broken? I hear you. Hard times. I actually went through the entire process of upgrading our whole instance for about a month and after finishing all that, it turns out it was just this broken index! Good news is that the newer version of Suite CRM is about ten times more awesome so I’m glad I was forced to upgrade. But here’s the point – this tutorial might be all you need to fix your Suitecrm broken global search.
The problem is that if you are in a shared host environment like me and not very skilled it’s just downright scary to perform some of the tutorials I found. I had no choice but to give it a shot and thankfully it worked. Hopefully this tutorial will remove some of the fear for you that I had to go through.
First, as always, none of this would have been possible without all the history of awesome developers who put their code into the Suitecrm project. I will refer to a few specific folks but there are many others involved as with any free software proejct. To you we are all thankful and hopefully this tutorial helps others join the Suitecrm project.
Next, let’s talk about the task at hand
Before We Begin
I find it’s useful to look at the steps in a casual way before doing them. No one really does this in tutorials but I find it lowers stress levels when you approach each step. What we’re going to do here is just back up a few things so we don’t kill our crm forever if we make a mistake, rename a folder, learn a couple of new things in Phpmyadmin (some tool in Cpanel and elsewhere), delete a bunch of old rows in a database table that’s making your Global Search broken, and then tell Suitecrm to ‘start indexing afresh’. That’s it. So remember, ‘If Wayne can do it, you can do it too. Just take your time.
I am assuming that you are using a Cpanel shared hosting environment. If you aren’t, I’m guessing that you are probably smarter than me anyway and this tutorial will feel to baby-like. Feel free to skip to this tutorial here if that is you. Pretty much all of what I’m doing is from that but he didn’t explain a few things I simply couldn’t figure out online.
Step 0 – Advise Others Not to Use SuiteCRM
I always tell others to not use the crm when I do maintenance. Probably you should advise the same.
Step 1 – Back up your Whole Suite CRM instance
In theory you ‘could’ skip this step but I’ve learned it’s worth it. We’re only renaming one directory in this tutorial so if you are careful you could skip it. But since you probably need a recent backup anyway, why not do it?
- a) Go to file manager
- b) Go to your suite crm install folder and back it up. There are two ways to do it I’ve learned. You can compress it where it is with the Cpanel compress tool and then just download to your computer or, better, use an FTP tool and get it that way. Both work.
Step 2 – Back up your MySQL Database
Besides your files in Step 1, these database files are the other critical files to back up.
- a) Go to ‘backup’ section of cpanel,
- b) go to “Download a MySQL Database Backup”
- c) select the database asssociated with your suitecrm and click it. If you don’t now what your database is, and you have a few, then there are two ways to figure it out:
- if you installed by Softaculous, you can just search Suitecrm in the search field, find your install and then click the pencil icon to see the details which will show the database name
- If you didn’t install it that way, you’ll have to find the Suitecrm directory in file manager, go into the directory, and then click the ‘view’ button after selecting the config.php file. Scroll down until you see a block that looks like this which will expose your info:
‘db_host_name’ => ‘localhost’,
‘db_host_instance’ => ‘SQLEXPRESS’,
‘db_user_name’ => ‘yourdatabaseNAMEwillbehere’,
‘db_password’ => ‘randomPASSWORDwillbehere’,
‘db_name’ => ‘yourdatabaseUSERNAMEwillbehere’,
‘db_type’ => ‘mysql’,
‘db_port’ => ”,
‘db_manager’ => ‘MysqliManager’,
Now you have your database file safely saved on your machine as well as a backup of your suitecrm. Nice work.
Step 3 – Rename the Index file.
In your file manager navigate to this spot:
(yes, that’s a lot of indexes!)
Inside this is, no surprise, another index folder! But this is the one we want. Double click on the text and re-name it to ‘index.backup’ from it’s current ‘index’. Just so you know what’s going to happen here is the system will go looking for ‘index’ but because you just renamed it it won’t find it and will create a new one (which is a trick, because that’s what we want, you trickster!). At the same time you’re backing up the old one so bonus.
And that’s it for the work you need to do in your file manager for now.
Step 4 – Stop your Cron Jobs if You Have Them Started (which I’m thinking you do…)
If your Suitecrm is doing workflow stuff and sending alerts, probably your cron jobs are setup so you already know what a cron job is. If not, back up your current cron setting so you can quickly get it up and running again. I just saved this in in a safe place in a text file to use again after completing this stuff.
- a) go to Cron Jobs in cpanel
- b) copy and paste the settings you have into the text file and save it safely somewhere
- c) delete the cron job This makes sure the cron job won’t run while you are doing the next steps. I went a step further, ( not sure if it’s needed ) and changed all my scheduled events in admin/schedules to ‘inactive’ to assure that I could turn them on one at a time later after I turned this on again. I think this was a good move so I’ll advise it next:
Step 5 – Turn all your scheduled jobs in Suite CRM Scheduler to ‘Inactive’
See comments just above.
## Do the Database Work
Ok, now we’re ready to do the stuff that was totally foreign to me but pretty fast and easy once you know how. Go into ‘Phpmyadmin’ section of Cpanel to begin.
Delete Rows from Table aod_index
This one is pretty easy. just find it on the left
… and click it then click the red delete circle and say yes to delete (or was it ‘go’? whatever, you’ll know…)
Delete Rows from aod_indexevent
This next step requires getting rid of WAAAAY too many rows to do by a human. Trust me, I tried before I was forced to find this better and faster way.
This one is a bit more ‘cryptic’ since you have to write a code. But the neat part is yiou know you are doing it right because it auto-fills as you type it.
- a) go to SQL tab at the top of your Phpmyadmin
- b) highlight and delete whatever is in that big white query box at the top if there is anything
- c) Start typing this command and use the tab key (for fun) to autofill when it pops up stuff:
- c) press ‘go’ on the far right
- d) agree when the warning Do you really want to execute “TRUNCATE aod_indexevent”? comes up
Boom. Done. Now we have to turn everything back on.
Step 6 – Turn Everything Back On
- 1. your cron job
- 2. your schedulers in admin, but see note below
There are two important admin schedulers related to your searches which need to index. the main one related to above which I believe broke our Global Search was the ‘Optimize AOD Index’ job. SO, for this one what I did was set it to ‘every 2 hours’ after I did the steps above. This would allow me to see an improvement sooner. Then, once I realized it was fixed, I switched it back to ‘every 14 hours’. I’m not sure what a good amount of time is but that seems to be working well for me. SO maybe you could do the same which will allow you to see if its working sooner. It seemed that by the end of the first day mine was fully working again.
Hope this tutorial helps someone as I don’t have developer skills to pay the project back