How do I diagnose a unstable installation on ubuntu 16 server?

Question

I'm deploying a site to a Digital Ocean Ubuntu 16 server. I'm using PHP 5.6, but otherwise using the default versions of the necessary database software. The site functions; however, I frequently have database related errors, specifically, the following one:

PDOException: SQLSTATE[HY000] [2002] Connection refused in lock_may_be_available() (line 167 of /var/www/html/phisigmarho.org/includes/lock.inc).

I usually get that error, but I also get "the website experienced an error" errors when I to do anything database or process intensive (updating modules through the UI, etc). I'm relatively new server administration, so I'm not sure what could be wrong or which logs I should be looking at for clues to what's wrong. For the moment, restarting MySQL from the terminal is letting me move forward with setting up the site, but I can't let anyone use the site the way that it is now.

How do I figure out why MySQL keeps going away?

Answer 1

Unless you are using Drupal 6, I suggest you start again from scratch and use/install PHP 7.

Drupal 8 and 7 (since 7.50) support PHP 7, which is twice as fast as PHP 5.

sudo apt-get update
sudo apt-get install apache2
sudo apt-get install curl
sudo apt-get install mysql-server
sudo mysql_secure_installation

Would you like to setup VALIDATE PASSWORD plugin? n

Change the password for root ? n

Now, for the rest of the questions put y

sudo apt-get install php php-cli php-curl php-gd php-mbstring php-mcript
a2enmod rewrite
sudo service apache2 restart

Now, Install and Secure phpMyAdmin and create a database with and a user with privileges. Do a fresh Drupal install and make sure you get no errors in the install screens.

Once all ok, dump all your DB tables and import your site. Make sure your Drupal directory contains all your modules and the files folder contains all your images and files of your importing site.

Answer 2

Just to expand: part of the issue here was that the MySQL server kept crashing. It was difficult to diagnose initially because the apache and php logs didn't tell me much about why the server was failing. It is a fairly large site with a large number of modules. I finally checked the right Mysql logs and determined that the server was failing due to a memory failure due to the size of my Digital Ocean Droplet. (512mb VPS). My research showed that there are the following solutions: 1) reduce the load to the server and run a low-memory mysql installation; 2) create a swap to better process the requests with the available ram; 3) increase the amount of ram on the server. I chose the third option and it seems to have fixed the problem. I am currently going with php7, but I assume I could also use php5.6 with the ram issue solved.