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
I love suitecrm. Freedom coding built by the community, super useful, and awesome for business.
I’m going to start logging some stuff here that will maybe save some other people time, too.
In this case, I realized today that I’m logging into my accounts page, scrolling down to ‘contacts’ and seeing absolutely nothing useful except an email address. The default stuff doesn’t seem very good so I’m changing to:
- name, office line, mobile, position/title, and email
That’s way more useful. Surprisingly, it was very hard for me to figure out how to do this and I had to actually look at the code. Good news is that it’s *crazy* easy once you know where to find stuff. Here we go:
- go to admin
- go to studio
- go to accounts module
- go to subpanels
- go to contacts
- drag and drop what you don’t want out and drag in what you want and in the order you want it
- click ‘save and deploy’
I discovered SuiteCRM by means of searching through my web host’s cpanel softaculous thing – that thing that allows one click installs of ‘apps’ for your server. Over time, I became a heavy believer in and user of SuiteCRM. Eventually, I installed an instance for my company on my shared host and the company started using it more and more. Finally, today, I realized that I better figure out how to back this bad boy up. I thought it would be as simple as going to the SuiteCRM documentation, but I guess I’m not smart enough for that. I also tried the SuiteCRM user guides which, for everything else, are very excellent. However, couldn’t seem to figure it out there, either. There is also this ‘backup feature’ in the Admin area which also I couldn’t figure out on my own or by searching. I even posted this post in the forum and it seems as though I had asked a question that was too easy for anyone to answer.
I’m just a standard, shared host, cpanel user, not a database guy or even web host guy, although I’ve got a small server running at home. How is it that I cannot figure out how to do a simple back up of SuiteCRM so that I can protect it as well as move it from host to host should I choose? So, with all that background, I will now begin to find the answer and document it here for my future benefit and hopefully help a few others along the way.
What I have learned from reading most stuff online is that If I want to be sure something like SuiteCRM is backed up safely I have to do the following two things:
- backup the SuiteCRM folder/directories/files
- back up the mysql database
Sounds easy except that when I went into cpanel, there was no ‘download’ button to get all my directory stuff! Although I’m shocked that there doesn’t seem to be a web-based download button, I also assume there must be a security reason for that so I won’t kick up a storm. I now assume that I have to do it the old FTP client way. So, I will now perform the following two steps in an attempt to download the directory parts of SuiteCRM:
- create an FTP account from the home page area of cpanel so that I can login with FTP client and
- download and install Filezilla on my computer
I went to the Ubuntu software centre and downloaded Filezilla successfully.
I entered in my credentials. It turns out that I did not have to create a new FTP user since my main cpanel login credentials/passwords worked perfectly to get in. However, if this is for someone else to login, obviously you’ll want to create a new FTP user/password for them and restrict them to the areas they are permitted to go.
Since my website wasn’t the ‘main’ website of my shared hosting, I had to go into the ‘public_html’ directory to find my SuiteCRM directory. Found it!
I learned the hard way that I must *first* choose the local machine directory to where the files must be downloaded. The first time I didn’t specify so it started running errors for five minutes while I sat there and drooled on my desk. Once I figured it out, I cancelled, started again by stating the local directory (in fact, I created a new directory just in case and to help me remember where it was) and then right clicked on the ‘remote server’ folder (my SuiteCRM install folder) and clicked ‘download’ and now everything appears to be downloading successfully to my machine.
The next step will be the second step which is to download/backup the mysql database… Of course, I’ve never done this so I am going to find this tutorial by Siteground which looks pretty solid. Siteground (whoever they are) seem pretty cool, by the way and has killer SEO results with Duck Duck Go. Let’s see if this tutorial works. You will probably have lots of time to study this tutorial if your FTP download is as big as mine. My SuiteCRM has nearly 10,000 files to download. It appears also, that this tutorial will show how to restore this mysql database for the next part of this tutorial which I’ll probably have to write for myself… In this tutorial, the only thing that seems ‘unknown’ to me is the ‘drop table’ option. Good thing I didn’t choose this option, I believe, because this tutorial shows that ‘drop table’ means to delete tables. I am quite certain that I will want to *keep* all the tables in this database so that I can import the database perfectly into the server to match the SuiteCRM stuff…. but I don’t know anything so we’ll see as we move forward 🙁
The above tutorial was good except that they claimed there was an ‘add drop table’ option in their screenshot example but it wasn’t there. They also didn’t mention that you have to choose ‘custom’ instead of ‘quick’ in order to view those options. And it turns out that the ‘add drop table’ option is just a recommended feature to add in while exporting which I chose to do. Otherwise, click ‘go’ and it worked. I have an .sql file on my hard drive now.
I think I’ve now backed everything up, but now I have to try to move it onto another server to test it out. Hopefully I will have a link to that tutorial soon but for now, stand by.