Error 1136

This simple problem drove me nuts yesterday. I can recreate the problem. Take this simple table I just created using php MyAdmin:

Code: Select all

CREATE  TABLE  `history` ( `history_id` SMALLINT UNSIGNED NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY ,
 `content` TEXT NOT  NULL ,
 `day` DATE NOT  NULL 
) ENGINE  =  MYISAM ;

MySQL automatically assigns a default "0000-00-00" to the 'day' column and collation latin1_swedish_ci to the 'content'.

I now run the following simple query directly in SQL:

Code: Select all

INSERT INTO history (content, date) VALUES ('blahblah'),('2006-08-03');

and I get the following error message:

Code: Select all

SQL query:     
              INSERT  INTO history( content, DATE ) 
VALUES ( 'blahblah'
), ( '2006-08-03'
);

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

Herlp!
I have a lot of tables in this database of all sizes and shapes, some of them with the same format DATE. They all work great -- I have never seen this before. I have even tried copying other (functional) tables' structures and then eliminating columns, and I still come down to this blasted 1136 error.

PS - I don't think it has anything to do with the INSERT query, as I have tried every size and shape of query I could come up with.

Well I finally got this to work but just by tinkering -- I still have no clue as to what's going on.

I changed the default date to 2006-01-01, which didn't do it. However, the table accepted the following query:

Code: Select all

INSERT INTO `history` (`history_id`, `content`, `day`) VALUES (NULL, '<li>more blah blah', '2006-08-06');

I'd still like to figure out why inserting a NULL value into the primary key makes this work -- I've never had to do anything like that before. The insertion was perfect and gave me a correct auto-incremented value for 'history_id'.

As i understand it the reason

INSERT INTO history (content, date) VALUES ('blahblah'),('2006-08-03');

doesnt work is that VALUES ('blahblah'),('2006-08-03'); should be
VALUES ('blahblah', '2006-08-03'), but sepparating the blah and date with the () makes mysql thinks you are trying to insert another row into the history table, and so of course you dont have the same number of input values to inputfields specified. This is useful though cos you can do this

INSERT INTO history (content, date) VALUES ('content1', '2006-08-03'), ('content2', '2006-07-05'),

which will insert two rows in one query

Gee Bee has it right. The reason for the 1136 error is the total amount of fields does not match when trying to INSERT a value in a table.

Thanks for pointing that out. As is true 99% of the time, it isn't some erudite point of programming, just a dumb syntax error.