Top 10 MySQL Errors and How to Fix Them: Practical Solutions for DBAs

Below is a curated list of the most frequent MySQL error cases, each accompanied by a concise analysis of the root cause and step‑by‑step remediation commands, aimed at helping newcomers and seasoned DBAs troubleshoot efficiently.

Top 1: Too many connections

When the number of client connections exceeds max_connections (default 151), new connections are rejected with error 1040.

mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

mysql> set global max_connections=500;
Query OK, 0 rows affected (0.00 sec)

# Example of failure
[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132
ERROR 1040 (HY000): Too many connections

Adjust max_connections based on expected load, but verify the server can handle the increased pressure; perform load testing before deployment. Also consider limiting InnoDB concurrency (e.g., innodb_thread_concurrency=16) and enabling thread pooling.

Top 2: Replication error – duplicate primary key (Error 1062)

The slave reports Last_SQL_Errno: 1062 when an INSERT on the master conflicts with existing data on the slave.

Last_Errno: 1062   Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY'

Solution: Skip the offending event using pt‑slave‑restart from Percona Toolkit, then set the slave to read‑only.

# ./pt-slave-restart -uroot -proot123 2017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002 1506 1062

Top 3: Installation error – missing index file

During mysqld_safe startup, the error log may show

File '/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)

, causing the server to abort.

Fix: Ensure the MySQL data directory has correct ownership ( chown -R mysql:mysql /data/mysql) and restart the service.

# chown mysql:mysql -R /data/mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

Top 4: Forgotten root password

If the root password is lost, start MySQL with --skip-grant-tables to bypass authentication, then reset the password.

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('new_root_pwd') WHERE User='root';

Top 5: TRUNCATE resets auto‑increment

Using TRUNCATE removes all rows and resets the auto‑increment counter to its start value, causing missing primary‑key lookups on the front end.

# Create table with auto_increment starting at 300
CREATE TABLE t (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(20), PRIMARY KEY(a)) ENGINE=InnoDB AUTO_INCREMENT=300;
INSERT INTO t (b) VALUES ('aa'), ('bb'), ('cc');
TRUNCATE TABLE t;  -- auto_increment resets to 1

Recommendation: Prefer DELETE FROM t when you need to clear data without resetting the counter.

Top 6: lower_case_table_names setting

On Alibaba Cloud MySQL, ensure lower_case_table_names matches your naming convention; set to 0 for case‑sensitive names or 1 for case‑insensitive.

lower_case_table_names=0   # case‑sensitive (default on Linux)

Top 7: Chinese character garbled output

Three layers must use UTF‑8: the client tool, the OS locale, and the MySQL server configuration ( character-set-server=utf8).

# In /etc/my.cnf under [mysqld]
character-set-server=utf8

For emoji support, use utf8mb4:

init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

Top 8: binlog_format=statement causing data loss

When binlog_format=statement is used, cross‑database statements may not replicate. Use binlog_format=row and avoid binlog-do-db unless necessary.

# Current format
binlog_format=statement
# Recommended
binlog_format=row

Top 9: Connection timeout (wait_timeout / interactive_timeout)

MySQL closes idle connections after wait_timeout (default 28800 s). Applications that reuse stale connections encounter errors like MySQLNonTransientConnectionException.

Solution: Align both timeout variables, increase them if appropriate, and ensure the application validates or closes connections properly.

Top 10: "Can't open file (errno: 24)" – too many open files

The error indicates the OS limit on open file descriptors is reached. Check open_files_limit in MySQL and increase it if necessary.

show variables like 'open_files_limit';
-- If low, set a higher value, e.g., 2048, and restart MySQL

Also verify disk space, file permissions, and run perror 24 to confirm the OS error.