ServerPilot, Digital Ocean and Craft CMS
Setting up a server with Digital Ocean, Server Pilot and CraftCMS, intimidating but easier than I thought. Here is everything you need to know.
I recently changed hosts due to a bad support experience and learning that my previous host had been acquired by EIG. Find out why EIG is evil by reading this, this and this. My new host seemed to meet all of my requirements. However after a few weeks my site became ridiculously slow and I learned that the server response was sitting at 3.7 seconds. Yesterday and this morning I looked at Digital Ocean as a replacement.
I had previously looked at Digital Ocean but was intimidated by having to set up all aspects of the server. I was talking with Andrew Welch in the Craft Slack room and he suggested looking at Server Pilot or Forge. So I tried out Server Pilot and after a couple of minor hiccups everything is working fine.
One thing that I like about using serverpilot is no matter what server you are on, your path is exactly the same.
/srv/users/serverpilot/apps/APP_NAME/public/
this makes it really easy to set path settings instead of having to fumble around Cpanel trying to find the path or creating a phpfile to find the path via a front end page.
The only real issue I ran into was that initially this site wouldn't load because of a SQL error.
Internal Server Error CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'creighton_mma_craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Insert ip address into host field
This error will not be present once Craft 3 is released but for Craft 2.x versions it occurs. Fortunately there is a fix documented here. I'll copy the details below as well.
You need to SSH in to your server as root. On windows you can do this with putty and adding your servers IP into the host field. Click open and when prompted type root for user and your root password which you should have received when creating your digital ocean account.
Once you've successfully SSH'd into the site you need to enter the following lines and hit return.
[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Finally restart MySQL by entering the following.
sudo service mysql restart
Refresh your Craft powered website and all is good. Now instead of a server response of 3.7 second Digital Ocean is responding in 0.38 seconds and I am currently using the cheapest plan.
