Eight Classic MySQL Errors and How to Fix Them

Preface

Today we list the eight classic error cases in MySQL databases, providing troubleshooting ideas and methods to help beginners and database enthusiasts.

1. Forgot password, cannot log in

1.1 Error symptom

ERROR 1130 (HY000): Host ‘172.18.1.1’ is not allowed to connect to this MySQL server – login failed.

1.2 Solution

When the password is lost, it cannot be recovered directly; you must modify it via a special method. Add the following line to the MySQL configuration file and restart MySQL; login will not require a password. skip-grant-tables

Example cat /etc/my.cnf (see image).

Restart MySQL: systemctl start mysqld mysql -uroot -p (empty password works) MySQL 8 password change: ALTER USER root@'localhost' IDENTIFIED WITH mysql_native_password BY 'root1'; MySQL 5.x password change: UPDATE mysql.user SET authentication_string=PASSWORD('root') WHERE user='root'; FLUSH PRIVILEGES; After editing, comment out skip-grant-tables in /etc/my.cnf and restart the service.

2. Simple password change triggers policy error

2.1 Error symptom

ALTER USER root@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

2.2 Solution

Check password policy: SHOW VARIABLES LIKE 'validate_password%'; Disable strict policy (default OFF): SET GLOBAL validate_password.check_user_name=OFF; Set policy level to LOW (0): SET GLOBAL validate_password_policy=0; Set minimum length: SET GLOBAL validate_password.length=4; Disable mixed‑case, numeric, and special‑character checks: SET GLOBAL validate_password.mixed_case_count=0; SET GLOBAL validate_password.number_count=0; Finally, flush privileges: FLUSH PRIVILEGES;

3. Case‑sensitivity error

3.1 Error symptom

On Windows development the table student works, but after deploying to Linux the error appears: MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist

3.2 Solution

Check the variable lower_case_table_names on both environments. Windows defaults to 1 (case‑insensitive), Linux defaults to 0 (case‑sensitive). Ensure table names match the case used in SQL statements, or set the variable consistently in the configuration.

4. MySQL service cannot start (Windows error 1053)

4.1 Error symptom

Windows reports: “The MySQL service did not respond to the start or control request in a timely fashion (error 1053).”

4.2 Solution

Terminate any lingering MySQL processes: tasklist to view, then taskkill /f /t /im to kill. Add the current user (or NETWORK SERVICE) to the Administrators group via the Windows “Computer Management → Local Users and Groups → Groups” UI. Re‑install MySQL if necessary, and ensure the “Windows Installer” service is set to manual and started ( services.msc → Windows Installer → Start ).

5. Export/Import error due to secure‑file‑priv

5.1 Error symptom

During data export the error appears: “secure‑file‑priv option so it cannot execute this statement.”

5.2 Solution

Clear the secure_file_priv setting in /etc/my.cnf : secure-file-priv= When the variable is NULL, import/export is blocked; when set to a directory (e.g., /tmp/ ), only that directory is allowed. Export example: SELECT host,user FROM mysql.user INTO OUTFILE '/tmp/user.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';

6. Too many connections, cannot connect to MySQL

6.1 Error symptom

Clients exceed the max_connections limit.

6.2 Solution

Increase the limit, e.g., SET GLOBAL max_connections=500; , but be aware of server capacity. Adjust InnoDB concurrency if needed: set innodb_thread_concurrency to a reasonable value (e.g., 16).

7. Disk full, binary log cannot be written

7.1 Error symptom

Binary log (binlog) fills the disk; MySQL cannot write new logs.

7.2 Cleanup

Check expiration variable (MySQL 8 replaces expire_logs_days ): SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%'; Set automatic expiration, e.g., SET GLOBAL binlog_expire_logs_seconds=86400; (1 day). Manually purge old logs: PURGE BINARY LOGS TO 'binlog.000025'; PURGE BINARY LOGS BEFORE '2020-04-28 23:59:59'; Reset the binary log sequence: RESET MASTER;

8. Primary‑key error causing replication failure

8.1 Error symptom

Slave status shows Last_SQL_Errno: 1062 and “Duplicate entry ‘1’ for key ‘PRIMARY’”.

8.2 Solution

Skip the offending transaction: SET GLOBAL sql_slave_skip_counter=1; START SLAVE SQL_THREAD; Or configure slave_skip_errors=1062 in the my.cnf file to skip duplicate‑key errors. Use these options carefully, as indiscriminate skipping may cause data inconsistency.