❌ Common MySQL Errors

ERROR 1064: Syntax Error

Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...' at line X

Common Causes:

• Missing or extra comma, parenthesis, or semicolon
• Typo in a keyword (SELCET instead of SELECT)
• Using a reserved word as a column or table name without backticks
• Running mysqldump from inside the mysql> prompt
• Missing quotes around string values

How to Fix:

-- Look at what comes AFTER "near" in the error — that's where MySQL got confused

-- Bad: reserved word as column name
CREATE TABLE orders (order INT);           -- 'order' is reserved!

-- Fix: use backticks or a different name
CREATE TABLE orders (`order` INT);         -- Works but not recommended
CREATE TABLE orders (order_number INT);    -- Better: avoid reserved words

-- Bad: missing comma
SELECT title price FROM books;

-- Fix: add the comma
SELECT title, price FROM books;
                    

ERROR 1054: Unknown Column

Message: Unknown column 'column_name' in 'field list'

Common Causes:

• Typo in the column name
• Column doesn't exist in the table
• Forgot to specify the table alias in a join
• Using a column alias in WHERE (not allowed — use HAVING or repeat the expression)

How to Fix:

-- Check the actual column names
DESCRIBE books;

-- Bad: alias used in WHERE
SELECT price * 0.9 AS sale_price FROM books WHERE sale_price < 10;

-- Fix: repeat the expression or use HAVING
SELECT price * 0.9 AS sale_price FROM books WHERE price * 0.9 < 10;
                    

ERROR 1055: Non-aggregated Column in GROUP BY

Message: Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column

Cause:

You selected a column that isn't in GROUP BY and isn't inside an aggregate function.

How to Fix:

-- Bad: title is not aggregated or grouped
SELECT author_id, title, COUNT(*) FROM books GROUP BY author_id;

-- Fix Option 1: Add to GROUP BY
SELECT author_id, title, COUNT(*) FROM books GROUP BY author_id, title;

-- Fix Option 2: Use an aggregate
SELECT author_id, MAX(title), COUNT(*) FROM books GROUP BY author_id;

-- Fix Option 3: Remove the non-aggregated column
SELECT author_id, COUNT(*) FROM books GROUP BY author_id;
                    

ERROR 1046: No Database Selected

Message: No database selected

Cause:

You ran a query without first selecting a database with USE.

How to Fix:

-- Select the database first
USE bookstore;

-- Then run your query
SELECT * FROM books;

-- Or specify the database in the query
SELECT * FROM bookstore.books;
                    

ERROR 1049: Unknown Database

Message: Unknown database 'database_name'

Cause:

The database name is misspelled or doesn't exist.

How to Fix:

-- List all databases to check the name
SHOW DATABASES;

-- Create it if it doesn't exist
CREATE DATABASE IF NOT EXISTS bookstore;
                    

ERROR 1050: Table Already Exists

Message: Table 'table_name' already exists

How to Fix:

-- Use IF NOT EXISTS to avoid the error
CREATE TABLE IF NOT EXISTS books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200)
);

-- Or drop the table first (careful — this deletes all data!)
DROP TABLE IF EXISTS books;
                    

ERROR 1146: Table Doesn't Exist

Message: Table 'database.table_name' doesn't exist

Common Causes:

• Typo in the table name
• Wrong database selected
• Table hasn't been created yet
• Case sensitivity on Linux (tables are case-sensitive on Linux, not on Windows/macOS)

How to Fix:

-- Check current database and list tables
SELECT DATABASE();
SHOW TABLES;
                    

ERROR 1062: Duplicate Entry

Message: Duplicate entry 'value' for key 'key_name'

Cause:

You're trying to insert or update a value that violates a UNIQUE constraint or PRIMARY KEY.

How to Fix:

-- Check existing values
SELECT * FROM users WHERE email = 'john@example.com';

-- Option 1: Use INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO users (email, name) VALUES ('john@example.com', 'John')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Option 2: Use INSERT IGNORE (silently skips duplicates)
INSERT IGNORE INTO users (email, name) VALUES ('john@example.com', 'John');

-- Option 3: Use a different unique value
                    

ERROR 1048: Column Cannot Be NULL

Message: Column 'column_name' cannot be null

Cause:

You're inserting or updating a NOT NULL column with a NULL value (or not providing a value for a required column).

How to Fix:

-- Provide a value for the required column
INSERT INTO books (title, price) VALUES ('My Book', 9.99);

-- Or alter the column to allow NULLs (if appropriate)
ALTER TABLE books MODIFY COLUMN price DECIMAL(10,2) NULL;

-- Or add a default value
ALTER TABLE books MODIFY COLUMN price DECIMAL(10,2) DEFAULT 0.00;
                    

ERROR 1451: Cannot Delete — Foreign Key Constraint

Message: Cannot delete or update a parent row: a foreign key constraint fails

Cause:

You're trying to delete a row that other rows depend on via a foreign key (and the action is RESTRICT or NO ACTION).

How to Fix:

-- Option 1: Delete the child rows first
DELETE FROM books WHERE author_id = 5;
DELETE FROM authors WHERE id = 5;

-- Option 2: Change the foreign key to CASCADE
ALTER TABLE books DROP FOREIGN KEY fk_author;
ALTER TABLE books ADD CONSTRAINT fk_author
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE;

-- Option 3: Set child values to NULL
ALTER TABLE books DROP FOREIGN KEY fk_author;
ALTER TABLE books ADD CONSTRAINT fk_author
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE SET NULL;
                    

ERROR 1452: Cannot Add — Foreign Key Constraint

Message: Cannot add or update a child row: a foreign key constraint fails

Cause:

