MySQL : Copy / Duplicate a ROW with JSON column copied the data as Binary, which is NOT valid JSON value #2647

Open
mattbryson opened this Issue Jan 4, 2017 · 0 comments

Projects

None yet

2 participants

@mattbryson

If you try to duplicate a row with a JSON data typed column in it, or copy the SQL INSERT string and then run it, it will fail with the error..

Cannot create a JSON value from a string with CHARACTER SET 'binary'.

This is because the default format of the JSON column is copied as BINARY but it needs to be copied as UTF8MB4.

The generated SQL from the console is...

INSERT INTO `my_table` (`name`, `config`) 
VALUES ('test', X'7B22736F6D655F6A736F6E223A20223132333435227D');

Where it should be this for JSON data type

INSERT INTO `my_table` (`name`, `config`) 
VALUES ('test', '{\"some_json\": \"12345\"}');
@stuconnolly stuconnolly added the Bug label Jan 7, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment