Unknown column ‘smth’ in ‘field list’ -> Oldie but goodie error

There is an error which is definitely new to people which have encountered such situation at first time.
It was reported a long time before:
#1689 -> 28 Oct 2003 4:46
#1478 -> 4 Oct 2003 10:58
#7501 -> 23 Dec 2004 5:35

Error is -> ERROR 1054 (42S22): Unknown column ‘c2’ in ‘field list’
The Place where you can encounter this error is something similar below:

mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(85) DEFAULT NULL
) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

If you try to insert data with wrong column name:

mysql> INSERT INTO t1(id, c2) VALUES (0, 'xxxxx');
ERROR 1054 (42S22): Unknown column 'c2' in 'field list'

But recently there was a question in our local community related to this error.
Suddenly i remembered this old but still good error 🙂
The reason was forgotten single quotes:

mysql> INSERT INTO t1(id, c1) VALUES (0, BEA10273D8);
ERROR 1054 (42S22): Unknown column 'BEA10273D8' in 'field list'

i wrote a simple Python script simulating this error(Showing application use case):

import mysql.connector
import uuid

cnx = mysql.connector.connect(user='all', password='12345',

cursor = cnx.cursor()

def my_random_string(string_length=10):
    """Returns a random string of length string_length."""
    random = str(uuid.uuid4()) # Convert UUID format to a Python string.
    random = random.upper() # Make all characters uppercase.
    random = random.replace("-","") # Remove the UUID '-'.
    return random[0:string_length] # Return the random string.

sql = "INSERT INTO t1(id, c1) VALUES ({}, {})"

for i in range(10):

    ids = i
    strings = my_random_string(10)

    sql2 = sql.format(ids, strings)
    print sql2


If you try to run:

sh@sh--work:~$ python insert2_577.py
INSERT INTO t1(id, c1) VALUES (0, BEA10273D8)
Traceback (most recent call last):
  File "insert2_577.py", line 32, in <module>
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 491, in execute
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 683, in cmd_query
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 601, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'BEA10273D8' in 'field list'

Not so confusing!
Just do not forget to put necessary single quotes (”).

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group and Python user group leader. QA Engineer, bug hunter by nature and true Pythonista

2 thoughts on “Unknown column ‘smth’ in ‘field list’ -> Oldie but goodie error”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.