Bug #79066 LOAD_FILE will not load a file into a JSON column unless converted
Submitted: 2 Nov 2015 9:09 Modified: 2 Nov 2015 9:32
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7.9, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[2 Nov 2015 9:09] Giuseppe Maxia
Description:
insert into test.connection_json values ( load_file('/tmp/connection.json' ) )"
ERROR 3144 (22032) at line 1: Cannot create a JSON value from a string with CHARACTER SET 'binary'

However, using CONVERT, the load is accepted.

insert into test.connection_json values ( convert( load_file('/tmp/connection.json') using UTF8 ) );
Query OK, 1 row affected (0.00 sec)

How to repeat:
$ echo '{"a" : "1"}' > /tmp/test.json

mysql [localhost] {msandbox} (test) > create table t1 (t longtext);
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > create table t2 (t json);
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values (load_file('/tmp/test.json'));
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t2 values (load_file('/tmp/test.json'));
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql [localhost] {msandbox} (test) > insert into t2 values (convert(load_file('/tmp/test.json') using utf8));
Query OK, 1 row affected (0.00 sec)
[2 Nov 2015 9:12] Giuseppe Maxia
(Updated title)
[2 Nov 2015 9:32] Umesh Shastry
Hello Giuseppe,

Thank you for the report and test case.

Thanks,
Umesh
[2 Nov 2015 9:32] Umesh Shastry
// 5.7.9, 5.7.10

mysql> use test
Database changed
mysql> create table t1 (t longtext);
Query OK, 0 rows affected (0.01 sec)

mysql>  create table t2 (t json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (load_file('/tmp/test.json'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (load_file('/tmp/test.json'));
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql> insert into t2 values (convert(load_file('/tmp/test.json') using utf8));
Query OK, 1 row affected (0.00 sec)
[17 Nov 2015 8:08] Knut Anders Hatlen
Changing to feature request, since this is an intentional restriction. Binary strings are not accepted as JSON text because one cannot reliably tell how the string is encoded. Instead of guessing the encoding and possibly producing wrong results, we thought it was safer to raise an error so that the user could clear up the ambiguity by stating the encoding of the binary string explicitly (for example with CONVERT, as suggested in the bug description). If this turns out to be a major inconvenience, we could reconsider.
[29 Mar 2016 5:52] Loai Ghoraba
This error happens also with me when using mysqlimport to load rows that contains json columns, and there is no option to convert file in mysqlimport, so how to solve this? Specifying default character set to utf8 didn't solve the problem. Note that this doesn't happen when using LOAD DATA IN FILE directly, which is strange since mysqlimport is just an interface to LOAD DATA IN FILE!
[29 Mar 2016 5:52] Loai Ghoraba
This error happens also with me when using mysqlimport to load rows that contains json columns, and there is no option to convert file in mysqlimport, so how to solve this? Specifying default character set to utf8 didn't solve the problem. Note that this doesn't happen when using LOAD DATA IN FILE directly, which is strange since mysqlimport is just an interface to LOAD DATA IN FILE!
[29 Mar 2016 11:52] Loai Ghoraba
After checking the logs when running mysqlimport, I found that this query was generated:

set @@character_set_database=binary;

Then LOAD DATA IN FILE command was generated, which when copied and run independently, it works! So the problem is in this generated query. When setting --default-character-set=utf8 in mysqlimport, the error was still there.
[10 May 2016 16:04] Tegan Snyder
I feel like this is a big issue. Importing a database dump with JSON fields in it errors.
[7 Feb 2017 17:14] Adam Thomson
Getting same error as described by others.

mysqldump -uroot --tab=/var/lib/mysql/database-dump mydb
  (works OK)

mysqlimport -uroot mydb /var/lib/mysql/database-dump/*.txt
  (gives this error):
  mysqlimport: Error: 3144, Cannot create a JSON value from a string with CHARACTER SET 'binary'., when using table: mytable

mysql -u root -v -D mydb -e "LOAD DATA INFILE 'database-dump/mytable.txt' INTO TABLE mytable"
(works OK)