Error
Error Code:
1166
MariaDB Error 1166: Invalid Column Name Specified
Description
Error 1166 in MariaDB indicates that a SQL statement attempted to reference a column name that either does not exist in the specified table or is syntactically incorrect. This typically occurs during data manipulation (DML) or data definition (DDL) operations when the column name provided does not match any existing column.
Error Message
Incorrect column name '%s'
Known Causes
3 known causesTypographical Error
A simple spelling mistake or typo in the column name within your SQL query.
Column Does Not Exist
The specified column was never created in the table, has been dropped, or belongs to a different table than intended.
Case Sensitivity Mismatch
The column name's casing in the query does not match the actual column name in a case-sensitive database environment or collation.
Solutions
4 solutions available1. Verify Column Name Spelling and Case Sensitivity easy
Double-check the column name for typos and ensure it matches the table definition, paying attention to case sensitivity if applicable.
1
Examine the SQL query that is generating the error. Identify the column name that is reported as invalid (the '%s' placeholder in the error message).
Example: SELECT `invalud_column` FROM `my_table`;2
Connect to your MariaDB server and describe the table structure to see the exact column names.
DESCRIBE your_table_name;3
Compare the column name in your query with the output of `DESCRIBE`. Correct any spelling mistakes or case mismatches. Remember that table and column names can be case-sensitive depending on the operating system and MariaDB configuration (`lower_case_table_names` variable).
Corrected Example: SELECT `invalid_column` FROM `my_table`;2. Check for Reserved Keywords easy
Ensure the column name is not a MariaDB reserved keyword that requires quoting.
1
Identify the column name causing the error in your SQL statement.
Example: SELECT `select` FROM `my_table`;2
Consult the MariaDB documentation for a list of reserved keywords. Common examples include `SELECT`, `FROM`, `WHERE`, `ORDER`, `GROUP`, `TABLE`, `COLUMN`, etc.
Refer to: https://mariadb.com/kb/en/reserved-words/3
If the column name is a reserved keyword, enclose it in backticks (`) to escape it.
Corrected Example: SELECT `select` FROM `my_table`;3. Confirm Column Existence in the Target Table medium
Verify that the column actually exists in the specific table you are querying.
1
Review your SQL query and identify the table and column names involved.
Example: SELECT `non_existent_column` FROM `users`;2
List all tables in the database to ensure you are targeting the correct one.
SHOW TABLES;3
Describe the structure of the table you are querying to confirm the existence of the column.
DESCRIBE users;4
If the column does not exist, you will need to either add it to the table or correct your query to use an existing column.
To add a column: ALTER TABLE `users` ADD COLUMN `existing_column` VARCHAR(255);5
Alternatively, correct the query to reference a valid column.
Corrected Example: SELECT `username` FROM `users`;4. Investigate Table Aliases in Joins medium
Ensure that column names are correctly qualified with table aliases when performing joins.
1
Examine SQL queries involving joins. Identify the tables and their aliases.
Example: SELECT u.usernaem FROM users u JOIN orders o ON u.id = o.user_id;2
Carefully check if the column name is preceded by the correct table alias and a dot (.).
Example of incorrect qualification: SELECT u.usernaem FROM users u JOIN orders o ON u.id = o.user_id;3
Verify that the column actually exists in the table represented by the alias.
Use `DESCRIBE table_name;` for each table involved in the join.4
Correct any typos in the column name or the alias, and ensure proper qualification.
Corrected Example: SELECT u.username FROM users u JOIN orders o ON u.id = o.user_id;