Troubleshooting and Common Errors

MySQL errors have a numeric code, a SQLSTATE, and a message: ERROR 1062 (23000): Duplicate entry ....

Concept

MySQL errors have a numeric code, a SQLSTATE, and a message: ERROR 1062 (23000): Duplicate entry .... Client-side connection errors have codes in the 2000 range. Knowing the common ones — and a systematic debugging method — resolves most problems quickly.

Why It Matters

Fast diagnosis reduces downtime and frustration, and many "database bugs" are actually configuration, privilege, or data problems.

Syntax

SHOW WARNINGS;          SHOW ERRORS;
SHOW ENGINE INNODB STATUS\G
SELECT * FROM performance_schema.error_log ORDER BY logged DESC LIMIT 20;   -- 8.0.22+: error log via SQL

perror 1213            # explain an error code from the shell

Example

38.1 Common errors reference Message (short) Access denied for user

Wrong password, wrong host part, user missing Check 'user'@'host', SELECT user, host FROM mysql.user Can't connect through socket Server down, wrong socket path Start service; use -h 127.0.0.1 Can't connect to MySQL server on 'host' Server down, firewall, bind-address Check service, port 3306, network Lost connection / server has gone away Timeouts, max_allowed_packet, crash

Increase packet size, pool maxLifetime < wait_timeout, check error log Too many connections Pool leaks, spikes

Fix leaks, pooling, raise max_connections carefully Typo, wrong server SHOW DATABASES Table doesn't exist Wrong DB/case (Linux), typo SHOW TABLES, lowercase names

Typo, alias in WHERE, double-quoted string Check DESCRIBE, use single quotes for strings SQL syntax error near '...' Typo, reserved word, wrong dialect

Look just before the quoted text; backtick reserved words Duplicate entry for key PK/UNIQUE violation Find existing row; upsert if intended

Cannot add or update a child row: FK fails Parent row missing Insert parent first; check values/types Cannot delete or update a parent row

Delete children or use deliberate ON DELETE action FK column incompatible / missing index Type/sign/collation mismatch

Match types exactly; index the referenced column Column cannot be null NULL into NOT NULL Supply value or default Field doesn't have a default value Omitted NOT NULL column (strict mode) Supply value or add DEFAULT Data too long for column Value exceeds length Validate/widen column Out of range value Numeric overflow, unsigned negative Wider/signed type, validate Incorrect / truncated value

Bad date or string→number, wrong charset Fix input format; use utf8mb4 Check constraint violated Not in GROUP BY (only_full_group_by) Aggregate, ANY_VALUE, or group by it

Can't specify target table for update in FROM Subquery on same table in UPDATE/DELETE Wrap in derived table or use JOIN Safe update mode UPDATE/DELETE without key WHERE Add key-based WHERE Lock wait timeout exceeded Blocked by another transaction

Find blocker (sys.innodb_lock_waits), shorten transactions Retry; consistent lock order

Command denied / access denied to database SHOW GRANTS; grant minimum needed Function has none of DETERMINISTIC...

Can't update table in trigger Trigger modifies its own table Use BEFORE trigger with SET NEW.col Illegal mix of collations Different collations compared The table is full Disk full / tmp space

Free disk, check innodb_data_file_path, temp limits Total number of locks exceeds lock table size Huge transaction, tiny buffer pool

Batch the operation; increase buffer pool Running with --secure-file-priv / --read-only

File path not allowed / read-only server Use allowed directory / write to the primary 38.2 Worked examples 38.3 "Access denied" checklist 38.4 Systematic debugging method

SELECT name FROM customers WHERE city = "Pune";
-- Works in default MySQL mode (double quotes = string), but fails with ANSI_QUOTES mode (double quotes = identifier).
-- Portable fix: single quotes.

SELECT * FROM order;
-- ERROR 1064: ... near 'order' — ORDER is reserved. Use `order` or rename the table to orders.

INSERT INTO orders VALUES (1007, 9, '2026-04-01', 'PENDING', 10);
-- ERROR 1452: foreign key fails (customer 9 does not exist).

UPDATE products SET stock = stock - 1 WHERE stock > 0;
-- with sql_safe_updates=1: ERROR 1175 (stock is not a key column) — add a key condition or LIMIT.

1. Which account matched?        SELECT USER(), CURRENT_USER();   (they can differ!)
2. Does the account exist?        SELECT user, host, plugin, account_locked FROM mysql.user WHERE user = 'x';
3. Host part correct?             'x'@'localhost' (socket) vs 'x'@'127.0.0.1' vs 'x'@'%'
4. Anonymous user shadowing?      A ''@'localhost' account can match before 'x'@'%'
5. Password / plugin?             Old client + caching_sha2_password → upgrade client or use TLS / RSA key exchange
6. Account locked or expired?     account_locked, password_expired
7. Privileges?                    SHOW GRANTS FOR CURRENT_USER();  roles active? SELECT CURRENT_ROLE();

1. Read the full error: code, SQLSTATE, object names, quoted position.
2. Reproduce with the smallest statement.
3. Check context: SELECT DATABASE(), CURRENT_USER(), @@sql_mode, @@version, transaction state.
4. SHOW WARNINGS after "successful" statements (truncation, conversions).
5. For wrong results: run the query in pieces and check row counts after each JOIN/WHERE/GROUP BY.
6. For slowness: EXPLAIN ANALYZE, then check locks (sys.innodb_lock_waits) before blaming the plan.
7. For server issues: error log, SHOW ENGINE INNODB STATUS, disk space, memory.
8. Fix, then add a regression test.

Expected Result

perror 1213

MySQL error code MY-001213 (ER_LOCK_DEADLOCK): Deadlock found when trying to get lock; try restarting transaction

Common Mistakes

Ignoring warnings (silent truncation in non-strict mode).

Confusing USER() with CURRENT_USER() when debugging access.

Increasing timeouts instead of finding the blocking transaction.

Best Practices

Strict SQL mode everywhere.

Log error codes in applications; classify retryable ones (1213, 1205, 2013 for idempotent operations).

Keep a team runbook of common errors and fixes.

Interview Relevance

Medium: "What does error 1213/1205/1452/1062 mean?", "How do you troubleshoot 'Access denied'?", "What is error 1093?".

Practice Questions

Reproduce errors 1062, 1452, 1055, and 1093 and fix each.

Create two accounts x@localhost and x@% with different passwords and explain which one is used when.

Find the transaction blocking your UPDATE using the sys schema.