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

The article lists ten classic MySQL error cases, explains why they occur, and provides practical solutions.

Top 1: Too many connections

Problem reproduction

Solution ideas

Check the max_connections setting in the MySQL configuration; the default is 151. Increase it (e.g., set global max_connections=500 ) only after confirming the server can handle the load, and perform stress testing before deployment.

Limit InnoDB concurrency with innodb_thread_concurrency (e.g., set to 16 or 64) and consider using thread pools or disabling unnecessary information_schema monitoring.

Top 2: Replication errors (e.g., Last_SQL_Errno: 1062)

Problem

Duplicate primary‑key inserts on the slave cause conflicts with the master.

Solution

Skip the offending query on the slave using tools like pt‑slave‑restart, then enable read_only on the slave to prevent further writes.

Top 3: Installation errors (Permission denied)

Solution

Ensure the MySQL data directory has proper permissions; during initialization add the --user=mysql flag to avoid permission‑related failures.

Top 4: Forgotten root password

Solution

Reset the password by starting MySQL with the --skip-grant-tables option, then update the mysql.user table.

Top 5: TRUNCATE resets auto‑increment

Explanation

TRUNCATE removes all rows and resets the auto‑increment counter to 1, causing “not found” errors when the application expects higher IDs.

Recommendation

Avoid TRUNCATE for regular data cleanup; use DELETE instead.

Top 6: Alibaba Cloud MySQL parameter

Top 7: Chinese character garbled output

Solution steps

1. Set the client tool to use UTF‑8. 2. Ensure the OS locale is UTF‑8 (e.g., check /etc/sysconfig/i18n). 3. Add character-set-server=utf8 under the mysqld section in my.cnf.

Top 8: binlog_format=statement causing data loss on cross‑database operations

Top 9: Connection timeout (wait_timeout / interactive_timeout)

Problem

Both parameters default to 28800 seconds (8 hours); after this period MySQL closes idle connections, causing application errors if not handled.

Solution

Adjust the timeout values to suit production workloads and ensure application code checks and closes connections properly.

Top 10: can’t open file (errno:24) – too many open files

Investigation

Check MySQL error log; the issue is usually the OS or MySQL open‑files limit.

Solution

Run show variables like 'open_files_limit'; and increase the limit in MySQL configuration and the OS (e.g., via ulimit -n).