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?

score 156 · Accepted Answer · answered Feb 03 '17 at 17:35

156

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

answered Feb 03 '17 at 17:35

Lorcan O'Neill

3,303
1
25
24

74

For grins, here's the non-inplace `sed` version: `sed -E "s/(X'[^,\)]*')/CONVERT(\1 using utf8mb4)/g" infile.sql > outfile.sql` – Ray Aug 30 '17 at 09:22
The regex in Ray's comment did not work for me so I had to use the following regex: `sed -E "s/(0x'[^,\)]*')/CONVERT(\1 using utf8mb4)/g" infile.sql > outfile.sql` – kapad May 22 '19 at 08:51
2

The regex in Ray's comment did not work for me so I had to use the following regex: `sed -E "s/(0x'[^,\)]*')/CONVERT(\1 using utf8mb4)/g" infile.sql > outfile.sql` – kapad May 22 '19 at 08:52
this works <3, how should I handle 40K rows? I can't use multiselect – Eliseo Mar 02 '21 at 18:10
@Eliseo use the sed version in Ray's comment to process the entire dump file at once. – jgangso Apr 06 '21 at 18:31

score 111 · Answer 2 · answered Mar 28 '18 at 21:01

111

I had this problem dealing with exports made by Sequel Pro. I unchecked the Output BLOB fields as hex option and the problem went away. Visually inspecting the export showed legible JSON instead of binary.

answered Mar 28 '18 at 21:01

Henry

7,721
2
38
38

15

Tried this, but some field had a quote. Sequel Pro export escapes this as `\"`, but this should be `\\"`. So basically: if you have quotes in your JSON data, you're out of luck with Sequel Pro. – Giel Berkers May 17 '18 at 08:55
1

Tha's what I needed. Thanks! – Noogic Jun 07 '18 at 09:44
4

Thank you. I just wish Sequel Pro would get an update. – user10971950 Nov 08 '19 at 15:24

score 26 · Answer 3 · answered Jan 28 '19 at 15:08

I faced the same issue today. Below were the findings for my case,

I asked one of my friend to generate an SQL dump for me to import. He used sequel-pro to generate the dump (export database). When I did the import it threw an error

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

So, there was an issue with the generated dump, all the json fields were converted to some raw format i.e. instead of value being

"{'key1':'value1', 'key2':'value2'}"

it was,

X'nfdsklsdsklnfjkbvkjsdbvkjhdfsbvkjdsbnvljkdsbvkjhdfbvkjdfbvjkdfb'

So, when importing the dump i.e. running the insert statements mysql could not process the data as it was not of json type.

Here is a link to the bug reported
https://github.com/sequelpro/sequelpro/issues/2397

You need to uncheck the Output BLOB fields as hex option.

score 13 · Answer 4 · answered Jul 05 '19 at 02:25

13

vim version For Lorcan O'Neill's answer

vi xxxx.sql
:%s/\(X'[^,\)]*'\)/CONVERT(\1 using utf8mb4)/g

answered Jul 05 '19 at 02:25

Kyogo Mochida

141
1
3

score 9 · Answer 5 · answered Jun 12 '17 at 14:07

9

This worked for me, (I had control of the export to the sql file as well). There're lots of caveats; e.g. I knew that the fields would never be bigger than 1000 and wouldn't contain any non-ascii chars. Please do comment and tell me all the whys this is so bad tho :)

Before export

alter table <table> modify <json_column> varchar(1000);

Then after import

alter table <table> modify <json_column> json;

answered Jun 12 '17 at 14:07

Lnr

187
1
7

1

I would would recommend using LONGTEXT as temporary type. If size is smaller than the JSON data will be truncated and when converting back to JSON column, errors will be thrown for affected records plus your data is only partially saved. – icosmin Jan 24 '20 at 00:05
yup :) hence caveats – Lnr Feb 14 '20 at 13:23

score 6 · Answer 6 · answered Jun 04 '19 at 21:11

