12 most common MySQL errors you should be aware of

[box type="note" align="" class="" width=""]The following excerpt is taken from the book MySQL 8 Administrator’s Guide written by Chintan Mehta, Ankit Bhavsar, Subhash Shah and Hetal Oza. This book provides tips and tricks to tackle problems you might encounter while administering MySQL solution.[/box]

While using MySQL 8 there can be few scenarios where you would not be able to access or use MySQL properly. These situations can be very annoying, but are easily fixable. However, before you look for the solution, you must know the problem! Here are some of the common errors you might come across when using MySQL 8.

1. Access denied

MySQL provides a privilege system that authenticates the user who connects from a host, and associates the user with access privileges on a database. The privileges include SELECT, INSERT, UPDATE, and DELETE and are able to identify anonymous users and grant privileges for MySQL specific functions, such as LOAD DATA INFILE and administrative operations. The access denied error may occur because of many causes. In many cases, the problem is caused because of MySQL accounts that the client programs use to connect with the MySQL server with permission from the server.

2. Lost connection to MySQL server

The lost connection to MySQL server error can occur because of one of the three likely causes explained in this section. One potential reason for the error is that the network connectivity is troublesome.

Network conditions should be checked if this is a frequent error. If an error message like “Lost connection to MySQL server” appears while querying the database, it is certain that the error has occurred because of network connection issues.

The connection_timeout system variable defines the number of seconds that the mysqld server waits for a connection packet before connection timeout response. Infrequently, this error may occur when a client is trying for the initial connection to the server and the connection_timeout value is set to a few seconds. In this case, the problem can be resolved by increasing the connection_timeout value based on the the distance and connection speed. SHOW GLOBAL STATUS LIKE and Aborted_connects can be used to determine if we are experiencing this more frequently. It can be certainly said that increasing the connection_timeout value is the solution if the error message contains reading authorization packet. It is possible that the problem may be faced because of larger Binary Large OBject (BLOB) values than max_allowed_packet. This can cause a lost connection to the MySQL server error with clients. If the ER_NET_PACKET_TOO_LARGE error is observed, it confirms that the max_allowed_packet value should be increased.

3. Password fails when entered incorrectly

MySQL clients ask for a password when the client program is invoked with the -- password or -p option without the password value. The following is the command:

> mysql -u user_name -p

Enter password:

On a few systems, it may happen that the password works fine when specified in an option file or on the command line. But it does not work when entered interactively on the Command Prompt at the Enter password: prompt. It occurs because the system-provided library to read the passwords limits the password values to a small number of characters (usually eight). It is an issue with the system library and not with MySQL. As a workaround to this, change the MySQL password to a value that is eight or fewer characters or store the password in the option file.

4. Host host_name is blocked

If the mysqld server receives too many connection requests from the host that is interrupted in the middle, the following error occurs:

Host 'host_name' is blocked because of many connection errors.

Unblock with 'mysqladmin flush-hosts'

The max_connect_errors system variable determines the number of successive interrupted connection requests that are allowed. Once there are max_connect_errors failed requests without a successful connection, mysqld assumes that something is wrong and blocks the host from further connections until the FLUSH HOSTS statement or mysqladmin flush-hosts command is issued.

mysqld blocks a host after 100 connection errors as a default. It can be adjusted by setting the max_connect_errors value on the server startup, as follows:

> mysqld_safe --max_connect_errors=10000

This value can also be set up at runtime, as follows:

mysql> SET GLOBAL max_connect_errors=10000;

It should be checked first that there is nothing wrong with TCP/IP connections from the host if the host_name is blocked error is received for a particular host. Increasing the value of the max_connect_errors variable does not help if the network has problems.

5. Too many connections

This error indicates that all available connection are in use for other client connections. The max_connections is the system variable that controls the number of connections to the server. The default value for the maximum number of connections is 151. We can set a larger value than 151 for the max_connections system variable to support more connections than 151.

The mysqld server process actually allows one more than max_connections (max_connections + 1) value clients to connect. The additional one connection is kept reserved for accounts with CONNECTION_ADMIN or the SUPER privilege. The privilege can be granted to the administrators with access to the PROCESS privilege. With this access, the administrator can connect to the server using the reserved connection. They can execute the SHOW PROCESSLIST command to diagnose the problems even though the maximum number of client connections is exhausted.

6. Out of memory

If the mysql does not have enough memory to store the entire request of the query issued by the MySQL client program, the server throws the following error:

mysql: Out of memory at line 42, 'malloc.c'

mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)

ERROR 2008: MySQL client ran out of memory

In order to fix the problem, we must first check if the query is correct. Do we expect the query to return so many rows? If not, we should correct the query and execute it again. If the query is correct and needs no correction, we can connect mysql with the --quick option. Using the --quick option results in the mysql_use_result() C API function for fetching the result set. The function adds more load on the server and less load on the client.

7. Packet too large

The communication packet is one of the following:

A single SQL statement that the MySQL client sends to the MySQL server

A single row that is sent to the MySQL client from the MySQL server

A binary log event that is sent from a replication master server to the replication slave

A 1 GB packet size is the largest possible packet size that can be transmitted to or from the MySQL 8 server or client. The MySQL server or client issues an ER_NET_PACKET_TOO_LARGE error and closes the connection if it receives a packet bigger than max_allowed_packet bytes.

The default max_allowed_packet size is 16 MB for the MySQL client program. The following command can be used to set a larger value:

> mysql --max_allowed_packet=32M

The default value for the MySQL server is 64 MB. It should be noted that there is no harm in setting a larger value for this system variable, as the additional memory is allocated as needed.

8. The table is full

The table-full error occurs in one of the following conditions:

The disk is full

The table has reached the maximum size

The actual maximum table size in the MySQL database can be determined by the constraints imposed by the operating system on the file sizes.

9. Can't create/write to file

This indicates that MySQL is unable to create a temporary file in the temporary directory for the result set if we get the following error while executing a query:

Can't create/write to file 'sqla3fe_0.ism'

The possible workaround for the error is to start the mysqld server with the --tmpdir option. The following is the command:

> mysqld --tmpdir C:/temp

10. Commands out of sync

If the client functions are called in the wrong order, the commands out of sync error is received. It means that the command cannot be executed in the client code. As an example, if we execute mysql_use_result() and try to execute another query before executing mysql_free_result(), this error may occur. It may also happen if we execute two queries that return a result set without calling the mysql_use_result() or mysql_store_result() functions in between.

11. Ignoring user

The following error is received when an account in the user table is found with an invalid password upon the mysqld server startup or when the server reloads the grant tables:

Found wrong password for user 'some_user'@'some_host'; ignoring user

The account is ignored by the MySQL permission system as a result. To fix the problem, we should assign a new valid password for the account.

12. Table tbl_name doesn't exist

The following error indicates that a specified table does not exist in the default database:

Table 'tbl_name' doesn't exist

Can't find file: 'tbl_name' (errno: 2)

In some cases, the user may be referring to the table incorrectly. It is possible because the MySQL server uses directories and files for storing database tables. Depending upon the operating system file management, the database and table names can be case sensitive.

For non case-sensitive file systems, such as Windows, the references to a specified table used within a query must use the same letter case.

In addition to these, you might come across MySQL 8 server errors such as issue with permission, or client errors like problem with NULL values. To know how to deal with them, you may check out this book MySQL 8 Administrator’s Guide.