Database connection errors – steps to fix

Following the February 10 power outage on 123 Reg systems, some of our VPS customers may see an issue on their website similar to "Database connection error". 

Chances are this happened due to a MySQL corruption. At the moment, the fastest way to resolve this would be to attempt to repair the database. if that fails you may need to rebuild the server and have it restored from your backups/

The underlying issue is most likely related to a crash related to an unclean shut down. This can be recovered, however, and after taking a backup you can resolve this by doing the following:

  1. Open the /etc/my.cnf file (MySQL’s config file)
  2. It should look similar to this (however options may vary):

    [mysqld]
    innodb_file_per_table=1
    open_files_limit=2164
    max_allowed_packet=268435456

  3. Add this line to the end:

    innodb_force_recovery = 1

  4. Try starting MySQL again.
  5. If it fails, change the line you added to my.cnf to:

    innodb_force_recovery = 2

  6. Try restarting MySQL again.
  7. If it still fails, change the line you added to my.cnf to:

    innodb_force_recovery = 3

  8. You can keep going up to 6 if needed however this should be a last resort as anything over 3 can cause data loss
  9. As soon as you get MySQL started, take a full backup of your databases. MySQL is now is read only recovery mode so your sites may not be online but you should take a copy of your data as soon as possible.

    # mysqldump --all-databases --lock-tables
    --compress --routines --comments --extended-insert > new_all_databases.sql
    -- Warning: Skipping the data of table
    mysql.event. Specify the --events option explicitly.

  10. Log in as the root MySQL user, check the DB, exit, stop MySQL and remove the “innodb_force_recovery = […]” line from my.cnf.
  11. Start MySQL one more time. It should now be running normally
You may need to repair corrupted tables using the following method:-

mysql> USE databasename;

Database changed

Now you can issue the repair operation:-

mysql> REPAIR TABLE `table1` , `table2`;

The above should work for you and will allow MySQL to start and run however once you do this get this started. There is more information regarding this here --> http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

The above guide should correct any issues, however if the above fails to work or if you may need to restore from your backups.

If the recovery process fails, and you do not have backups please raise a ticket on our support page and we will attempt to recover your data. Please understand however that we have an extremely large backlog of tickets and we are handling them in chronological order.