MySQL 5.7.12 import cannot create a JSON value from a string with CHARACTER SET 'binary'

Danny Bevers picture Danny Bevers · Jun 28, 2016 · Viewed 41.3k times · Source

I exported my database with JSON columns in it. After I migrated to a new server, my import crashed every time with an error like:

cannot create a JSON value from a string with CHARACTER SET 'binary'

On stackoverflow, I found this post but didn't work for me: mysqlimport issues "set @@character_set_database=binary" which prevents loading json values

The file is 2GB and isn't possible to open the file.

Anyone has an idea to import my database file?

Answer

Lorcan O'Neill picture Lorcan O'Neill · Feb 3, 2017

You can apply a regex to the SQL text which you exported which will convert your binary strings into an insertable format. This was my quick and dirty fix when I faced this issue

(X'[^,\)]*')
CONVERT($1 using utf8mb4)

Applying this regex means

INSERT INTO json_table (json_column) VALUES (X'7B22666F6F223A2022626172227D');

will now become

INSERT INTO json_table (json_column) VALUES (CONVERT(X'7B22666F6F223A2022626172227D' using utf8mb4));