MySQL Bugs: #27955: error of unknown column 'xxx' in field list but column 'xxx' isnt in the query

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

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

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.

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.

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.

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".