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.
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)”.
Can’t Connect to Local MySQL Server
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.
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.
To verify the MySQL service status, use the following command.
Check MySQL Status
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.
Restart MySQL and Verify Status
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.
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:
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.
You can grant privileges to a particular user on specific database to the remote ip address using the following commands in the MySQL shell.
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.
If the MySQL configuration file is not accessible for you, then you can set this value using the following command in the MySQL shell.
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.
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.
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.
Alternatively, run the following mysqladmin command to find uptime of MySQL server.
Find MySQL Server Uptime
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:
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:
- 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.
- 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.
- You can also examine system log file such as /var/log/messages or similar for reasons for your problem.
- 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.
- 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.
- 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.
- 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:
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.
If You Appreciate What We Do Here On TecMint, You Should Consider:
TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.
If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.
We are thankful for your never ending support.
Troubleshooting Connection Issues
If you are completely new to MariaDB and relational databases, you may want to start with the MariaDB Primer. Also, make sure you understand the connection parameters discussed in the Connecting to MariaDB article.
There are a number of common problems that can occur when connecting to MariaDB.
Server Not Running in Specified Location
If the error you get is something like:
the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see Getting, Installing and Upgrading MariaDB, Starting and Stopping MariaDB or Troubleshooting Installation Issues.
The socket file can be in a non-standard path. In this case, the socket option is probably written in the my.cnf file. Check that its value is identical in the [mysqld] and [client] sections; if not, the client will look for a socket in a wrong place.
If unsure where the Unix socket file is running, it’s possible to find this out, for example:
Unable to Connect from a Remote Location
Usually, the MariaDB server does not by default accept connections from a remote client or connecting with tcp and a hostname and has to be configured to permit these.
Note that from MariaDB 10.4.3, the unix_socket authentication plugin is enabled by default on Unix-like systems. This uses operating system credentials when connecting to MariaDB via the local Unix socket file. See unix_socket authentication plugin for instructions on connecting and on switching to password-based authentication as well as Authentication from MariaDB 10.4 for an overview of the MariaDB 10.4 changes..
Authentication is granted to a particular username/host combination. user1’@’localhost’ , for example, is not the same as user1’@’220.127.116.11′ . See the GRANT article for details on granting permissions.
Passwords are hashed with PASSWORD function. If you have set a password with the SET PASSWORD statement, or used INSERT or UPDATE to update the permissions table directly, the PASSWORD function must be used at the same time. For example, SET PASSWORD FOR ‘bob’@’%.loc.gov’ = PASSWORD(‘newpass’) rather than just SET PASSWORD FOR ‘bob’@’%.loc.gov’ = ‘newpass’ ;
If grant tables have been changed directly, the new passwords or authentication data will not immediately be active. A FLUSH PRIVILEGES statement, or the flush-privileges mysqladmin option must be run in order for the changes to take effect.
Problems Exporting Query Results
If you can run regular queries, but get an authentication error when running the SELECT . INTO OUTFILE, SELECT . INTO DUMPFILE or LOAD DATA INFILE statements, you do not have permission to write files to the server. This requires the FILE privilege. See the GRANT article.
Access to the Server, but not to a Database
If you can connect to the server, but not to a database, for example:
or can connect to a particular database, but not another, for example mysql -u name db1 works but not mysql -u name db2 , you have not been granted permission for the particular database. See the GRANT article.
Option Files and Environment Variables
It’s possible that option files or environment variables may be providing incorrect connection parameters. Check the values provided in any option files read by the client you are using (see mysqld Configuration Files and Groups and the documentation for the particular client you’re using — see Clients and Utilities).
Option files can usually be suppressed with no-defaults option, for example:
Unable to Connect to a Running Server / Lost root Password
If you are unable to connect to a server, for example because you have lost the root password, you can start the server without using the privilege tables by running the —skip-grant-tables option, which gives users full access to all tables. You can then run FLUSH PRIVILEGES to resume using the grant tables, followed by SET PASSWORD to change the password for an account.
localhost and %
You may have created a user with something like:
This creates a user with the ‘%’ wildcard host.
However, you may still be failing to login from localhost. Some setups create anonymous users, including localhost. So the following records exist in the user table:
Since you are connecting from localhost, the anonymous credentials, rather than those for the ‘melisa’ user, are used. The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.