How to Fix MySQL Error 1166: Incorrect Column Name

ERROR 1166 (42000): Incorrect column name '<name>' is raised when a column name used in a CREATE TABLE or ALTER TABLE statement contains characters that are syntactically invalid or not permitted by MySQL's identifier rules. The error symbol is ER_WRONG_COLUMN_NAME.

Impact

The DDL statement is rejected entirely and no schema change is applied. The table is not created, and no column is added or modified. Because this is a parse-time validation error, it occurs before any data is touched, so there is no partial state to clean up.

Developers most often encounter this error when executing hand-written migration scripts, using code-generation tools that produce column names from untrusted input, or porting schemas from other databases where identifier rules differ. ORMs such as SQLAlchemy, Hibernate, and ActiveRecord will surface this as a database-level exception during CREATE TABLE or migrate/db:migrate calls, wrapping the underlying 1166 error in a driver-specific exception class.

Common Causes

Trailing or leading spaces in the column name. A name like "email " (with a trailing space) passes the quoting check but fails MySQL's internal column name validation.
Null byte or control characters embedded in the identifier. Some client libraries or string interpolation paths can accidentally inject a null byte (\0) or other non-printable ASCII characters into identifier strings.
Column name ends with a period or contains only whitespace. MySQL rejects names that consist entirely of whitespace or that end with a . character, even when the identifier is backtick-quoted.
Programmatically generated column names that include special characters. If column names are built dynamically from user input or external data, characters such as /, \, ., or null bytes may be injected into the identifier string.
Copy-paste artifacts from word processors or documents. Non-breaking spaces (Unicode U+00A0), smart quotes, or zero-width characters are invisible in many editors but cause MySQL to reject the identifier.
Importing a dump from a case-insensitive filesystem with mixed-case identifiers that collapse to the same name. In some edge cases, the resulting identifier can contain unexpected characters.

Troubleshooting and Resolution Steps

Echo the exact column name to inspect hidden characters. Before issuing DDL, print the column name as a hex string to expose non-printable characters:
```
SELECT HEX('email ');
-- Returns: 656D61696C20  (the trailing 20 is a space)
```
Any hex digit pairs other than expected ASCII values indicate a hidden character.
Strip and trim the column name in your migration script. If names are built programmatically, sanitize them before use:
```
# Python example
col_name = col_name.strip().replace('\x00', '')
```
In SQL-generating code, validate that the name matches ^[a-zA-Z0-9_$]+$ or the broader Unicode identifier pattern before embedding it in DDL.

Use backtick quoting and verify the content is clean. Backtick quoting allows most characters but does not bypass MySQL's column name validation for certain disallowed characters:

-- This still fails because of the trailing space:
CREATE TABLE t (`email ` VARCHAR(255));
-- ERROR 1166 (42000): Incorrect column name 'email '

-- Correct: remove the trailing space
CREATE TABLE t (`email` VARCHAR(255));

Check for non-breaking spaces from copy-pasted DDL. Open the script in a hex editor or run:
```
cat -A migration.sql | grep -n 'M-'
```
Replace any non-ASCII whitespace with a regular space or remove it, then re-run the migration.

Validate column names against MySQL's naming rules programmatically. Column names can be up to 64 characters, must not consist entirely of spaces, must not contain null bytes, and must not end with a space. A simple pre-flight check:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME   = 'your_table'
  AND COLUMN_NAME != TRIM(COLUMN_NAME);
-- Should return 0 rows; any result reveals a column with leading/trailing spaces

Re-export the source schema through a clean tool if migrating from another database. If the error surfaces during an import from PostgreSQL, SQLite, or another MySQL instance, use mysqldump or a schema comparison tool to regenerate clean DDL rather than editing the dump file manually.

Additional Information

The SQLSTATE 42000 (Syntax Error or Access Rule Violation) is shared with many other MySQL syntax errors; the specific error code 1166 is what uniquely identifies an invalid column name.
Error 1059 (ER_TOO_LONG_IDENT) is a related identifier error raised when the column name exceeds 64 characters.
Error 1103 (ER_WRONG_TABLE_NAME) is the table-level equivalent of this error.
MySQL's sql_mode setting does not affect this error — there is no permissive mode that allows illegal column names. The column name must be fixed before the statement will succeed regardless of sql_mode.
The MySQL C API and JDBC driver both expose the 1166 error code directly; most higher-level ORM exceptions will include the original error number and message in the exception detail.

Frequently Asked Questions

Why does MySQL reject a column name that looks valid in my editor? Some characters, such as non-breaking spaces (U+00A0) or zero-width joiners, are invisible in most text editors but are distinct from regular ASCII characters. MySQL's identifier parser rejects these. Use a hex dump or a linting tool to inspect the raw bytes of the column name.

Does backtick-quoting allow any column name? No. Backtick quoting removes the need to avoid reserved words and allows spaces within names, but MySQL still enforces rules against null bytes, names that are entirely whitespace, and names with trailing spaces. You cannot bypass error 1166 by adding backticks alone.

Can this error occur at runtime, not just in migrations? Error 1166 is a DDL-only error triggered during CREATE TABLE, ALTER TABLE, or CREATE INDEX. It will not appear during SELECT, INSERT, UPDATE, or DELETE statements — those produce error 1054 (ER_BAD_FIELD_ERROR) when a column does not exist.

How do I find which column in a large CREATE TABLE statement is causing the error? MySQL's error message includes the offending column name: Incorrect column name '<name>'. Search the DDL for that exact string (using a hex-aware search if needed). If the name appears correct visually, inspect it with HEX() or a hex editor to locate the hidden character.