MySQL Bugs: #88689: LOAD DATA is failed if JSON file include blank line at end of file

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

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

LOAD DATA is failed

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

LOAD DATA is succeed

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

Hell Takahashi-San,

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

regards,
Umesh