Top 10 MySQL Error Cases and How to Fix Them

The author shares personal experience of encountering numerous MySQL errors as a beginner and provides a curated list of the ten most common error cases with practical troubleshooting methods.

Case 1: Too many connections

Problem reproduction

mysql> show variables like '%max_connection%'; | Variable_name | Value | max_connections | 151 | mysql> set global max_connections=1; Query OK, 0 rows affected (0.00 sec) ERROR 1040 (00000): Too many connections

Solution approach

Check the max_connections setting in the MySQL configuration; the default is 151 and can be increased (e.g., set global max_connections=500) after ensuring the server can handle the load. Also consider adjusting innodb_thread_concurrency (e.g., to 16) and disabling unnecessary metadata statistics ( innodb_stats_on_metadata=0).

Case 2: Master‑slave replication errors

Last_SQL_Errno: 1062 (duplicate entry)

Last_Errno: 1062 Duplicate entry '4' for key 'PRIMARY'

Use pt‑slave‑restart from Percona Toolkit to skip the offending transaction, then set read_only on the slave.

Last_IO_Errno: 1593 (server‑id conflict)

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

Assign unique server-id values to each instance (e.g., last octet of IP + port).

Last_SQL_Errno: 1032 (missing row on slave)

Could not execute Update_rows event on table test.t; Can't find record in 't'

Identify the offending binlog position, extract the SQL from the master using mysqlbinlog, and replay it on the slave.

Case 3: MySQL installation errors

File '/data/mysql/mysql-bin.index' not found (Errcode: 13 – Permission denied) [ERROR] Aborting

Fix directory permissions ( chown mysql:mysql -R /data/mysql) and restart MySQL. Use --user=mysql during initialization to avoid permission problems.

Case 4: Forgotten root password

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Start MySQL with --skip-grant-tables, then update the password:

update mysql.user set password=password('newpass') where user='root';

Case 5: TRUNCATE resets auto‑increment

Using TRUNCATE clears the auto‑increment counter, causing subsequent inserts to start from 1 and potentially break front‑end lookups. Prefer DELETE for logical data removal.

Case 6: Alibaba Cloud MySQL configuration

lower_case_table_names = 0

(default, case‑sensitive) lower_case_table_names = 1 (case‑insensitive)

Adjust table names and MyBatis mapper files accordingly.

Case 7: Chinese character garbling

Ensure UTF‑8 everywhere: client tools, OS locale, and MySQL character-set-server=utf8. For emoji support, set character-set-server=utf8mb4 and init-connect='SET NAMES utf8mb4'.

Case 8: binlog_format=statement causing data loss

When using binlog_format=statement with binlog-do-db, statements executed on a different database are not replicated. Recommend switching to binlog_format=row and avoiding binlog-do-db in production.

Case 9: Connection timeout errors

Errors stem from mismatched wait_timeout and interactive_timeout values. Increase the timeout appropriately and ensure application code closes idle connections.

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

OS error code 24: Too many open files

Check MySQL's open_files_limit, raise it (e.g., to 2048), and restart. Also repair corrupted tables, adjust file permissions, and clean disk space.