6

For those using Sequel Pro around June 2019, in addition to unchecking the "Output BLOB fields as hex option" (as mentioned above) - you also need to use the nightly build, which added support for JSON types 2 years ago. This support has not yet made it to the official release.

answered Jun 04 '19 at 21:11

Peter

203
3
7

1

Can you provide the link for the specific nightly build since I am facing the same inspite of unchecking the unchecking the "Output BLOB fields as hex option" – anuragb26 Sep 04 '19 at 07:51
Sequel Pro was abandoned and is now continued as Sequel Ace on the App Store. – AAGD Nov 10 '20 at 07:24

score 3 · Answer 7 · answered Jan 11 '19 at 20:44

3

I had this problem with a dump. i was able to fix it by changing the line in the dump file from:

/*!40101 SET NAMES binary*/;

to

/*!40101 SET NAMES utf8mb4*/;

answered Jan 11 '19 at 20:44

Andrew Burns

13,917
9
40
42

My dump had `/*!40101 SET NAMES utf8 */;`. Replacing that `utf8` with `utf8mb4` did not fix or change the issue. – FooF Jun 09 '20 at 13:27

score 1 · Answer 8 · answered Nov 09 '20 at 19:41

Lorcan's answer did help me well as a start, but converting all binary values created a bunch of other error messages like Duplicate entry [...] for key 'PRIMARY'. Finally I figured out that JSON entries all started with 5B or 7B, and closed with 5D or 7D, which of course means they start with [ or { and end with ] or }. So what worked for me was to regex-replace only those entries:

Find:    (X'5B[^,\)]*5D')
Replace: CONVERT($1 using utf8mb4)

then

Find:    (X'7B[^,\)]*7D')
Replace: CONVERT($1 using utf8mb4)

Et voilá, all import errors gone! (At least for my case)

score 0 · Answer 9 · answered Jul 26 '18 at 10:39

0

change collation to utf8_general_ci. worked for me.

answered Jul 26 '18 at 10:39

Swarup Bam

166
1
9

4

Instead of one-liner, add some relevant explanation to your answer. – Vikrant Jul 26 '18 at 10:56

score 0 · Answer 10 · answered Jan 21 '19 at 12:57

For the ones like me arived here using Symfony 4 / Doctrine : For some reasons the same entity can be resolved in a longtext MySQL type storing JSON; or a json MySQL type storing json. Manually setting longtext MySQL type resolved the problem in my particular case.

Emre · Answer 11 · 2019-06-11T17:27:15.323

0

This odd issue was occurring when running a simple UPDATE query:

update some_table set json_attr = '{"test":168}' where id = 123456;

Restarting MySQL fixed it. Was not able to pinpoint the cause.

Edit: We are using Aurora. It looks like it was related to us having a weird configuration where the same instance handled both master & slave/reader connections.

edited Jun 11 '19 at 17:27

answered Jun 10 '19 at 21:10

Emre

831
11
13

score -5 · Answer 12 · answered Jun 28 '16 at 14:12

-5

All MySQL JSON data type information must be UTF8MB4 character set not BINARY.

answered Jun 28 '16 at 14:12

David Stokes

120
3

3

Is there a possible way to force this with a current SQL dump to fix this thing? – Danny Bevers Jun 28 '16 at 14:40
How to convert it to UTF8MB4? I get error when I use `ALTER TABLE table_name CHANGE col_name col_name JSON CHARACTER SET utf8mb4;` – burakcalik Jun 01 '17 at 22:11
8

Not sure his is an answer, more like a statement. How does get import past this issue? – Ray Aug 29 '17 at 15:12
Where is an answare? – Mayur Jan 30 '19 at 13:24
1

This is not an answer. – doncadavona Feb 22 '19 at 06:12
@DannyBevers please change the accepted answer. This answer is definitely wrong. – kapad May 22 '19 at 08:02

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

12 Answers12

Linked