Mysql Unknown column on existing column (only insert)

Question

I have been researching this problem for quite some time but have not been able to find any helpful results.

I have a table:

CREATE TABLE `jobs` (
    `jb_id` MEDIUMINT(7) UNSIGNED NOT NULL AUTO_INCREMENT,
    `wo_id` MEDIUMINT(7) UNSIGNED NOT NULL,
    `file_name` VARCHAR(140) NOT NULL COLLATE 'latin1_bin',
    `jb_status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    `descr` TEXT NULL COLLATE 'latin1_bin',
    `syncronized` TINYINT(2) UNSIGNED NOT NULL,
    `failedcnt` TINYINT(3) UNSIGNED NOT NULL,
    `clip_title` TINYTEXT NULL COLLATE 'latin1_bin',
    `clip_description` TEXT NULL COLLATE 'latin1_bin',
    `clip_tags` TINYTEXT NULL COLLATE 'latin1_bin',
    PRIMARY KEY (`jb_id`),
    INDEX `woid` (`wo_id`),
    INDEX `job_stat` (`jb_status`),
    INDEX `synced` (`syncronized`),
    INDEX `failedcnt` (`failedcnt`),
    INDEX `file_name` (`file_name`)
)
COLLATE='latin1_bin'
ENGINE=MyISAM;

When i run SELECT or UPDATE commands everything works ok.

select jobs.clip_description from jobs Limit 1;
/* 0 rows affected, 1 rows found. Duration for 1 query: 0.768 sec. */

UPDATE `jobs` SET `clip_description`='test' WHERE  `jb_id`=2 LIMIT 1;

But as I try to run

INSERT INTO `jobs` (`clip_description`) VALUES ('test');
/* SQL Error (1054): Unknown column 'clip_description' in 'field list' */

This also happened yesterday, but as i did not have much time to deal with the issue then, i created new table with different name but same structure, copied over all the data and then renamed both tables and it worked again. That is until about two hours ago when the issue returned. It is not really an option to start coping the table every 12h.

For creating a copy i used:

CREATE TABLE jobs_new LIKE jobs; INSERT jobs_new SELECT * FROM jobs;

After which previously mentioned insert would work.

Any help will be greatly appreciated.

EDIT: If it makes any difference I'm running Server version: 5.5.28-0ubuntu0.12.04.2-log (Ubuntu) On ubuntu server 12.04 LTS 64bit

What happens when you run INSERT INTO jobs SET clip_description = 'test'? — Robbert van den Bogerd
– Robbert van den Bogerd, Commented Mar 12, 2013 at 9:44
INSERT INTO jobs SET clip_description = 'test'; /* SQL Error (1054): Unknown column 'clip_description' in 'field list' */ — madisx
– madisx, Commented Mar 12, 2013 at 9:45
This might be related: stackoverflow.com/questions/12862081/… — Robbert van den Bogerd
– Robbert van den Bogerd, Commented Mar 12, 2013 at 9:47
Are you able to execute the insert query as default value for not null columns is not set? — Angel
– Angel, Commented Mar 12, 2013 at 9:48
@RobbertvandenBogerd Thank you so much for pointing me in that direction. I found a before insert trigger that was not working correctly. — madisx
– madisx, Commented Mar 12, 2013 at 10:00

Mayukh Roy · Accepted Answer · 2013-03-12 09:56:47Z

3

It looks like you have other constrains related to table, may be a trigger or some calculated column depending upon clip_description column. Is not it?

Please check the dependencies and triggers with this table.

answered Mar 12, 2013 at 9:56

Mayukh Roy

1,8173 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

madisx Over a year ago

Yes, added a comment to previous post i found a coworkers trigger that was using this exact column. SET NEW.clip_description = REPLACE(clip_description,'Old','New'); But had to be SET NEW.clip_description = REPLACE(NEW.clip_description,'Old','New');

Collectives™ on Stack Overflow

Mysql Unknown column on existing column (only insert)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related