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

Question

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 1

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));