Top 10 MySQL Errors Every DBA Should Know and How to Fix Them

The author shares personal experience with MySQL and presents the ten most common error cases, each with a clear problem description, diagnostic commands, and practical remediation steps.

Case 1: Too many connections

Problem

mysql> show variables like '%max_connection%'; | Variable_name | Value | | max_connections | 151 | mysql> set global max_connections=1; ERROR 1040 (00000): Too many connections

Solution: Increase max_connections (e.g., set global max_connections=500) after confirming the server can handle the load, adjust innodb_thread_concurrency if needed, and consider disabling unnecessary metadata stats ( innodb_stats_on_metadata=0).

Case 2: Master‑slave replication errors

Duplicate entry (1062)

Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table test.t;

Cause: primary‑key conflict on the slave. Fix: Use pt‑slave‑restart to skip the error and enable read_only on the slave.

Server‑id conflict (1593)

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

Ensure each server has a unique server-id (e.g., last octet of IP + port).

Missing row on slave (1032)

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

Locate the offending binlog position with mysqlbinlog, extract the original SQL, and replay it on the slave to restore consistency.

Case 3: MySQL startup errors

[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf & ... Permission denied on '/data/mysql/mysql-bin.index' ... ERROR 1040 (00000): Aborting

Fix permission on the data directory ( chown mysql:mysql -R /data/mysql) and start MySQL with the --user=mysql option.

Case 4: Forgotten root password

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &

Start MySQL without privilege checks, then reset the root password with

UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') WHERE user='root';

Case 5: TRUNCATE resets auto‑increment

After TRUNCATE TABLE t; the auto‑increment counter restarts at 1, causing missing primary‑key lookups. Use DELETE FROM t; instead if you need to preserve the counter.

Case 6: Alibaba Cloud MySQL configuration

Set lower_case_table_names=0 for case‑sensitive table names.

Set lower_case_table_names=1 for case‑insensitive names.

Adjust MyBatis mapper files accordingly.

Case 7: Chinese character garbling

Ensure UTF‑8 everywhere: client tools, OS locale, and MySQL config ( character-set-server=utf8).

For emoji errors, switch to utf8mb4:

[mysqld] init-connect='SET NAMES utf8mb4' character-set-server=utf8mb4

Case 8: Statement‑based binlog causing data loss

When binlog_format=statement and binlog-do-db filter is used, cross‑database statements may not replicate. Recommend binlog_format=row and avoid binlog-do-db in production.

Case 9: Connection timeout errors

Errors stem from mismatched wait_timeout and interactive_timeout. Align their values and consider lowering them for production, while ensuring applications close idle connections.

Case 10: "Can't open file (errno:24)"

Too many open files. Check open_files_limit and raise it (e.g., SET GLOBAL open_files_limit=2048;), then restart MySQL. Also verify file permissions and disk space.

By understanding these common pitfalls and applying the suggested fixes, developers and DBAs can maintain stable MySQL environments.