mysqlimport issues "set @@character_set_database=binary" which prevents loading json values

Question

I have been using mysqlimport without problems for a long time, now as mysql 5.7 added json data type support, I'm trying to use mysqlimport with rows containing json data.

Here is an example of a row in csv file that will be imported using mysqlimport:

column_A_value,column_B_value,[{"x":20,"y":"some name"}]

Notice that the last column type is json. Now when using mysqlimport as the following:

mysqlimport -u user -ppass -h localhost --columns='col_A,col_B,col_C' --local --fields-terminated-by=',' some_db /path/to/products.txt

I got the following error: Cannot create a JSON value from a string with CHARACTER SET 'binary'., when using table: products

However using the generated LOAD DATA IN FILE instead of mysqlimport worked without problems! I opened mysql log and checked the generated LOAD DATA IN FILE command when running mysqlimport, then copied and pasted it, and it worked without problems! I ran something like:

LOAD DATA LOCAL INFILE '/path/to/products.txt'
INTO TABLE products

And it worked! The only difference is that in the log, when running mysqlimport this line was generated also

Query   /*!40101 set @@character_set_database=binary */

Then LOAD DATA IN FILE command was generated, so that line is the source of the problem.

So anyway I tried setting character set to utf8,

mysqlimport -u user -ppass -h localhost --columns='col_A,col_B,col_C' --local --fields-terminated-by=',' --default-character-set=utf8 some_db /path/to/products.txt

but in vain, same error happened.

So any clue how to solve this character set issue please ?

Rora

Answer 1

Check your character set configuration:

show variables like ‘%char%';

Not responding to --default-character-set might still be a bug of mysqlimport: https://bugs.mysql.com/bug.php?id=29712

Possible Solutions:

1. Try SET NAMES utf8; before the import
2. Try using mysql with --default-character-set=utf8 instead of mysqlimport
3. Alter your my.cnf:

[mysqld]
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
default-character-set=utf8

[client]
default-character-set=utf8

Mady

None of the above suggestions worked for me. Please note on MYSQL versions 5 or greater you will need to use different values to set the default charset as they deprecated that. Examples here: outofcontrol.ca/blog/comments/… – Castellatus

Using mysqlshould be working. As for my.cnf it seems that init-connect and default-character-set no longer belong in the [mysqld] section. – Mady

Answer 2

Backup mysqlimport to another directory

Change charset using brutal force!!!

sed -i 's/character_set_database=binary\ \*/character_set_database=utf8mb4\*/' mysqlimport

Larrylars