MySQL Error Codes

Error Categories

ERROR 2002, 2003, 1045

ERROR 1064

ERROR 1062, 1452

ERROR 1146, 1205

🔹 Connection Error Codes

Connection errors occur when MySQL cannot establish or maintain a database connection. Common causes include wrong credentials, server not running, network issues, or firewall blocking. Check your host, port, username, password, and ensure the MySQL service is active before troubleshooting further.

-- ERROR 1045: Access denied
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
-- Solution: Check username and password

-- ERROR 2002: Can't connect through socket
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
-- Solution: Check if MySQL server is running

-- ERROR 2003: Can't connect to server
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
-- Solution: Verify server is running and port is correct

-- ERROR 1130: Host not allowed
ERROR 1130 (HY000): Host '192.168.1.100' is not allowed to connect
-- Solution: Grant access to the host

-- Fix example
GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.100' 
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

🔹 Syntax Error Codes

Syntax errors happen when SQL statements have incorrect formatting or invalid keywords. Error 1064 is the most common, indicating typos, missing commas, wrong quotes, or misspelled commands. Carefully review your query syntax, check for missing semicolons, and verify keyword spelling to resolve these errors.

-- ERROR 1064: Syntax error
ERROR 1064 (42000): You have an error in your SQL syntax
-- Common causes: typos, missing commas, wrong quotes

-- Wrong syntax
SELCT * FROM users;  -- Typo in SELECT

-- Correct syntax
SELECT * FROM users;

-- Wrong syntax (missing comma)
INSERT INTO users (name email) VALUES ('John', '[email protected]');

-- Correct syntax
INSERT INTO users (name, email) VALUES ('John', '[email protected]');

-- Wrong syntax (reserved keyword without backticks)
CREATE TABLE order (id INT);

-- Correct syntax
CREATE TABLE `order` (id INT);
-- Or better: use a different name
CREATE TABLE orders (id INT);

🔹 Duplicate Entry Errors

Error 1062 occurs when inserting or updating data violates a UNIQUE or PRIMARY KEY constraint. This means you're trying to add a value that already exists in a column that requires unique values. Solutions include checking existing data, using INSERT IGNORE, or UPDATE ON DUPLICATE KEY.

-- ERROR 1062: Duplicate entry
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'email'
-- Trying to insert duplicate value in UNIQUE column

-- Problem query
INSERT INTO users (email, name) 
VALUES ('[email protected]', 'John');
-- Fails if email already exists

-- Solution 1: Check before inserting
SELECT * FROM users WHERE email = '[email protected]';
-- If exists, don't insert

-- Solution 2: Use INSERT IGNORE (skips duplicates)
INSERT IGNORE INTO users (email, name) 
VALUES ('[email protected]', 'John');

-- Solution 3: Update if exists
INSERT INTO users (email, name) 
VALUES ('[email protected]', 'John')
ON DUPLICATE KEY UPDATE name = 'John';

-- Solution 4: Use REPLACE (deletes old, inserts new)
REPLACE INTO users (email, name) 
VALUES ('[email protected]', 'John');

🔹 Foreign Key Constraint Errors

Error 1452 happens when foreign key constraints are violated, typically when inserting a child record that references a non-existent parent record. Error 1451 occurs when deleting a parent record that has dependent child records. Always ensure referenced records exist and handle dependencies properly.

-- ERROR 1452: Foreign key constraint fails (insert)
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails
-- Trying to reference non-existent parent record

-- Problem: Inserting order for non-existent user
INSERT INTO orders (user_id, total) 
VALUES (999, 100.00);  -- user_id 999 doesn't exist

-- Solution: Check if user exists first
SELECT id FROM users WHERE id = 999;
-- If not exists, create user first or use valid user_id

-- ERROR 1451: Cannot delete parent row
ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails
-- Trying to delete parent with existing children

-- Problem: Deleting user with orders
DELETE FROM users WHERE id = 1;  -- Has orders

-- Solution 1: Delete children first
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- Solution 2: Use CASCADE (set during table creation)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE
);

🔹 Table and Column Errors

