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.
• 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 the SQLSTATE 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.

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.