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:Microsoft Windows (Windows XP)
Assigned to:

[28 Oct 2003 4:46] Anthony Marston
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;
[28 Oct 2003 6:36] Alexander Keremidarski
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?
[21 Feb 2005 13:43] Luiggi ZAMOL
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
[21 Aug 2006 3:42] Mikel Ward
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    |                |
+--------+-------------+------+-----+---------+----------------+
[21 Aug 2006 3:47] Mikel Ward
A restart of the MySQL server seems to have fixed the problem.
[3 Jan 2007 7:19] Fredy Xavier
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?
[7 May 2007 1:37] Ben Slater
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
[28 Feb 2008 20:38] Kage Bunshin
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).
[19 Feb 2010 2:11] Pwint Phyu Shwe
After I changed `` to ''. It works :)
[17 Aug 2011 15:47] Piyasiri M.P.
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 :)
[30 May 2012 1:16] yhomarth reyes
hey check your column name in you table don't have white space ex: "COSTUMER" -- 
" COSTUMER"
[31 Jul 2012 1:11] Justin Ford
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')");