MySQL Troubleshooting

Introduction

Database errors look cryptic until you know the pattern. This chapter fixes connection refused, Access denied, charset garbling, deadlocks, duplicate key conflicts, Too many connections, and slow queries—using the same diagnostic flow on Windows, macOS, Linux, and Docker.

Prerequisites

Chapters through Query Performance Basics
Terminal and optional mysql client access

Cannot Connect

text

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306'

Checklist:

Check	Command / action
Server running	Services, `systemctl status mysql`, `docker ps`
Correct port	`3306` vs Docker `3307`
Host	`localhost` vs `127.0.0.1` socket differences
Firewall	Allow local; block public 3306 on laptops
`bind-address`	`127.0.0.1` in config limits remote

bash

ss -tlnp | grep 3306
docker logs mysql-learn

Access Denied

text

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

Cause	Fix
Wrong password	Reset or use correct creds
User/host mismatch	`'user'@'localhost'` vs `'user'@'127.0.0.1'`
Auth plugin	`ALTER USER … IDENTIFIED WITH mysql_native_password BY '…'` for old clients
No privilege	`SHOW GRANTS`, `GRANT` needed tables

Ubuntu socket root:

bash

sudo mysql

Then set password auth if tools require it.

Chinese / Emoji Garbled

Unify utf8mb4 end-to-end:

sql

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Database:

sql

ALTER DATABASE blog_dev CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Client session:

sql

SET NAMES utf8mb4;

Table already wrong charset—ALTER TABLE convert (locks table):

sql

ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Connection string in app must specify utf8mb4.

Deadlock and Lock Wait Timeout

text

ERROR 1213 (40001): Deadlock found when trying to get lock
ERROR 1205 (HY000): Lock wait timeout exceeded

Actions:

Retry transaction in app
Access tables in consistent order
Shorten transactions
Index rows you lock with FOR UPDATE

Inspect:

sql

SHOW ENGINE INNODB STATUS\G

Look at LATEST DETECTED DEADLOCK section.

Duplicate Key Errors

text

ERROR 1062 (23000): Duplicate entry 'ada@example.com' for key 'uk_users_email'

Expected on UNIQUE violation—fix data or use ON DUPLICATE KEY UPDATE.

Primary key duplicate—wrong AUTO_INCREMENT after manual id insert:

sql

SELECT MAX(id) FROM users;
ALTER TABLE users AUTO_INCREMENT = <max_plus_one>;

Too Many Connections

text

ERROR 1040 (HY000): Too many connections

sql

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

Fix:

Close leaked app connections—use pool (HikariCP)
Raise max_connections with RAM awareness
Kill idle threads (careful production):

sql

SHOW PROCESSLIST;
KILL <id>;

Slow Queries

Enable slow log—see Query Performance Basics.

Quick checks:

sql

EXPLAIN your_slow_query\G
SHOW INDEX FROM posts;

Missing index on WHERE/JOIN column—common fix.

Recovery After Mistake

Mistake	Path
Bad UPDATE committed	Restore backup; or point-in-time with binlogs
Dropped table	`mysqldump` restore; no undo
`DELETE` without WHERE	Restore backup; `sql_safe_updates` prevention

git reflog is Git—MySQL needs Backup and Restore.

FAQ

Server gone away?

Timeout or max_allowed_packet—reconnect; increase packet for big inserts.

Table doesn't exist?

Wrong database USE; case sensitivity on Linux file names.

Corrupt table?

CHECK TABLE t; REPAIR TABLE t;—InnoDB prefer restore from backup.

Docker volume full?

docker system df; prune or expand disk.

JDBC works CLI not?

URL host 127.0.0.1, SSL params, timezone.

Where are logs?

Linux /var/log/mysql/; Docker docker logs; Windows Event Viewer + data dir .err.