Common MySQL errors and how to fix them
While MySQL error codes are useful, understanding what they mean can be difficult. This article looks at common MySQL error codes, non-coded errors, and how to fix them.
MySQL error code format#
Each MySQL error includes:
- An error number: A MySQL-specific number corresponding to a particular error.
SQLSTATEvalue: A five-character string that indicates the error condition.
- An error message: A textual description of the error.
MySQL error example
Here’s an example of what a MySQL error code looks like:
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist
In the error above:
1146is the error number.
Table test.no_such_table doesn't existis the error message.
Common MySQL error codes#
Let’s explore some common error codes, what they mean, and how to resolve them.
Error 1040: Too many connections
Error 1040 occurs when MySQL reaches the maximum number of client connections, forcing you to close connections so the server can accept new connections.
By default, MySQL can handle up to 151 connections. If needed, you can change this by editing the value held by the
max_connections variable. One approach to fixing this error is setting the
max_connections value to a number corresponding to connection usage.
For instance, if you think you need around 200 connections, you can set it to 250.
SET GLOBAL max_connections = 250;
Note that the more you increase the number of connections, the more memory-intensive MySQL gets, increasing the chance of the server crashing.
PlanetScale, a MySQL-compatible database, supports nearly unlimited connections. You can sign up for a free account today to get increased connections, no downtime schema changes, an advanced query monitoring dashboard, and more.
Error 1045: Access denied
Error 1045 occurs when a user is denied permission to perform operations such as
DELETE on the database. Below is a list of some reasons why MySQL denies access and possible fixes.
- The user doesn’t exist. Check if the user exists in the database, and if they don’t, create a new user.
- The password is incorrect. To fix this, reset the MySQL password.
- Connecting to the wrong host. Double check that the host you’re connecting to is correct.
Error 1064: Syntax error
SQL syntax issues are often to blame for this error, such as reserved keywords, missing data, or mistyped commands. The best way of identifying the syntax issue is by comparing the query with the error message to identify the specific point in the query that raised the error. To troubleshoot this error:
- Proofread your code and correct mistyped commands.
- If you have to use reserved keywords, place them in backticks like this:
- Replace obsolete commands with current ones.
- Add missing data to the database.
- Use an automated syntax checker, like EverSQL.
Error 1114: Table is full
Error 1114 occurs when you try to insert data into a table due to a shortage of disk memory. Disk full issues can also occur when creating a backup for large databases alongside the original database.
To fix this error, check the partition in which the MySQL server is installed and ensure it’s less than 80% full.
Error 2006: MySQL server connection closed
Error 2006 occurs when the MySQL server connection has timed out and closed the connection. The
wait_timeout value determines how long the server waits before closing a connection due to inactivity. To fix this, check the
wait_timeout value (28800 seconds by default) and increase it if it’s too low.
Error 2008: Client ran out of memory
This error message means there’s not enough memory to store the entire query result. To solve this problem, check the specifics of the query. Do you need to return this many results from the database? If not, modify the query to return only the necessary rows.
Error 2013: Lost connection during query
Error 2013 occurs when the connection drops between the MySQL client and the database server, usually because the database took too long to respond.
To fix the error, first, check that your internet connection is stable. Delayed results may be due to network connectivity issues. Also, try increasing the
net-read-timeout value to give more time for the query to complete.
In addition to coded errors, there are several common non-coded MySQL errors that you might encounter.
Packet too large
The maximum possible size of a pocket transmitted to or from a MySQL 8.0 server or client is 1GB. The
max_allowed_packet variable stores the allowable packet size.
For the client, the default
max_allowed_packet value is 16MB and for the server is 64MB. To fix this error, increase the
max_allowed_packet value for the client and the server.
For example, increase the
max_allowed_packet for the client to 32MB:
Note that MySQL needs to restart for the change to take effect.
Can’t create/write file
You’ll get this error when MySQL can’t create the temporary file for the result in the temporary directory. This might be because there’s no memory left in the
/tmp folder, or if there’s an incorrect configuration that doesn’t allow MySQL to write in the
To solve the memory issue, try starting the MySQL server with the
--tmpdir option and specifying a directory for the server to write to. For example, to specify C:/temp:
If the configuration is incorrect, make sure MySQL has permission to write to the directory specified by
Commands out of sync
commands out of sync error occur when you call client functions in the wrong order. For example, using
mysql_use_result() before calling
mysql_free_result() will raise this error.
To fix this error, check your functions and make sure you are calling them in the correct order.
Hostname is blocked
This error occurs when the MySQL server receives too many connections that have been interrupted by the host. The server assumes something is wrong, like someone trying to break in, and blocks the hostname until you execute the
The number of interrupted connect requests is determined by the
max_connect_errors variable, 10 by default. Modify the value by starting the MySQL server like this:
This error occurs when clients attempt and fail to connect to the MySQL server, often due to the client using incorrect credentials or lacking access privileges.
To fix this error, start by checking the error logs and general logs at
/var/log/mysql/ to determine the cause of the aborted connections.
Error handling can be exhausting and time-consuming, so it's important to understand how to fix the common MySQL errors. If you're looking for a straightforward, developer-friendly way to run MySQL, try PlanetScale. You can import your application's existing database with no downtime using our Import tool and be up and running in no time.