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