Database problems

DATABASE NOT AVAILABLE

1. Check MySQL

- connect to the server via SSH and run:

service mysql status

- the output should contain running, if not, start the service:

service mysql start

- after starting MySQL, check the site. If it doesn't work, go to the next step.

2. Check disk space

- check for free space:

df -h

- if there is not enough free space on the disk - you can expand the disk or delete unnecessary files. For a detailed analysis of the disk space state and deleting files, it is better to use special utilities du or ncdu.

- if there is free space on the disk, but applications displays an error about not enough space - check inodes.  

RESTORE DB TABLES  

If the site displays errors Warning: Table './database/table' is marked as crashed

repair damaged tables.               

- If phpMyAdmin is installed on your VPS server, go to its interface and select the required database. Highlight the tables that were reported corruption in errors. From the Marked menu, select Recover.

- You can also restore tables from the console.

Connect to the server via SSH. To restore a table_name in base_name, run:

mysqlcheck -r base_name table_name -uroot –p

To restore all tables in the base_name database run:

mysqlcheck -r base_name -uroot -p

To check all tables in the database, run:

mysqlcheck -r -A -uroot -p

MYSQL SERVER HAS GONE AWAY (ERROR 2006)

This error indicates that the server has closed the connection. This could be due to a timeout or an oversized packet received.

To fix the error, connect to the server via SSH. The configuration file can be located in various directories, for example:

/etc/my.cnf

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

/etc/mysql/my.cnf

To find the file with the required settings, use the following command:

grep -Rl 'name' /etc/*

The command to search for a file with settings for the acceptable size of received packets:

grep -Rl 'max_allowed_packet' /etc/*

The command to search for a file with timeout settings:

grep -Rl 'wait_timeout' /etc/*

1. Timeout increase

Open the configuration file using an editor.

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

Increase wait_timeout. The timeout in the configuration file is specified in seconds. To set a timeout of 5 minutes, specify a value of 300:

wait_timeout = 300

Restart MySQL:

service mysql restart

2. Increasing the allowed packet size

Change the max_allowed_packet parameter. Open the configuration file using an editor.

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

Increase max_allowed_packet as needed. The maximum allowable packet size is indicated in megabytes. To set the maximum packet size to 128 megabytes, enter:

max_allowed_packet = 128M

Restart MySQL to apply changes:

service mysql restart

TOO MANY CONNECTIONS (ERROR 1040)

This error indicates that the maximum number of connections to the database has been reached. Reasons for the error:

1. Slow queries that take a long time. Only optimization of the database code can solve this problem.

2. A large number of concurrent connections. This problem can be resolved by changing the maximum connections setting in the MySQL configuration file.

Find the path to the file containing the parameter max_connections:

grep -Rl 'max_connections' /etc/*

Increase the maximum number of concurrent connections (for example, up to 150):

max_connections = 150

Restart MySQL to apply changes:

service mysql restart

INCORRECT DATE VALUE (ERROR 1292)

This error can be caused to a MySQL table without specifying a date.

1. Open the configuration file to find the error. For example:

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

2. On the sql-mode = line, remove:

NO_ZERO_IN_DATE




NO_ZERO_DATE




STRICT_ALL_TABLES

3. If the sql-mode = line is missing in the config file, add the following line:

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

4. Restart MySQL to apply changes:

service mysql restart

If you have any questions, please create a ticket to technical support.