MySQL Bugs: #79066: LOAD_FILE will not load a file into a JSON column unless converted

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

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)

(Updated title)

Hello Giuseppe,

Thank you for the report and test case.

Thanks,
Umesh

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

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.

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!

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!

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.

I feel like this is a big issue. Importing a database dump with JSON fields in it errors.

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)