"Column count doesn't match value count at row" but it does

I’ve got a mysql file with over than 14000 statements.
All of them are inserts into a table, and when I import the file using console, it throws the following error:

ERROR 1136 (21S01) at line 1548: Column count doesn't match value count at row 45

I know that error appears when the rows specified in column names are different from the rows specified in VALUES list, but I’ve checked it out many times and the number of columns is exactly the same in both lists.

The row in position 45 is:

('00553', 'AAA', 'BBB', 'CCC', 'XXXXXXXXR', 'user address', 'spain', 'spain', '39212', '1900-01-21', '123456789', 'M', 'No disponible', 0, 'AAA', 'BBB ', 'CCC', 'XXXXXXXXR', NULL, '888993344', '', '', '', '', 'no', 'no') --> 26 columns

and the insert line associated with it, many lines above is:

INSERT INTO `users` (`id_patient`, `name`, `surname1`, `surname2`, `dni`, `address`, `city`, `state`, `postal_code`, `birthday`, `telephone`, `sex`, `email`, `lopd_status`, `lopd_name`, `lopd_surname1`, `lopd_surname2`, `lopd_dni`, `lopd_as`, `mobile_phone`, `notes`, `job`, `company`, `place`, `active_citation`, `signature`)  --> 26 columns

I’ve looked for this error but it seems it only appears when count is different, but in this case, it is the same.

Any idea?

EDIT: forgot to mention: if I throw the sql statement directly on phpmyadmin, it adds the line correctly without any errors. It only crashes when run from command line, although the statements above it are correctly inserted into database and they are almost the same than this one.

EDIT2: I’ve removed all lines until the 46 and when I launch the file the erros is the following:

ERROR 1136 (21S01) at line 1503: Column count doesn't match value count at row 45

It only changes the line, but that line is the INSERT statement, which is exactly the same as the other INSERT statements in the rest of the file. The previos line is also correct.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I faced a similar problem because of inserting parenthesis around data :-(:

INSERT INTO Customers (Name,LastCredit,CreditDate) VALUES ( <-- Here.
    ("Nuclear Millitary Systems",500.0,CURRENT_DATE),
    ("Evil Corporation",67890.95,"2012-02-12"),
    ("Nuke Software Systems",5600.0,"2013-05-06"),
    ("RR Millitary",600.0,"2013-05-06"),
    ("Random Automation",560.0,"2012-05-01"),
    ("Evil Data Systems",600.0,"2013-03-01")
  );

Method 2

I was facing a similar error. It was just the matter of a missing comma. Check if all the items in the insert statement are separated by commas

Method 3

In my case, my database table has a trigger associated to another table, and there was one column missing in triggered table. Something like this:

Table1 columns: UserAccountID, Document type, permissions, Status

Triggered Table 2 columns: Revision number, Revision Status, UserAccountID, Permissions, Status

So in the table 2, “document type” column was missing.
Check the triggers in the DB by

“show triggers from {Database Name}”

Method 4

In my case I had a trigger on INSERT with column mismatch count :(&

Method 5

I met the same issue too. @valplo’s answer is right. Since I don’t have enough reputation to comment, I write it here.

The correct way to insert multiple values is to list the values after the key word VALUES, not parenthesis around multiple value entries, but parenthesis around single value entries, and separate each single value entry with comma.

The wrong way is:

INSERT INTO my_table(column_name_0, column_name_1) VALUES(
    (val_00, val_01),
    (val_10, val_11)
);

The correct way is:

INSERT INTO my_table(column_name_0, column_name_1) VALUES
    (val_00, val_01),
    (val_10, val_11);

Method 6

Check for special hidden characters in the file. You might think that all whitespace in the file are spaces, but sometimes other characters can kill the insert in some way you can’t understand by looking at the data.

If all else fails, retype the INSERT manually and run it in a file by itself to see if it still fails.

Method 7

In my case it was the number of “?” in the prepared statement

prepare stmt1 from  "insert into company (name, address, phone) values(?,?);";
/*note only 2 question marks which should be 3.*/

This also will trigger the same error

insert into company (name, address, phone) values('foo','bar');

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating