MySQL Bugs: #1689: Unknown column in 'field list' for a field that does exist

Bug #1689	Unknown column in 'field list' for a field that does exist
Submitted:	28 Oct 2003 4:46	Modified:	28 Oct 2003 6:36
Reporter:	Anthony Marston	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When I try the following statement:

INSERT INTO survey_answer_dtl SET survey_answer_id='1', survey_id='1', section_id='1', question_id='1', answer_text='1 High Street New Malden Surrey', weighting_id='0', measure_adequate='N', created_date='2003-10-27 21:15:30', created_user='AJM', revised_date=NULL, revised_user=NULL

it fails with "Unknown column 'weighting_id' in 'field list' " even though the field 'weighting_id' does exist in that table.

I found that by swapping that field's position in the table with another field that it worked OK.

How to repeat:
This database structure causes the problem on INSERT:

DROP TABLE IF EXISTS `survey_answer_dtl`;
CREATE TABLE `survey_answer_dtl` (
  `survey_answer_id` int(10) unsigned NOT NULL default '0',
  `survey_id` smallint(5) unsigned NOT NULL default '0',
  `section_id` smallint(5) unsigned NOT NULL default '0',
  `question_id` smallint(5) unsigned NOT NULL default '0',
  `answer_text` varchar(255) NOT NULL default '',
  `weighting_id` tinyint(3) unsigned default NULL,
  `measure_adequate` char(1) NOT NULL default '',
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) NOT NULL default '',
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`survey_answer_id`,`survey_id`,`section_id`,`question_id`)
) TYPE=MyISAM;

This database structure does NOT cause the problem on INSERT:

DROP TABLE IF EXISTS `survey_answer_dtl`;
CREATE TABLE `survey_answer_dtl` (
  `survey_answer_id` int(10) unsigned NOT NULL default '0',
  `survey_id` smallint(5) unsigned NOT NULL default '0',
  `section_id` smallint(5) unsigned NOT NULL default '0',
  `question_id` smallint(5) unsigned NOT NULL default '0',
  `answer_text` varchar(255) NOT NULL default '',
  `measure_adequate` char(1) NOT NULL default '',
  `weighting_id` tinyint(3) unsigned default NULL,
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) NOT NULL default '',
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`survey_answer_id`,`survey_id`,`section_id`,`question_id`)
) TYPE=MyISAM;

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

With both your table structures INSERT statement worked well.
Can you provide more information?

I forgot and also you forgot, to configure correctly the connection with localhost(host, user, password and maybe database).
AND in your scripts you got something like >>> $result = mysql_query($query, $connection) or die(mysql_error());.
The "die(mysql_error())" do the "Unknown column 'new_column' in 'field list'" error

Just got this error on MySQL 4.1.15 on Debian GNU/Linux.

The error message was:
Unknown column 'asin' in 'field list'

When performing:
SELECT title, asin, isbn FROM fiction ORDER BY title;

Given a fiction table:
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| number | int(11)     |      | PRI | NULL    | auto_increment |
| isbn   | varchar(10) | YES  |     | NULL    |                |
| title  | varchar(64) | YES  |     | NULL    |                |
| asin   | varchar(10) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

A restart of the MySQL server seems to have fixed the problem.

Hi mike,

The same case happened to me also. If i launch the server for the first time i get the "Unknown column in field list" exception. But if i stop and again relaunch, the field is properly getting updated.

How to rectify it?

Hi there, I had the same issue and it was doing my head in!

I found the cause though; it is to do with using grave accents instead of straight quote mark/apostrophes in the query. 

e.g. `NULL` instead of 'NULL'

I had the problem when I copied and pasted a list of fields from an SQL manager.

I hope this works

I was trying to load a string value (without quotes) in an 'int' column and I got this error. Error disappeared after I included the quotes though (and the value got truncated to zero).

After I changed `` to ''. It works :)

This should not be regarded as a bug, instead it should be verified as a syntax error. Let me try to explain it this way..

[1]Looking at the syntax for "insert into" first..,

insert into table_name values('string1','string2','string3');

--> if the values are strings, must be always placed between ' and ' ('string')

[2] if the values are string variables instead(such as in the case when we get those from a form submit), "insert into" looks like this..,

insert into table_name values($str1,$str2,$str3);
-->
* $str1="string1"; //but when $str1 is been placed inside the bracket in the above command, it does not take the " and " in to the bracket to surround itself.Therefore it can be manipulated like below..,

insert into table_name values(string1,string2,string3);

--> This is a syntax error since there is no ' and' (or "and") surrounding the strings.This do not happen if your data are numbers,because the numbers do not need to be surrounded with 'and'(or "and").

My solution to the problem: concatonate ' and ' at the left and right of the string variable before process.

$str1="'".$str1."'";

It worked for me :)

hey check your column name in you table don't have white space ex: "COSTUMER" -- 
" COSTUMER"

Reiterating what Piyasiri M.P. said

This php will give the error:

$somedata='a string';
mysql_query("INSERT INTO Mytable (somecolumn,$somedata)");

This php will not: (note the '' around $somedata)

$somedata='somedata';
mysql_query("INSERT INTO Mytable (somecolumn,'$somedata')");

I have the same problem. The error is:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'filename' in 'field list'

Yet filename DOES in fact exist in my table. It is a plain string and I have checked for any unprintable characters before the filename word..

Also facing similar issue , no syntax errors !! anyone found root cause of this ?