#3144 – Cannot create a JSON value from a string with CHARACTER SET ‘binary’.

Error #3144 – Cannot create a JSON value from a string with CHARACTER SET ‘binary’.

last day when i was exporting my database, i got above error. I solved the same by below way. I am saving it for my future use. Hope so, this will help someone also.

Suppose i have below table

CREATE TABLE `tbl_order_master` (
`ID` int(11) UNSIGNED NOT NULL,
`Status` enum('C','P','PP') NOT NULL DEFAULT 'PP' ,
`orderinfo` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

when i insert below data it will throw errors.

INSERT INTO `tbl_order_master` (`ID`, `Status`, `orderinfo`)
VALUES
(1,'PP',X'7B2264617461223A207B224F454D223A20224F4E455F415050227D');

#3144 – Cannot create a JSON value from a string with CHARACTER SET ‘binary’.

But when i will change the field `orderinfo` value with CONVERT function for e.g .

INSERT INTO `tbl_order_master` (`ID`, `Status`, `orderinfo`) VALUES (1,'PP', CONVERT(X'7B22666F6F223A2022626172227D' using utf8mb4));

This will insert one row successfully in the database. Below will be the data in the table.

SELECT * FROM `tbl_order_master`