Column count doesn’t match value count at row 1

by

In this article, we will explore the errors column count doesn’t match value count at row 1 that arise when using the JSON datatype in MariaDB.

Column count doesn’t match value count at row 1

In MariaDB, the JSON datatype can’t be one of the following data types (invalid, Function, or Date).

I have executed the queries below.

CREATE TABLE Json_demo(
user_name varchar(20) not null,
json_value json
CHECK(JSON_VALID(json_value)));

INSERT INTO JSON_DEMO(user_name,json_value)
values('David_TS','invalid');

SELECT * FROM JSON_DEMO;

After executing the second query, this error occurred when inserting a long text value or a string of JSON datatype into the JSON_VALUE column of the JSON_DEMO table using the INSERT INTO statement. Check out the screenshot below for your reference.

Column count doesn't match value count at row 1

Why Does This Error Occur?

This error typically happens due to one or more of the following reasons:

  • Mismatch between columns and values: You’re inserting data into a subset of columns but providing a different number of values.
  • Omitting the column list: When you omit the column list in an INSERT statement, MySQL expects values for all columns in the table, in order.
  • Incorrect use of DEFAULT or NULL values: Missing values for columns without defaults or NOT NULL constraints.
  • Triggers or stored procedures: Sometimes triggers or procedures modify the insert operation, leading to mismatches.
  • Improper multi-row insert syntax: The number of values in each row differs in a multi-row insert.

Solution

Now, let’s fix the error of the JSON data type in the JSON_DEMO table with the following query:

CREATE TABLE Json_demo(
user_name varchar(20) not null,
json_value json
CHECK(JSON_VALID(JSON_VALUE)));

INSERT INTO JSON_DEMO(user_name,json_value)
values('JohnY_K','{"json_id":2,"json_name":"George"}');

SELECT * FROM Json_demo;

In the preceding query after the CREATE TABLE statement of the JSON_DEMO table, we have inserted another record in the JSON_DEMO table by using the INSERT INTO statement. In the json_value column, we have inserted a string or long text value, such as “json_id:2, json_name: George”.

To retrieve all records from the JSON_DEMO table, we will use the SELECT statement. You can see there is no error this time and can insert the record successfully, as shown in the screenshot below.

mariadb column count doesn't match value count at row 1

You may also like the following articles.