---
title: "MySQL Debugging: Common Problems and Solutions"
description: "Step-by-step workflows for diagnosing access denied errors, connection limits, slow queries, InnoDB lock waits, replication lag, and data corruption in MySQL."
url: https://agent-zone.ai/knowledge/databases/mysql-debugging/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["mysql","debugging","troubleshooting","innodb","replication"]
skills: ["mysql-administration","database-debugging","incident-response"]
tools: ["mysql","mysqladmin","perror","innochecksum"]
levels: ["intermediate"]
word_count: 777
formats:
  json: https://agent-zone.ai/knowledge/databases/mysql-debugging/index.json
  html: https://agent-zone.ai/knowledge/databases/mysql-debugging/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=MySQL+Debugging%3A+Common+Problems+and+Solutions
---


# MySQL Debugging: Common Problems and Solutions

When MySQL breaks, it falls into a handful of failure modes. Here are the diagnostic workflows, in order of frequency.

## Access Denied Errors

`Access denied for user 'appuser'@'10.0.1.5' (using password: YES)` means wrong password, user does not exist for that host, or missing privileges.

Diagnosis:

```sql
-- 1. Does the user exist for that host?
SELECT user, host, plugin FROM mysql.user WHERE user = 'appuser';
-- MySQL matches user+host pairs. 'appuser'@'localhost' != 'appuser'@'%'.

-- 2. Check grants
SHOW GRANTS FOR 'appuser'@'%';

-- 3. Auth plugin mismatch? Old clients can't handle caching_sha2_password:
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
```

To reset a lost root password:

```bash
# Stop MySQL, start with --skip-grant-tables
sudo systemctl stop mysqld
sudo mysqld --skip-grant-tables --skip-networking &
mysql -u root
```

```sql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';
```

Then restart MySQL normally.

## Too Many Connections

Error 1040: `Too many connections`. MySQL hit `max_connections`.

```sql
-- Check current state
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- See what connections are doing
SHOW FULL PROCESSLIST;
-- Look for: many connections in Sleep state (idle app connections),
-- or many connections in Query state (overloaded server)
```

Immediate fix:

```sql
-- Increase dynamically (does not persist across restart)
SET GLOBAL max_connections = 500;
```

The real question is why. Common causes: no connection pooling, idle connections not being closed, or burst traffic. Find and kill idle connections:

```sql
SELECT id, user, host, command, time FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;
KILL 12345;
```

Long-term: implement connection pooling (ProxySQL, HikariCP) and set `wait_timeout` to close idle connections automatically.

## Slow Queries and SHOW PROCESSLIST

When the application is slow, start here:

```sql
-- What is running right now?
SHOW FULL PROCESSLIST;
-- Look for queries with high Time values or State = 'Sending data',
-- 'Creating sort index', 'Copying to tmp table'

-- For more detail, use Performance Schema
SELECT * FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL\G
```

If you see a specific slow query, grab it and run EXPLAIN:

```sql
EXPLAIN ANALYZE <paste the slow query here>;
```

Common patterns:
- `type: ALL` on a large table: missing index.
- `Using temporary; Using filesort`: the query needs a temp table and sort. Consider adding an index that covers the ORDER BY/GROUP BY.
- Subquery in WHERE clause scanning millions of rows: rewrite as a JOIN.

## InnoDB Lock Waits

`Lock wait timeout exceeded; try restarting transaction` -- one transaction is waiting for a lock held by another.

```sql
-- MySQL 8.0+: find who is blocking whom
SELECT * FROM performance_schema.data_lock_waits\G
SELECT * FROM performance_schema.data_locks\G

-- Deeper analysis
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" and "TRANSACTIONS" sections
```

Find the blocking transaction's thread ID and kill it if necessary:

```sql
KILL 67890;
```

Common causes: long-running transactions left open (forgot to COMMIT), batch UPDATEs hitting many rows while OLTP queries run, and missing indexes causing lock escalation.

## Replication Lag

On a replica:

```sql
SHOW REPLICA STATUS\G
-- Key fields:
-- Seconds_Behind_Source: lag in seconds (NULL means replication is broken)
-- Replica_IO_Running: should be Yes
-- Replica_SQL_Running: should be Yes
-- Last_Error: error message if SQL thread stopped
```

If `Replica_SQL_Running: No`, check `Last_Error`. For duplicate key errors, skip the event: `SET GLOBAL sql_replica_skip_counter = 1; START REPLICA;`

If lag is high but replication is running, enable parallel replication:

```sql
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
```

Other causes: heavy writes exceeding replica disk throughput, or long queries on the replica blocking the apply thread.

## Disk Full

MySQL stops accepting writes when disk is full. The error log will show `No space left on device`.

```bash
df -h /var/lib/mysql
du -sh /var/lib/mysql/*
# Purge binary logs
mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"
# Rotate slow log
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old && mysqladmin flush-logs
# Check for long-running transactions bloating undo logs
mysql -e "SELECT trx_id, trx_started, trx_rows_modified FROM information_schema.innodb_trx;"
```

## Crashed Tables and InnoDB Corruption

If MySQL won't start or you see `InnoDB: corrupted page` in the error log:

```bash
# Check the error log first
tail -100 /var/log/mysql/error.log

# Start in recovery mode (try levels 1-6, start low)
# Add to my.cnf:
# [mysqld]
# innodb_force_recovery = 1
sudo systemctl start mysqld

# Once started, dump the data immediately
mysqldump -u root -p --all-databases > emergency_dump.sql

# Then rebuild: stop MySQL, remove data, reinitialize, restore
sudo systemctl stop mysqld
sudo rm -rf /var/lib/mysql/*
sudo mysqld --initialize
sudo systemctl start mysqld
mysql -u root -p < emergency_dump.sql
```

Recovery levels: 1 (skip corrupt pages) through 6 (skip redo log). Higher levels risk data loss. Always dump and rebuild rather than running in recovery mode permanently.

For individual InnoDB table corruption, force a rebuild:

```sql
ALTER TABLE tablename ENGINE=InnoDB;  -- rebuilds the table in place
```

This resolves minor corruption and reclaims fragmented space. `REPAIR TABLE` only works for MyISAM.