Top 10 MySQL Error Cases and How to Fix Them

Case 1: Too many connections

The server reports ERROR 1040 (Too many connections) when the number of client connections exceeds max_connections (default 151). Check the current value with show variables like '%max_connection%'; and increase it, e.g. set global max_connections=500;. Adjust innodb_thread_concurrency (e.g., to 16) and optionally disable metadata statistics collection with set global innodb_stats_on_metadata=0;. Remember that raising limits without capacity testing may cause server crashes.

Default max_connections is 151; set according to workload.

Monitor server load after changes.

Case 2: Master‑Slave replication errors

Duplicate entry (Error 1062) on the slave occurs when the same primary‑key row is inserted on both master and slave. Skip the offending event with pt-slave-restart -uroot -pYOUR_PASSWORD from the Percona Toolkit, then enable read_only on the slave.

Server‑id conflict (Error 1593) happens when both master and slave share the same server-id. Assign unique IDs, typically using the last octet of the IP plus the MySQL port.

Missing rows on slave (Error 1032) indicates the slave cannot find a record referenced by an UPDATE. Locate the offending binlog position with

mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 | grep -A 10 1708 > 1.log

, extract the SQL, and replay it on the slave.

Case 3: MySQL startup error – permission denied

The error log shows

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

. Fix by correcting ownership: chown mysql:mysql -R /data/mysql and restart MySQL with

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

.

Case 4: Forgotten root password

Start MySQL without privilege checks:

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

. Then change the password:

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

and restart the server.

Case 5: TRUNCATE resets auto‑increment

Creating a table with AUTO_INCREMENT=300, inserting three rows, then executing truncate table t; resets the auto‑increment counter to 1. Subsequent inserts start from 1, causing “record not found” errors in applications that rely on the original IDs. Prefer delete from t; if you need to keep the auto‑increment sequence.

Case 6: Alibaba Cloud MySQL configuration

The lower_case_table_names parameter controls case sensitivity. Use 0 for case‑sensitive names or 1 for case‑insensitive names, and ensure MyBatis mapper files match the chosen setting.

Case 7: Chinese character garbling

Typical cause is mismatched character sets. Ensure the client tool, OS locale, and server configuration all use UTF‑8 (or UTF‑8 mb4). Add to my.cnf under [mysqld]:

character-set-server=utf8mb4

init-connect='SET NAMES utf8mb4'

Case 8: binlog_format=statement leads to data loss

When binlog_format=statement and binlog-do-db=mydb1 are set, statements executed on a different database (e.g., use mydb2; insert into mydb1.t1 …) are not replicated, causing missing data on the slave. Use binlog_format=row in production and avoid binlog-do-db unless absolutely necessary.

Case 9: Connection timeout

MySQL closes idle connections after wait_timeout (default 28800 seconds). Errors such as SQL Error:0, SQLState:08S01 appear when the application reuses a stale connection. Align wait_timeout and interactive_timeout values, increase them if appropriate, and ensure the application closes connections properly.

Case 10: Too many open files (errno:24)

The error Can't open file (errno:24) indicates the OS limit on open files is reached. Verify the limit with ulimit -n and increase MySQL's open_files_limit (e.g., set global open_files_limit=2048;). Repair affected tables with repair table <table_name>;, adjust file permissions, and clean up disk space.