Skip to content
DevOps AI ToolKit
Newsletter
MySQL Troubleshooting Toolkit

MySQL Troubleshooting Toolkit

Use this MySQL troubleshooting toolkit to diagnose too-many-connections, locks and deadlocks, replication lag, "server has gone away", and InnoDB issues.

Paste your error and get a triage plan.

Paste a log line, CLI error, stack trace, service failure, or config snippet and get a structured troubleshooting plan. Your snippet is carried straight into the AI Incident Response Assistant with MySQL context prefilled.

Open the MySQL Incident Assistant → Browse MySQL prompts

Do not paste secrets, tokens, private keys, passwords, or customer data. Your snippet stays in your browser until you open the assistant.

Top 25 MySQL Errors and Failure Modes

The production failures engineers hit most — each links to a full cause → fix → prevention guide.

Too many connections (ER 1040)

The server has reached max_connections and refuses new client sessions until existing ones close.

Access denied for user (1045)

Authentication failed because of a wrong password, missing grant, or host not matching the user account.

Can't connect to local MySQL server through socket (2002)

The client cannot reach the Unix socket, usually because mysqld is down or the socket path is wrong.

Can't connect to MySQL server on host (2003)

The TCP connection to the server failed due to a firewall, wrong port, bind-address, or a stopped daemon.

Lock wait timeout exceeded (1205)

A transaction waited longer than innodb_lock_wait_timeout for a row lock held by another transaction.

Deadlock found when trying to get lock (1213)

InnoDB detected two transactions waiting on each other's locks and rolled one back to break the cycle.

MySQL server has gone away (2006)

The connection dropped mid-query, often from wait_timeout, a large packet, or the server restarting.

Packet bigger than max_allowed_packet (1153)

A query or result exceeded max_allowed_packet, so the server aborted the oversized transfer.

The table is full (1114)

InnoDB could not extend the tablespace, typically from a full disk or an exhausted table size limit.

Got fatal error 1236 from source

A replica's IO thread stopped because the source's binary logs are missing, purged, or GTID state diverged.

Duplicate entry for key (1062)

An insert or update violated a unique or primary key constraint with a value that already exists.

Table doesn't exist (1146)

A query referenced a table that is missing, misspelled, or in a different database or case than expected.

Unknown column in field list (1054)

A query referenced a column that does not exist, often from a typo, alias scope, or a schema change.

Incorrect string value for column (1366)

A charset mismatch, such as inserting 4-byte emoji into a utf8 (not utf8mb4) column, rejected the value.

Aborted connection / Got an error reading communication packets

Clients disconnected without closing cleanly, from timeouts, network drops, or oversized packets.

Table is marked as crashed and should be repaired (1194)

A MyISAM or Aria table is corrupted and needs REPAIR TABLE before it can be read or written again.

Out of sort memory / sort aborted (1038)

A large ORDER BY or GROUP BY exceeded sort_buffer_size, so the sort operation failed.

The total number of locks exceeds the lock table size (1206)

A huge transaction ran InnoDB out of lock memory, usually from a bulk delete or update without batching.

Cannot add or update a child row: foreign key constraint fails (1452) guide coming soon

An insert or update referenced a parent key that does not exist, so the foreign key check rejected it.

Replication lag (high Seconds_Behind_Master) guide coming soon

A replica's SQL thread cannot keep pace with the source, leaving reads increasingly stale.

Out of memory / OOM killer terminates mysqld guide coming soon

Oversized buffers or too many connections exhausted RAM and the kernel killed the MySQL process.

InnoDB: Database page corruption / checksum mismatch guide coming soon

InnoDB detected a corrupt page on disk, often from bad hardware, and may refuse to start.

Too many open files (24) guide coming soon

MySQL hit the operating system file-descriptor limit and could not open more tables or connections.

The MySQL server is running with the --read-only option (1290) guide coming soon

A write was attempted against a read-only replica or a server put into read_only mode.

InnoDB log waits / redo log pressure guide coming soon

Write throughput stalled because the redo log filled faster than checkpoints could flush dirty pages.

Validate your config before you redeploy

Catch the structural mistakes that cause outages — 100% in your browser, nothing uploaded.

YAML Validator (config / manifests) Not sure? Start with the Incident Assistant →

Best MySQL Prompts

Turn symptoms, logs, and config into a structured plan with prompts tuned for MySQL.

Slow Query Log EXPLAIN & Tuning

Diagnose and rewrite slow queries

Use this prompt

InnoDB Deadlock Analysis

Decode SHOW ENGINE INNODB STATUS deadlocks

Use this prompt

Metadata Lock Wait Debug

Find and clear blocking locks

Use this prompt

Replication Lag & GTID Debug

Trace and fix replica lag

Use this prompt

Too Many Connections & Pooling

Right-size connections and pooling

Use this prompt

InnoDB Buffer Pool Config Tuning

Tune InnoDB memory and I/O

Use this prompt

Crash Recovery & Corruption Triage

Recover a corrupt or crashed instance

Use this prompt

Disk Full & Tablespace Reclaim

Free space and reclaim tablespace

Use this prompt

Download the MySQL Troubleshooting Runbook Pack

First-response checklists for MySQL/MariaDB — connections, locks, replication, and InnoDB.

  • Too-many-connections & limits
  • Locks & deadlock analysis
  • Slow query & index review
  • Replication status & lag
  • InnoDB & disk-full recovery
Download the MySQL Runbook Pack

All MySQL Troubleshooting Guides

Grouped by failure-mode type — each guide covers cause, fix, validation, and prevention.

Authentication & TLS (4)

