Useful Tips to Troubleshoot Common Errors in MySQL

MySQL is a widely used open source relational database management system (RDMS) owned by Oracle. It has over the years been the default choice for web-based applications and still remains popular in comparison to other database engines.

Read Also: How to Install Latest MySQL on RHEL/CentOS and Fedora

MySQL was designed and optimized for web applications – it forms an integral part of major web-based applications such as Facebook, Twitter, Wikipedia, YouTube, and many others.

Is your site or web application powered by MySQL? In this detailed article, we will explain how to troubleshoot problems and common errors in MySQL database server. We will describe how to determine the causes of the problems and what to do to solve them.

1. Can’t Connect to Local MySQL Server

One of the common client to server connection errors in MySQL is “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”.

This error indicates that there is no MySQL server (mysqld) running on the host system or that you have specified a wrong Unix socket file name or TCP/IP port when trying to connect to the server.

Ensure that the server is running by checking a process named mysqld on your database server host using the ps command and grep command together as shown.

$ ps xa | grep mysqld | grep -v mysqld

If the above commands show no output, then the database server isn’t running. Therefore the client can’t connect to it. To start the server, run the following systemctl command.

$ sudo systemctl start mysql        #Debian/Ubuntu
$ sudo systemctl start mysqld       #RHEL/CentOS/Fedora

To verify the MySQL service status, use the following command.

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

From the output of the above command, the MySQL service has failed. In such a case, you can try to restart it and check its status once more.

$ sudo systemctl restart mysql
$ sudo systemctl status mysql

In addition, if the server is running as shown by the following command, but you still see the above error, you should also verify that the TCP/IP port is blocked by a firewall or any port blocking service.

$ ps xa | grep mysqld | grep -v mysqld

To find the port the server is listening on, use the netstat command as shown.

$ sudo netstat -tlpn | grep "mysql"

2. Can’t Connect to MySQL Server

Another commonly encountered connection error is “(2003) Can’t connect to MySQL server on ‘server’ (10061)”, which means that the network connection has been refused.

Here, start by checking that there is a MySQL server running on the system as shown above. Also ensure that the server has network connections enabled and that the network port you are using to connect is the one configured on the server.

Other common errors you are likely to encounter when you try to connect to the MySQL server are:

ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

These errors indicate that the server might be running, however, you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening.

3. Access Denied Errors in MySQL

In MySQL, a user account is defined in terms of a username and the client host or hosts from which the user can connect to the server. In addition, an account may also have authentication credentials such as a password.

Although there are many different causes of “Access denied” errors, one of the common causes is relating to the MySQL accounts that the server permits client programs to use when connecting. It indicates that username specified in the connection does not have privileges to access the database.

MySQL allows the creation of accounts that enable client users to connect to the server and access data managed by the server. In this regard, if you encounter an access denied error, check if the user account is allowed to connect to the server via the client program you are using, and possibly the host from which the connection is coming from.

You can see what privileges a given account has by running the SHOW GRANTS command as shown.

> SHOW GRANTS FOR 'tecmint'@'localhost';

You can grant privileges to a particular user on specific database to the remote ip address using the following commands in the MySQL shell.

> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100';
> flush privileges;

Furthermore, access denied errors can also result from problems with connecting to MySQL, refer to the previously explained errors.

4. Lost Connection to MySQL Server

You may encounter this error due to one of the following reasons: poor network connectivity, connection timeout or a problem with BLOB values that are larger than max_allowed_packet. In case of a network connection problem, ensure that you have a good network connection especially if you are accessing a remote database server.

If it is a connection timeout problem, particularly when MySQL is trying to use an initial connection to the server, increase the value of the connect_timeout parameter. But in case of BLOB values that are larger than max_allowed_packet, you need to set a higher value for the max_allowed_packet in your /etc/my.cnf configuration file under [mysqld] or [client] section as shown.

[mysqld]
connect_timeout=100
max_allowed_packet=500M

