Troubleshooting Database Problems

In this article, we will look at problems that may occur with your database and provide recommendations on how to fix them.

Problem: The database is unavailable

If your database is unavailable, follow these instructions to diagnose and fix the problem.

1. Check whether the MySQL service is running:

service mysql status

Example output for a running service:

If the output does not show the service as running, the MySQL service is not started. In this case, try to start it with the following command:

service mysql start

Then check whether your website is available.

If the problem persists, move on to the next step.

2. Check the disk space status.

First, run the command to see the total and used disk space:

df -h

Pay attention to the available space on the main partition. If it is exhausted, you need to either expand the disk or delete unnecessary files to free up space. To manage disks and free up space on them, you can use the ncdu or du utilities.

If there is enough free disk space but the problems continue, move on to checking the inode status.

If you find this task difficult, feel free to contact our support team for help.

Problem: Corrupted database tables (Table is marked as crashed)

If you get an error like "Warning: Table ... is marked as crashed", you need to repair the tables.

If phpMyAdmin is installed on your server, do the following:

1. Open the phpMyAdmin interface and select the database you need in the left-hand menu.
2. Select the tables that need to be repaired (that is, the tables whose names appear in the errors).
3. At the bottom of the page, choose "With selected" and then "Repair table".

You can also repair tables without phpMyAdmin by connecting to the server via SSH:

To repair a single table, use the following command:

mysqlcheck -r database_name table_name -uroot -p

To repair all tables in a database, run the following command:

mysqlcheck -r database_name -uroot -p

You can also check all tables in all databases with the command:

mysqlcheck -r -A -uroot -p

Problem: Error 2006 - MySQL server has gone away

The "MySQL server has gone away" error means that the server closed the connection. This usually happens because of a timeout or because the server received a data packet that was too large.

To solve this problem, you will need to make changes to the MySQL configuration file. You can do this by connecting to the server via SSH or using the web console in the control panel.

The configuration file may be located at different paths, for example:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

To find out which file contains the parameter you need, run the following command:

grep -Rl 'parameter_name' /etc/*

For example:

grep -Rl 'wait_timeout' /etc/* 
grep -Rl 'max_allowed_packet' /etc/*

This command lets you find the files that contain the parameter you need and change its value.

Problem: Timeout

To increase the connection timeout, change the wait_timeout parameter.

Open the configuration file in an editor (specify the correct path to the file):

nano /etc/mysql/my.cnf

Set the wait_timeout parameter to a higher value. The value is specified in seconds; for example, to increase the timeout to 10 minutes, set it to 600:

wait_timeout = 600

After making the changes, restart the MySQL service:

On Debian/Ubuntu:

service mysql restart

On CentOS:

service mysqld restart

Problem: Packet size

If necessary, increase the maximum allowed packet size by changing the max_allowed_packet parameter.

Open the configuration file (specify the correct path to the file):

nano /etc/mysql/my.cnf

Set the max_allowed_packet parameter to a higher value (the value is specified in megabytes), for example:

max_allowed_packet = 64M

Then restart the service:

On Debian/Ubuntu:

service mysql restart

On CentOS:

service mysqld restart

Problem: Error 1040 - Too many connections

The "Too many connections" error means that the database connection limit has been reached. This can be caused by slow queries or a large number of simultaneous connections.

To solve this problem, you can raise the connection limit by changing the max_connections parameter in the MySQL configuration file.

Find the location of the my.cnf file as described above.

Open the file in an editor (specify the correct path):

nano /etc/mysql/my.cnf

And set the max_connections parameter to a higher value, for example:

max_connections = 200

After making the changes, restart the service:

On Debian/Ubuntu:

service mysql restart

On CentOS:

service mysqld restart

Problem: Error 1292 - Incorrect date value

If you get the "ERROR 1292 (22007): Incorrect date value" error when inserting data into a MySQL table without specifying a date, make the following changes:

1. Open the /etc/mysql/my.cnf file:

nano /etc/mysql/my.cnf

2. In the line starting with sql-mode=, remove the following values:

NO_ZERO_IN_DATE
NO_ZERO_DATE
STRICT_ALL_TABLES

3. Then restart the MySQL server:

Restarting the service on Debian/Ubuntu:

sudo service mysql restart

Restarting the service on CentOS:

sudo service mysqld restart

Note: If there is no sql-mode= line, you can add it as follows:

1. Add the line to the /etc/mysql/my.cnf file after the [mysqld] section header:

sql-mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2. Then restart the MySQL server:

sudo service mysql restart