9 Classic MySQL Errors and How to Fix Them

Case 1: Too many connections

Problem

Too many connections prevents clients from connecting to MySQL, causing business interruption.

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)

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

Solution ideas

1. Check whether max_connections is set too low. The default is 151; increase it as needed, e.g., set global max_connections=500.

The default value is 151; adjust based on workload.

Corresponding fix:

set global max_connections=500

Increasing the limit may cause server overload if the hardware cannot handle the load; perform stress testing before changing.

2. Adjust InnoDB concurrency, e.g., set innodb_thread_concurrency=0 (unlimited) or to 16/64 based on testing.

3. Use thread pool (available in MySQL 5.7+) to improve connection handling.

4. Disable metadata statistics collection if not needed:

innodb_stats_on_metadata=0

set global innodb_stats_on_metadata=0

Case 2: Master‑slave replication errors

Error type

Last_SQL_Errno: 1062 – duplicate primary key on the slave.

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

Solution

Skip the offending event on the slave using Percona Toolkit:

./pt-slave-restart -uroot -proot123

After fixing, enable read_only on the slave.

Another error Last_IO_Errno: 1593 indicates identical server-id values; assign unique IDs to master and slave, e.g., using IP last octet plus MySQL port.

Last_IO_Error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work.

Case 3: MySQL installation errors

Problem

Permission denied when starting mysqld_safe, e.g., “/data/mysql/mysql-bin.index not found (Errcode: 13 – Permission denied)”.

Solution

Ensure the data directory is owned by the mysql user and restart:

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

Adding --user=mysql during initialization also prevents this issue.

Case 4: Forgotten root password

Problem

Cannot log in:

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

.

Solution

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 password=password('newpass') where user='root';

Case 5: TRUNCATE resets auto‑increment

Problem

Using truncate on a table resets the AUTO_INCREMENT counter, causing “not found” errors in front‑end lookups.

Solution

Avoid truncate for data removal; use delete instead, or manually set the auto‑increment value after truncating.

Case 6: Chinese character garbling

Problem

Chinese characters appear as garbled text due to mismatched character sets.

Solution

Set client tools to UTF‑8.

Ensure OS locale is UTF‑8 (e.g., cat /etc/sysconfig/i18n).

Add character-set-server=utf8 to [mysqld] section.

For emoji support, use UTF‑8MB4:

vim /etc/my.cnf
[mysqld]
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

Case 7: binlog_format=statement causing data loss

Problem

Cross‑database statements are not replicated when binlog_format=statement and binlog-do-db limit replication.

Solution

Use binlog_format=row in production and avoid restrictive binlog-do-db settings.

Case 8: Connection timeout errors

Problem

Errors like “wait_timeout” and “interactive_timeout” cause connections to be closed after 8 hours.

Solution

Adjust both timeout variables to the same reasonable value and ensure application code closes connections properly.

Case 9: “can’t open file (errno:24)”

Problem

Too many open files; MySQL exceeds its open_files_limit.

Solution

Increase the limit, e.g., show variables like 'open_files_limit'; then set to 2048 and restart MySQL. Also check OS limits with ulimit -n and repair tables or clean disk space as needed.

These cases illustrate common MySQL error scenarios and practical troubleshooting steps.