MySQL Error: ON DUPLICATE KEY UPDATE, column count doesn't match value count at row

Question

Can anyone see why I'm getting this error is causing an error:

#1136 - Column count doesn't match value count at row 1

Here is the query:

INSERT INTO `people` 
(`id`,`title`,`first_name`,`middle_initial`,`preferred_name`,`last_name`,
`home_phone`,`mobile_phone`,`email`,`gender`,`date_of_birth`,`qff`,`status`) 

VALUES ('20','Mr','first','mid','pref','fam',
'home','mobile','email','male','0000-00-00','qff','active') 

ON DUPLICATE KEY UPDATE 
`people`.`id` = LAST_INSERT_ID(`people`.`id`), 
`people`.`title` = 'Mr', 
`people`.`first_name` = 'first', 
`people`.`middle_initial` = 'mid', 
`people`.`preferred_name` = 'pref', 
`people`.`last_name` = 'fam', 
`people`.`home_phone` = 'home', 
`people`.`mobile_phone` = 'mobile', 
`people`.`email` = 'email', 
`people`.`gender` = 'male', 
`people`.`date_of_birth` = '0000-00-00', 
`people`.`qff` = 'qff', 
`people`.`status` = 'active'

And the table structure:

CREATE TABLE `people` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` text,
  `first_name` text,
  `middle_initial` text,
  `preferred_name` text,
  `last_name` text,
  `home_phone` text,
  `mobile_phone` text,
  `email` text,
  `gender` enum('male','female') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `qff` varchar(20) NOT NULL,
  `status` enum('active','inactive') NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int(10) unsigned DEFAULT NULL,
  `updated_by_type` enum('person','admin') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I cannot reproduce this problem. I created the table and inserted a few rows and everything went fine (I changed the table name to stack_people, no other changes were made). Can you reproduce the error when you start with a clean state? Is the query 100% the same as the one that causes the error? — Aleksi Yrttiaho, Feb 20 '11 at 02:26
Cannot reproduce error. What version of MySql are you using? — The Scrum Meister, Feb 20 '11 at 02:27
@Aleksi, what the hell, it worked when I create a new DB/table. And yes I'm 100% sure that it is the same query that causes the error. — Petah, Feb 20 '11 at 02:43
@petah Is it possible that one of the fields are not being escaped properly and have a `)` or a `,` in them? — The Scrum Meister, Feb 20 '11 at 02:46
As the scrum meister said, can you isolate the actual parameters used when the query caused an error? Can you still reproduce it using the original context? — Aleksi Yrttiaho, Feb 20 '11 at 02:49
Thanks guys, but the problem was to do with a trigger, the query itself was fine. — Petah, Feb 20 '11 at 02:55

score 4 · Accepted Answer · answered Feb 20 '11 at 02:51

4

I had the exact same problem a while ago - for me the issue was related to a trigger on the table in question.

answered Feb 20 '11 at 02:51

Michael Robinson

29,278
12
104
130

Yes that was the problem. I had an on update trigger that was out of sync with the table. – Petah Feb 20 '11 at 02:54

score -1 · Answer 2 · edited Aug 08 '19 at 07:32

Recently I had the same problem, But I used batch insert/update ，my problem is not about trigger , its the 'foreach' problem, if u used the total 'foreach' like

<foreach collection="meters" index="index" item="meter" open="(" close=")" separator=",">
</foreach>

but the error code is
Error Code: 1136. Column count doesn't match value count at row 1 0.000 sec

for my test it will add another () for your code (I didn't check the log). so we can user

< foreach collection="medichines" index="index" item="medichine" separator=",">

    (
    )

< /foreach>

this way can fix you error

MySQL Error: ON DUPLICATE KEY UPDATE, column count doesn't match value count at row

2 Answers2

Linked