If the MySQL configuration file is not accessible for you, then you can set this value using the following command in the MySQL shell.

> SET GLOBAL connect_timeout=100;
> SET GLOBAL max_allowed_packet=524288000;

5. Too Many MySQL Connections

In case a MySQL client encounters the “too many connections” error, it means that all available connections are in use by other clients. The number of connections (default is 151) is controlled by the max_connections system variable; you can remedy the problem by increasing its value to permit more connections in your /etc/my.cnf configuration file.

[mysqld]
max_connections=1000

6. Out of Memory MySQL

In case you run a query using the MySQL client program and encounter the error in question, it means that MySQL does not have enough memory to store the entire query result.

The first step is to ensure that the query is correct, if it is, then do the following:

• if you are using MySQL client directly, start it with --quick switch, to disable cached results or
• if you are using the MyODBC driver, the configuration user interface (UI) has an advanced tab for flags. Check “Do not cache result“.

Another great tool is, MySQL Tuner – a useful script that will connect to a running MySQL server and gives suggestions for how it can be configured for higher performance.

$ sudo apt-get install mysqltuner     #Debian/Ubuntu
$ sudo yum install mysqltuner         #RHEL/CentOS/Fedora
$ mysqltuner

For MySQL optimization and performance tuning tips, read our article: 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips.

7. MySQL Keeps Crashing

If you encounter this problem, you should try to find out whether the problem is that the MySQL server dies or whether its the client with an issue. Note that many server crashes are caused by corrupted data files or index files.

You can check the server status to establish for how long it has been up and running.

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

Alternatively, run the following mysqladmin command to find uptime of MySQL server.

$ sudo mysqladmin version -p 

Other solutions include but not limited to stopping the MySQL server and enabling debugging, then start the service again. You can try to make a test case that can be used to repeat the problem. In addition, open an additional terminal window and run the following command to display MySQL process statistics while you run your other queries:

$ sudo mysqladmin -i 5 status
OR
$ sudo mysqladmin -i 5 -r status 

The Bottom Line: Determining What Is Causing a Problem or an Error

Although we have looked at some common MySQL problems and errors and also provided ways to troubleshoot and solve them, the most important thing with diagnosing an error is understanding what it means (in terms of what is causing it).

So how can you determine this? The following points will guide you on how to ascertain what is exactly causing a problem:

1. The first and most important step is to look into the MySQL logs which are stored in the directory /var/log/mysql/. You can use command line utilities such as tail to read through the log files.
2. If MySQL service fails to start, check its status using systemctl or use the journetctl (with the -xe flag) command under systemd to examine the problem.
3. You can also examine system log file such as /var/log/messages or similar for reasons for your problem.
4. Try using tools such as Mytop, glances, top, ps, or htop to check which program is taking all CPU or is locking the machine or to inspect whether you are running out of memory, disk space, file descriptors, or some other important resource.
5. Assuming that problem is some runaway process, you can always try to kill it (using the pkill or kill utility) so that MySQL works normally.
6. Supposing that the mysqld server is causing problems, you can run the command: mysqladmin -u root ping or mysqladmin -u root processlist to get any response from it.
7. If the problem is with your client program while trying to connect to the MySQL server, check why it is not working fine, try to get any output from it for troubleshooting purposes.

You might also like to read these following MySQL related articles:

1. Learn MySQL / MariaDB for Beginners – Part 1
2. How to Monitor MySQL/MariaDB Databases using Netdata on CentOS 7
3. How to Transfer All MySQL Databases From Old to New Server
4. Mytop – A Useful Tool for Monitoring MySQL/MariaDB Performance in Linux
5. 12 MySQL/MariaDB Security Best Practices for Linux

For more information, consult the MySQL Reference manual concerning Problems and Common Errors, it comprehensively lists common problems and error messages that you may encounter while using MySQL, including the ones we have discussed above and more.