MySQL Error: 'ERROR 1044 (42000): Access denied for user to database command denied to user Access denied for user root@localhost ERROR 1045 (28000)

Networking (9)

MySQL Error: 'ERROR 1130 (HY000): Host is not allowed to connect to this MySQL server MySQL Error: 'ERROR 1927 (HY000): Connection was killed Host is blocked because of many connection errors Lost connection to MySQL server during query ERROR 2003 (HY000) Got an error reading communication packets ERROR 1040 (HY000) ERROR 2002 (HY000) Designing ProxySQL Read/Write Splitting With AI

Storage (3)

MySQL Error: 'Got error 28 from storage engine server is running with the --read-only option MySQL Backup and Point-in-Time Recovery With AI

Performance (7)

ERROR 1038 (HY001) ERROR 1206 (HY000) ERROR 1205 (HY000) ERROR 1213 (40001) Debugging Slow MySQL Queries With AI Diagnosing MySQL Deadlocks With AI MySQL Replication Setup and Lag Debugging With AI

Configuration (4)

MySQL Error: 'ERROR 1067 (42000): Invalid default value MySQL Error: 'ERROR 2005 (HY000): Unknown MySQL server host You have an error in your SQL syntax ERROR 1054 (42S22)

Other (41)

MySQL Error: 'ERROR 1032 (HY000): Can't find record MySQL Error: 'ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key MySQL Error: 'ERROR 1093 (HY000): You can't specify target table for update in FROM clause MySQL Error: 'ERROR 1118 (42000): Row size too large MySQL Error: 'ERROR 1136 (21S01): Column count doesn't match value count at row 1 MySQL Error: 'ERROR 1170 (42000): BLOB/TEXT column used in key specification without a key length MySQL Error: 'ERROR 1305 (42000): FUNCTION does not exist MySQL Error: 'ERROR 144: Table is marked as crashed and last (automatic?) repair failed MySQL Error: 'ERROR 1449 (HY000): The user specified as a definer does not exist MySQL Error: 'ERROR 1615 (HY000): Prepared statement needs to be re-prepared MySQL Error: 'InnoDB: page_cleaner: 1000ms intended loop took ... ms Table already exists isn Out of range value for column Incorrect datetime value Data too long for column Cannot delete or update a parent row Cannot add or update a child row: a foreign key constraint fails Specified key was too long; max key length is 767 bytes errno 150 Cannot add foreign key constraint ERROR 1153 (08S01) ERROR 1194 (HY000) ERROR 1236 (HY000) ERROR 1366 (HY000) Setting Up MySQL Audit Logging With AI ERROR 1062 (23000) ERROR 1114 (HY000) ERROR 1146 (42S02) ERROR 2006 (HY000) Running a MySQL Group Replication Cluster With AI Generated Columns and Functional Indexes in MySQL With AI Fixing MySQL Query Plans With Histograms and Optimizer Hints, With AI Designing MySQL JSON Columns With AI Investigating MySQL Performance With performance_schema and AI Tuning MySQL Semi-Synchronous Replication With AI Partitioning Large MySQL Tables With AI AI-Assisted Composite and Covering Index Design for MySQL Migrating MySQL to utf8mb4 Safely With AI Online Schema Changes With gh-ost and AI Tuning InnoDB Buffer Pool and Flushing With AI Tuning my.cnf for Your Workload With AI
Browse the full MySQL category

MySQL troubleshooting FAQ

How do I fix 'Too many connections' in MySQL?
This means the server hit max_connections. Free connections by closing idle sessions and using a connection pool with sane pool sizes, lower wait_timeout so idle clients disconnect, and raise max_connections only after confirming RAM per connection is sufficient. Long term, cap application pool sizes so total connections stay well under the server limit.
Why does MySQL say 'server has gone away'?
Error 2006 means the connection was dropped mid-request. The most common causes are hitting wait_timeout on an idle connection, sending a query larger than max_allowed_packet, or the server restarting or being OOM-killed. Increase max_allowed_packet for large payloads, keep connections short-lived or validate them before reuse, and check the error log for restarts.
How do I resolve a MySQL deadlock (error 1213)?
InnoDB automatically rolls back one transaction to break the deadlock, so the immediate fix is to catch the error and retry the losing transaction. To prevent recurrence, run SHOW ENGINE INNODB STATUS to see the conflicting locks, then make transactions access rows in a consistent order, keep them short, and add indexes so locks target fewer rows.
What causes MySQL replication lag?
High Seconds_Behind_Master usually comes from a single-threaded replica applying heavy write bursts, long-running transactions on the source, slow replica disk or I/O, or missing indexes that make replicated statements slow. Enable multi-threaded replication (parallel appliers), keep transactions small, ensure the replica has indexes matching the source, and check for lock contention on the replica.
How do I fix 'Lock wait timeout exceeded' (error 1205)?
A transaction waited past innodb_lock_wait_timeout for a row lock held by another transaction. Identify the blocking transaction via the data_locks and INNODB_TRX tables, then commit or kill the long-running holder. Prevent it by keeping transactions short, committing promptly, adding indexes so updates lock fewer rows, and avoiding user think-time inside open transactions.
Why do I get 'Incorrect string value' when inserting emoji or special characters?
Error 1366 means the column's charset cannot store the bytes you sent, most often when inserting 4-byte characters like emoji into a utf8 (3-byte) column. Convert the column, table, and connection to utf8mb4 with the utf8mb4_unicode_ci or utf8mb4_0900_ai_ci collation, and make sure the client sets names to utf8mb4 so the full Unicode range is stored correctly.