Error 1146 means the table doesn't exist in the database, often due to typos or wrong database selection. Error 1054 indicates a column doesn't exist in the specified table. Error 1050 occurs when creating a table that already exists. Always verify table and column names match your schema.

-- ERROR 1146: Table doesn't exist
ERROR 1146 (42S02): Table 'mydb.userss' doesn't exist
-- Typo in table name or wrong database

-- Solution: Check table name
SHOW TABLES;
SELECT * FROM users;  -- Correct name

-- ERROR 1054: Unknown column
ERROR 1054 (42S22): Unknown column 'emial' in 'field list'
-- Typo in column name

-- Solution: Check column names
DESCRIBE users;
SELECT email FROM users;  -- Correct name

-- ERROR 1050: Table already exists
ERROR 1050 (42S01): Table 'users' already exists

-- Solution: Use IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Or drop first
DROP TABLE IF EXISTS users;
CREATE TABLE users (id INT PRIMARY KEY);

🔹 Data Type and Value Errors

Error 1366 occurs when inserting incorrect data types, like text into numeric columns. Error 1264 happens when values exceed column limits. Error 1048 indicates NULL values in NOT NULL columns. Always validate data types, check value ranges, and ensure required fields have values before insertion.

-- ERROR 1366: Incorrect data type
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'age'
-- Trying to insert text into numeric column

-- Problem
INSERT INTO users (name, age) VALUES ('John', 'twenty');

-- Solution
INSERT INTO users (name, age) VALUES ('John', 20);

-- ERROR 1264: Out of range value
ERROR 1264 (22003): Out of range value for column 'age' at row 1
-- Value too large for column type

-- Problem: TINYINT range is -128 to 127
INSERT INTO users (age) VALUES (200);

-- Solution: Use appropriate data type
ALTER TABLE users MODIFY age SMALLINT;

-- ERROR 1048: Column cannot be null
ERROR 1048 (23000): Column 'email' cannot be null

-- Problem
INSERT INTO users (name) VALUES ('John');  -- email is NOT NULL

-- Solution: Provide value
INSERT INTO users (name, email) VALUES ('John', '[email protected]');

🔹 Lock and Timeout Errors

Error 1205 occurs when a transaction waits too long for a table lock, usually due to long-running queries or deadlocks. Error 1213 indicates a deadlock where two transactions wait for each other. Solutions include optimizing queries, using shorter transactions, proper indexing, and implementing retry logic in applications.

-- ERROR 1205: Lock wait timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- Transaction waiting too long for lock

-- Problem: Long-running transaction blocking others
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
-- ... long delay ...
COMMIT;

-- Solution 1: Keep transactions short
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT;  -- Commit quickly

-- Solution 2: Increase timeout (temporary fix)
SET innodb_lock_wait_timeout = 120;  -- Default is 50 seconds

-- ERROR 1213: Deadlock
ERROR 1213 (40001): Deadlock found when trying to get lock
-- Two transactions waiting for each other

-- Solution: Use consistent lock order
-- Transaction 1: Lock A then B
-- Transaction 2: Lock A then B (not B then A)

-- Check current locks
SHOW ENGINE INNODB STATUS;

-- Kill blocking process
SHOW PROCESSLIST;
KILL 123;  -- Process ID

🔹 Debugging Error Codes

When encountering errors, read the complete error message including the code, SQL state, and description. Use SHOW WARNINGS for additional details, check MySQL documentation for specific error codes, enable query logging for debugging, and test queries in isolation to identify the exact problem source.

-- View detailed error information
SHOW WARNINGS;
SHOW ERRORS;

-- Enable general query log for debugging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

-- Check error log location
SHOW VARIABLES LIKE 'log_error';

-- View recent errors in error log
-- (Run in terminal, not MySQL)
-- tail -f /var/log/mysql/error.log

-- Get SQL mode (affects error behavior)
SELECT @@sql_mode;

-- Set strict mode for better error detection
SET sql_mode = 'STRICT_ALL_TABLES';

-- Test query step by step
-- Instead of complex query, break it down:
SELECT * FROM users WHERE id = 1;  -- Test table access
SELECT COUNT(*) FROM orders WHERE user_id = 1;  -- Test join condition
-- Then combine

-- Use EXPLAIN to debug slow queries
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';