You're inserting a foreign key value that doesn't exist in the parent table.

How to Fix:

-- Check if the parent record exists
SELECT * FROM authors WHERE id = 99;

-- Insert the parent record first, then the child
INSERT INTO authors (id, name) VALUES (99, 'New Author');
INSERT INTO books (title, author_id) VALUES ('New Book', 99);
                    

ERROR 1406: Data Too Long

Message: Data too long for column 'column_name' at row N

Cause:

The value exceeds the column's maximum length (e.g., inserting 300 characters into a VARCHAR(200)).

How to Fix:

-- Check the column's current limit
DESCRIBE books;

-- Increase the column size
ALTER TABLE books MODIFY COLUMN title VARCHAR(500);

-- Or truncate the data before inserting
                    

ERROR 1366: Incorrect Value for Column

Message: Incorrect integer value: 'abc' for column 'id' at row 1

Cause:

You're inserting a value that doesn't match the column's data type (e.g., text into an INT column).

How to Fix:

Make sure the value matches the column type. Check for accidental quotes around numbers or missing quotes around strings.

ERROR 1045: Access Denied

Message: Access denied for user 'username'@'host' (using password: YES)

Common Causes:

• Wrong password
• Wrong username
• User doesn't exist for that host (e.g., 'user'@'localhost' exists but 'user'@'%' does not)

How to Fix:

-- Check if the user exists
SELECT User, Host FROM mysql.user WHERE User = 'username';

-- Reset the password (as root)
ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPassword!';

-- Make sure you're using -p (with password prompt)
-- mysql -u username -p
                    

ERROR 1142: Command Denied

Message: DELETE command denied to user 'user'@'localhost' for table 'books'

Cause:

The user doesn't have the required privilege for the operation.

How to Fix:

-- Check the user's privileges
SHOW GRANTS FOR 'user'@'localhost';

-- Grant the missing privilege (as root)
GRANT DELETE ON bookstore.books TO 'user'@'localhost';
                    

ERROR 2002: Can't Connect to MySQL Server

Message: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Cause:

The MySQL server isn't running.

How to Fix:

# Start the MySQL service
sudo service mysql start

# Check if it's running
sudo service mysql status

# If using systemd:
sudo systemctl start mysql
sudo systemctl status mysql
                    

ERROR 2003: Can't Connect to MySQL Server on 'host'

Message: Can't connect to MySQL server on 'hostname' (111)

Common Causes:

• MySQL isn't configured to accept remote connections
• Firewall blocking port 3306
• Wrong hostname or IP address

How to Fix:

# Check if MySQL is listening on the network
sudo netstat -tlnp | grep 3306

# Edit MySQL config to allow remote connections
# In /etc/mysql/mysql.conf.d/mysqld.cnf:
# Change: bind-address = 127.0.0.1
# To:     bind-address = 0.0.0.0

# Restart MySQL
sudo service mysql restart
                    

ERROR 1290: --secure-file-priv

Message: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Cause:

Your INTO OUTFILE or LOAD DATA INFILE path is outside the allowed directory.

How to Fix:

-- Check the allowed directory
SHOW VARIABLES LIKE 'secure_file_priv';

-- Use the allowed path in your query
-- e.g., /var/lib/mysql-files/
SELECT * INTO OUTFILE '/var/lib/mysql-files/export.csv' ...
                    

ERROR 13: Can't Get Stat / Permission Denied (File Operations)

Message: Can't get stat of '/path/file.csv' (OS errno 13 - Permission denied)

Cause:

The MySQL server process doesn't have permission to read or write the file.

How to Fix:

# Use /tmp or the secure_file_priv directory
# Or adjust file permissions
chmod 644 /path/to/file.csv
chown mysql:mysql /path/to/file.csv
                    

File Already Exists (INTO OUTFILE)

Message: File '/path/file.csv' already exists

Cause:

INTO OUTFILE will not overwrite existing files.

How to Fix:

# Delete the existing file from the terminal
rm /tmp/export.csv

# Then re-run your query
                    

ERROR 3948: LOAD DATA LOCAL INFILE Disabled

Message: Loading local data is disabled; this must be enabled on both the client and server side

How to Fix:

-- Enable on the server
SET GLOBAL local_infile = 1;
                    

# Reconnect with LOCAL enabled
mysql -u root -p --local-infile=1 bookstore
                    

ERROR 1304: Procedure/Function Already Exists

Message: PROCEDURE my_proc already exists

How to Fix:

-- Drop and recreate
DROP PROCEDURE IF EXISTS my_proc;

-- Then create the procedure
DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
    -- ...
END //
DELIMITER ;
                    

ERROR 1418: No DETERMINISTIC or NO SQL

Message: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration

Cause:

Binary logging is enabled and MySQL requires functions to declare their nature.

How to Fix:

-- Add DETERMINISTIC (if the function always returns the same output for the same input)
CREATE FUNCTION my_func(x INT) RETURNS INT
DETERMINISTIC
BEGIN
    RETURN x * 2;
END;

-- Or READS SQL DATA (if it reads from tables but doesn't write)
CREATE FUNCTION my_func(x INT) RETURNS INT
READS SQL DATA
BEGIN
    RETURN (SELECT COUNT(*) FROM books WHERE author_id = x);
END;
                    

DELIMITER Confusion

Symptom: Procedure/function creation fails or only runs the first line.

Cause:

You forgot to change the delimiter before CREATE PROCEDURE / CREATE FUNCTION, so MySQL treats the first semicolon inside the body as the end of the statement.

How to Fix:

-- Always set DELIMITER before and after
DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
    SELECT 1;   -- This semicolon doesn't end the CREATE statement
    SELECT 2;
END //
DELIMITER ;