Bug #88689 LOAD DATA is failed if JSON file include blank line at end of file
Submitted: 29 Nov 2017 6:57 Modified: 30 Nov 2021 9:53
Reporter: Yoshimi Takahashi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7, 5.7.36 OS:Linux
Assigned to: CPU Architecture:Any
Tags: json

[29 Nov 2017 6:57] Yoshimi Takahashi
Description:
I created dummy data of JSON type, and try to import it for JSON column.

If JSON file has "blank line" at the end, LOAD DATA is failed.

mysql> LOAD DATA INFILE '/tmp/sample_data.json' INTO TABLE bench(col1);
ERROR 3140 (22032): Invalid JSON text: "The document is empty." at position 0 in value for column 'bench.col1'.

But when I delete its blank line, LOAD DATA could be executed.

mysql> LOAD DATA INFILE '/tmp/sample_data_2.json' INTO TABLE bench(col1);
Query OK, 10 rows affected (0.00 sec)

The difference between two files is only "blank line".

# diff /tmp/sample_data.json /tmp/sample_data_2.json 
11d10
< 

[environment]

# rpm -qa | grep -i mysql
mysql-community-libs-5.7.20-1.el7.x86_64
mysql-community-server-5.7.20-1.el7.x86_64
mysql57-community-release-el7-11.noarch
mysql-community-client-5.7.20-1.el7.x86_64
mysql-community-libs-compat-5.7.20-1.el7.x86_64
perl-DBD-MySQL-4.023-5.el7.x86_64
mysql-community-common-5.7.20-1.el7.x86_64

How to repeat:
1) Install latest MySQL5.7

2) change my.cnf (add secure_file_priv='/tmp') and put sample files under /tmp

3) start MySQL5.7

4) create database and table

mysql> CREATE DATABASE d1;
mysql> CREATE TABLE `d1`.`bench` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

5) LOAD DATA each sample files

mysql> LOAD DATA INFILE '/tmp/sample_data.json' INTO TABLE bench(col1);
mysql> LOAD DATA INFILE '/tmp/sample_data_2.json' INTO TABLE bench(col1);

Suggested fix:
MySQL should ignore "blank space" in JSON file
[29 Nov 2017 6:57] Yoshimi Takahashi
LOAD DATA is failed

Attachment: sample_data.json (application/octet-stream, text), 1.44 KiB.

[29 Nov 2017 6:58] Yoshimi Takahashi
LOAD DATA is succeed

Attachment: sample_data_2.json (application/octet-stream, text), 1.44 KiB.

[30 Nov 2021 9:53] MySQL Verification Team
Hell Takahashi-San,

Thank you for the report.
Verified as described with 5.7.36 build.

regards,
Umesh