Common MySQL errors and how to fix them
By Mary Gathoni |
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.
- An
SQLSTATE
value: 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:
1146
is the error number.422s02
is theSQLSTATE
value.Table test.no_such_table doesn't exist
is 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 an 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 SELECT
, INSERT
, UPDATE
, and 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:
INSERT
. - 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 <code>wait_timeout</code> value determines how long the server waits before closing a connection due to inactivity. To fix this, check the <code>wait_timeout</code> 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 <code>net-read-timeout</code> value to give more time for the query to complete.
Non-coded errors
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 <code>max_allowed_packet</code> 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:
mysql --max_allowed_packet=32M
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 /tmp
folder.
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:
tmpdir=C:/temp
If the configuration is incorrect, make sure MySQL has permission to write to the directory specified by tmpdir
.
Commands out of sync
The 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 flush-hosts
command.
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:
mysqld_safe --max_connect_errors=10000
Aborted connections
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.
Conclusion
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.