MySQL Common Problems

Replication stopped

Replica_SQL_Running: No with an error in Last_SQL_Error. Common causes: a row that does not exist on the replica for an UPDATE, a duplicate key from a write made directly on the replica, or a schema difference.

Diagnose before acting — see the replication stopped playbook. Set super_read_only = ON on replicas to prevent the most common cause.

Lock wait timeout exceeded

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Another transaction holds a lock for longer than innodb_lock_wait_timeout (50 seconds by default).

SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

The blocking transaction is nearly always long-running application code holding a transaction open across an external call. Lower the timeout so failures are fast and visible, and fix the code.

Deadlocks

Detected and resolved automatically by rolling back one transaction; the application must retry. Enable innodb_print_all_deadlocks to see all of them rather than only the most recent in SHOW ENGINE INNODB STATUS.

Undo history growth

Disk usage climbing while row counts do not, and reads getting slower. The purge thread cannot remove old row versions because a transaction is still open.

SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';

Find and terminate the oldest open transaction, then prevent recurrence with application-level transaction deadlines.

Too many connections

ERROR 1040: Too many connections. Raising max_connections buys time and costs memory. Use ProxySQL to cap the total, and give background jobs their own smaller pool. See the too many connections playbook.

Table is full or disk full

Both the data directory and the binary log directory can fill. Binlogs are the usual surprise on a write-heavy server. Check binlog_expire_logs_seconds, confirm no replica still needs the old logs, and monitor both directories separately.

The query got slower after a data load

InnoDB samples index pages for cardinality estimates, and a bulk load can leave them stale.

ANALYZE TABLE orders;

If the plan is still wrong, the estimate rather than the query is the problem — raise innodb_stats_persistent_sample_pages for that table.

Character set and collation mismatch

A join between a utf8mb4_0900_ai_ci column and a utf8mb4_general_ci column cannot use an index on either, because the server must convert. It appears as an inexplicable full scan on a well-indexed column.

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'shop' AND collation_name IS NOT NULL;

Keep one charset and collation across the whole schema.

Implicit type conversion

-- user_id is BIGINT; the string literal forces a conversion and drops the index.
SELECT * FROM orders WHERE customer_id = '4711';

MySQL will accept it, convert, and often scan. Match parameter types to column types in the application layer.