CODEWITHSUNDEEP
mysqlmysql-5.7
eComEvo
eComEvo

Reputation: 12551

Unknown error 3140 Invalid JSON text: "Invalid value." at position 0 but for a NULL column

SQLSTATE[22032]: <<Unknown error>>: 3140 Invalid JSON text: "Invalid value." at position 0 in value for column 'form_fields.options'. (SQL: INSERT INTO `form_fields` VALUES (1, 'first_name', 'First Name', 6, 1, 'text', NULL, 'string', NULL, NULL, NULL, '2019-05-28 16:44:08', '2019-05-28 16:44:08', NULL);)

The options column in this query is NULL so I can't figure out how it's getting an "invalid value" error on position 0.

We have plenty of NULL values in that column presently and the column is defined as nullable.

I'm literally just taking a dump of the table from one DB and trying to run inserts on another identical DB to seed it.

Table structure:

CREATE TABLE `form_fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `required` tinyint(1) NOT NULL DEFAULT '0',
  `type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subtext` text COLLATE utf8mb4_unicode_ci,
  `validations` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `options` json DEFAULT NULL,
  `html_attributes` json DEFAULT NULL,
  `display_width` int(11) NOT NULL DEFAULT '12',
  `gender` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `form_fields_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Upvotes: 0

Views: 22556

Answers (3)

bcag2
bcag2

Reputation: 2439

in my case :

INSERT INTO `my_table` (`my_attribute`)
VALUES ('{\"id\": 23, \"gis_table\": \"v_continent\"}');

or

INSERT INTO `my_table` (`my_attribute`)
VALUES (JSON_OBJECT("id",23,"gis_table","v_continent"));

Upvotes: 0

Nazikmen
Nazikmen

Reputation: 1

Request:

INSERT INTO `messages`(`data`, `only_text_msg`, `media_group_id`, `media_group_json`, `forward_date`) VALUES ('1','None','secret_id','{\'media\': [\'media_id\']}','2022-03-27 12:09:37')

The problem is that in the database json cell, need to be written with double brackets " that is, the json itself needs to be wrapped in something like this function:

*Python

def json_to_string(json):
    js = str(json)
    ret_str = ""
    for i in js:
        f = ""
        if i == "\"" or i == "'":
        if i == "'":
            i = "\""
        f = "\\"
    ret_str += f+i
    return ret_str

and some code else:

lol = {
    "asda" : '2w',
    1 : 32,
    '2' : 22
}
import json
print(json.dumps(lol))

{"asda": "2w", "1": 32, "2": 22}

Upvotes: 0

derpirscher
derpirscher

Reputation: 17372

In your table definition, options is the 8th column, yet the 8th value in your insert is not NULL but 'string'. Others don't match either. The 5th column is a VARCHAR, but the 5th value is a number.

Try enumerating the column names in your insert to make sure the correct column order is used.

 INSERT INTO table(col1, col2 ...) VALUES ('val1', 2, NULL, ...)

Upvotes: 1

Related Questions