Bug #27955 error of unknown column 'xxx' in field list but column 'xxx' isnt in the query
Submitted: 19 Apr 2007 14:33 Modified: 19 May 2007 18:16
Reporter: Regi Guardialao Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0.37 OS:Linux (FC5)
Assigned to: Assigned Account CPU Architecture:Any
Tags: bad parsing, replication, Unknown column

[19 Apr 2007 14:33] Regi Guardialao
Description:
Ver 14.12 Distrib 5.0.37

This is a slave server and it fails with this query

Our query is:

INSERT INTO `events` (`campaigndetailid`,`reasonid`,`termcodeid`,`tmid`,`itemsold`,`amountsold`,`datecontacted`,`timecontacted`,`duration`,`billsec`,`notes`,`cdrlink`,`targetdetailid`,`campaignid`,`eventtypeid`,`eventstateid`,`actionrequired`,`pmdetailid`) VALUES ('11679675','2','20','55','','0','2007-04-19','02:18:37','35','35','','1176974286.9357','4706316','967','8','0','0','0');

The error is:

ERROR 1054 (42S22): Unknown column 'termcodedid' in 'field list'

Problem:

As you can see, there is no 'termcodedid' column in the query, its `termcodeid`

Describe table shows:

*************************** 1. row ***************************
  Field: eventid
   Type: int(9) unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: campaigndetailid
   Type: int(9) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 3. row ***************************
  Field: targetdetailid
   Type: int(9) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 4. row ***************************
  Field: campaignid
   Type: mediumint(7) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 5. row ***************************
  Field: reasonid
   Type: smallint(6) unsigned
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 6. row ***************************
  Field: termcodeid
   Type: smallint(6) unsigned
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 7. row ***************************
  Field: agentid
   Type: smallint(6) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 8. row ***************************
  Field: tmid
   Type: smallint(6) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 9. row ***************************
  Field: itemsold
   Type: varchar(250)
   Null: NO
    Key:
Default:
  Extra:
*************************** 10. row ***************************
  Field: numbersold
   Type: smallint(9)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 11. row ***************************
  Field: amountsold
   Type: float
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 12. row ***************************
  Field: datecontacted
   Type: date
   Null: NO
    Key: MUL
Default: 0000-00-00
  Extra:
*************************** 13. row ***************************
  Field: timecontacted
   Type: time
   Null: NO
    Key:
Default: 00:00:00
  Extra:
*************************** 14. row ***************************
  Field: dateofevent
   Type: date
   Null: NO
    Key: MUL
Default: 0000-00-00
  Extra:
*************************** 15. row ***************************
  Field: timeofevent
   Type: time
   Null: NO
    Key:
Default: 00:00:00
  Extra:
*************************** 16. row ***************************
  Field: duration
   Type: smallint(6)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 17. row ***************************
  Field: billsec
   Type: smallint(6)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 18. row ***************************
  Field: notes
   Type: text
   Null: NO
    Key:
Default:
  Extra:
*************************** 19. row ***************************
  Field: cdrlink
   Type: varchar(60)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 20. row ***************************
  Field: attachedrecording
   Type: varchar(250)
   Null: NO
    Key:
Default:
  Extra:
*************************** 21. row ***************************
  Field: eventtypeid
   Type: smallint(6) unsigned
   Null: NO
    Key: MUL
Default: 0
  Extra:
*************************** 22. row ***************************
  Field: eventstateid
   Type: smallint(6) unsigned
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 23. row ***************************
  Field: actionrequired
   Type: tinyint(4)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 24. row ***************************
  Field: potentialrevenue
   Type: varchar(150)
   Null: NO
    Key:
Default:
  Extra:
*************************** 25. row ***************************
  Field: potentialclosedate
   Type: date
   Null: NO
    Key:
Default: 0000-00-00
  Extra:
*************************** 26. row ***************************
  Field: confidence
   Type: float
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 27. row ***************************
  Field: refid
   Type: int(9) unsigned
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 28. row ***************************
  Field: rdayofmonth
   Type: varchar(50)
   Null: NO
    Key:
Default:
  Extra:
*************************** 29. row ***************************
  Field: rdayofweek
   Type: varchar(50)
   Null: NO
    Key:
Default:
  Extra:
*************************** 30. row ***************************
  Field: pmdetailid
   Type: mediumint(7) unsigned
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 31. row ***************************
  Field: incentive
   Type: float
   Null: NO
    Key:
Default: 0
  Extra:
31 rows in set (0.00 sec)

Measures Taken:

i tried restarting the server, checking , analyzing , optimizing the table
all to no avail.

Everytime I run the query, this error occurs. Before this, this query runs fine.

Only this slave has this error. All other slaves work as normal.

Please advice.

How to repeat:
I know this cannot be repeated since when i dumped this to a new table and tried the query, it worked normally.

I just want to let you know that this is happening to our mysql.

Suggested fix:
Dump the table and reload it
re-image from master
[19 Apr 2007 16:02] MySQL Verification Team
Thank you for the bug report. Have you followed the instructions
recommend in our Manual:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

when upgrading from 4.1 to 5.0. Otherwise please do.
[19 Apr 2007 17:08] Regi Guardialao
This is a fresh install of mysql 5 on this server.

Sorry, what i meant was, since we started using 5.0 almost a year ago, these server crashes became frequent.

The tables have been re-dumped, recreated, and repaired more than a dozen times, especially big tables, (700MB - 5GB) since they are the ones that frequently crash.

Yes, we followed the steps religiously for updating to 5.0, since we didnt want to lose our data too. Also, this is a 64-bit server with 4GB of memory.

I hope that helps.

Good luck.
[19 Apr 2007 18:16] Sveta Smirnova
Thank you for the feedback.

Please upload to our FTP server master binary log file contains problem query and queries issued before corruption happened, corrupted table (*.frm, *.MYD, *MYI files) and configuration files for both master and slave.

Instructions how to upload file to our server you can find at http://bugs.mysql.com/bug.php?id=27955&files=1 Or just read a quote:

Create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (example: bug-data-27955.zip), and use FTP to upload the archive to ftp://ftp.mysql.com/pub/mysql/upload/. Once you have uploaded the file, add a comment to this bug to notify us about it. Note: This directory is unlistable, which means that once you have uploaded your file, you will not be able to see it.
[19 May 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".