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;

Answer

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, ...)