First of all, this article is prepared for advanced users. Please follow the steps only if you know what are you doing. In WordPress CMS the database is the most important part so in case of database component crash you site is literally ruined. Being a database-driven web application, everything that you do on your WordPress powered website is just read-and-write processes in/out database files.
Sometimes there could be a fault during the input/output operations with database and this means that one or several tables become corrupted (in plain words, your data is not retrievable or writeable) and your website going down with no access to front-end and back-end.
Let’s see what should we apply step-by-step to fix this worst scenario and restart your WordPress website as soon as possible.
Diagnostics
Check mysql user credentials in wp-config.php and if everything is okay (no typos) follow the next step with creating a special file to check the mysql connection.
This simple php-script is perfect for testing and troubleshooting a connection to a MySQL database. The PHP script will test the server address, username and password. If the database is available to connect, it will return a list of available tables (numbers) in your database.
The script is below, adjust it with your database name / user / password / connection.
# Fill the vars and run on cli
# $ php -f db-test.php
$dbname = 'name';
$dbuser = 'user';
$dbpass = 'pass';
$dbhost = 'host';
$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
mysql_select_db($dbname) or die("Could not open the db '$dbname'");
$test_query = "SHOW TABLES FROM $dbname";
$result = mysql_query($test_query);
$tblCnt = 0;
while($tbl = mysql_fetch_array($result)) {
$tblCnt++;
#echo $tbl[0]."
\n";
}
if (!$tblCnt) {
echo "There are no tables
\n";
} else {
echo "There are $tblCnt tables
\n";
}
Suppose we got an output and connection was successful. In this case your WordPress installation is possibly involved with tables corruption issue. To fix this issue we have to check what table is corrupted and repair it.
Repairing via WP built-in system
If the error message is the same on both pages “Error establishing a database connection”, then proceed onto the next step. (skipped) The database may need to be repaired”, then you need to repair your database. You can do this by adding the following line in your wp-config.php file.
define('WP_ALLOW_REPAIR', true);
After editing wp-config.php point your browser to the URL:
http://www.yoursite.com/wp-admin/maint/repair.php
* yourwebsite.com should be replaced with your website URL
You will see WP logo and should click on ‘Repair Database’ button. This will activate a script which will search and repair a corrupted database, users can often not login when the database is corrupt, so once you are done repairing and optimizing your database, make sure to remove this string away from your wp-config.php file.
In our case we got result that ‘wp_options’ table is corrupted and can not be repaired. This is the worst scenario could be. But we have an extra rabbit in the hat 😉
Login to your hosting panel (basically ISP manager panel).
Panel repairing and uploading new dumps
In the panel select databases and activate ‘Repair’ button with the database name to apply. After repairing download the dump file and select databases again. Create a new database and import the downloaded dump file into this new blank database.
Adjust connection data in wp-config.php file with your new database/user/password. Check website and see it is now running.
What is this story about?
This teach us to do backups of your website regularly. This is where your backup database comes in useful.