Entry

Database: Relational: MySql: Error 1136 (21S01): Column count doesn't match value count at row 1

May 2nd, 2008 21:41
dman, Knud van Eeden, Simon Ndira, http://sturly.com

----------------------------------------------------------------------
--- Knud van Eeden --- 22 May 2005 - 08:57 pm ------------------------

Database: Relational: MySql: Error 1136 (21S01): Column count doesn't 
match value count at row 1

---

Possible cause:

The total amount of fields does not match
when trying to INSERT a value in a table.

---

e.g.

--- cut here: begin --------------------------------------------------

-----------------------------------------------
DROP TABLE IF EXISTS table1;
-----------------------------------------------
CREATE TABLE
 table1
(
 columnId INT AUTO_INCREMENT PRIMARY KEY,
 column1  VARCHAR( 50 ),
 column2  VARCHAR( 50 ),
 column3  VARCHAR( 50 )
)
;
-----------------------------------------------
-- inserting a value with correct 4 fields totally
INSERT INTO
 table1
VALUES
 ( 1, "test1", "test2", "test3" )
;
-----------------------------------------------
-- inserting a value with not correct 3 fields totally
INSERT INTO
 table1
VALUES
 ( 2, "test4", "test5" )
;
-----------------------------------------------

=> ERROR 1136 (21S01): Column count doesn't match value count at row 1

--- cut here: end ----------------------------------------------------

---

Possible solution:

Count the total amount of fields in the SQL query
INSERT statement, and correct this.

===

Note:

This could e.g. happen if you have a binary BLOB stored in a field in a
table, and then after say a backup you want to recreate your database
again. Maybe the binary information in the BLOB
field is not correctly read back into the database.

e.g. using copy/paste of an SQL query

If the length of the INSERTed information is too long, MySql might just
cut that off after a certain amount of
characters.

What you better do then is store the information in a file,
and read this information via

 source <your filename>;

===

Internet: see also:

---

Database: MySql: Command: Multiple: Execute: How to execute multiple 
commands at once in MySql?
http://www.faqts.com/knowledge_base/view.phtml/aid/36416/fid/52

---

Database: Relational: MySql: Link: Overview: Can you give an overview 
of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/35331/fid/52

----------------------------------------------------------------------

===

Simon Ndira:

The feedback regarding this error is only helpful if there are very few
lines of record to update. I am trying to upload about 12.000 lines of
recrods affecting different tables that were edited in the course of
time. How can someone work around error 1136 in such an environment?