MySQL Bugs: #80538: LOAD DATA INFILE Error 13 when file size is 4.1GB

Bug #80538	LOAD DATA INFILE Error 13 when file size is 4.1GB
Submitted:	26 Feb 2016 23:21	Modified:	21 Jul 2018 14:31
Reporter:	J Scavok	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.11	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
I was attempting to use LOAD DATA INFILE (without LOCAL) to populate three separate InnoDB tables. All three tab-separated-values files were located in the same directory (/var/lib/mysql-files). Two were successful, while the third failed:

1) OKAY: 5,638,286 rows/  262,764,135 bytes
2) OKAY: 3,739,764 rows/  102,659,743 bytes
3) FAIL: 5,638,286 rows/4,369,760,837 bytes; Error 13 (HY000): Can't get stat of '/var/lib/mysql-files/patent.tsv' (Errcode: 13 - Permisssion denied)

Yet when I manually invoked `stat' it returned this:

[root@localhost mysql]# stat '/var/lib/mysql-files/patent.tsv'
  File: `/var/lib/mysql-files/patent.tsv'
  Size: 4369760837	Blocks: 8534704    IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 1577079     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2015-10-13 07:23:17.000000000 -0700
Modify: 2015-10-13 07:27:36.000000000 -0700
Change: 2016-02-25 15:56:00.360040034 -0800

I'm wondering if it has to do with the large file size of 4.1 GB (4,369,760,837 bytes)? The other two (relatively) large files (5,638,286 rows/262,764,135 bytes and 3,739,764 rows/102,659,743 bytes) were successfully processed by LOAD DATA INFILE.

N.B.: I did not use LOCAL with the LOAD DATA INFILE statements.

Specifics:

OS: CentOS release 6.6 (Final)

MySQL:

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | i686                         |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

Database name: patentsview

Create tables and LOAD DATA INFILE statements:

1) The `application' table (no issues with LOAD DATA INFILE)

CREATE TABLE `application` (
  `id` varchar(36) DEFAULT NULL,
  `patent_id` varchar(20) DEFAULT NULL,
  `series_code` varchar(20) DEFAULT NULL,
  `number` varchar(64) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> load data infile '/var/lib/mysql-files/application.tsv' into table application character set 'latin1' fields terminated by '\t' lines terminated by '\r\n' ignore 1 lines;
Query OK, 5638286 rows affected (2 min 35.23 sec)
Records: 5638286  Deleted: 0  Skipped: 0  Warnings: 0

The application.tsv file was retrieved from http://www.patentsview.org/data/2015/application.zip

[root@localhost mysql-files]# ls -ld application.tsv
-rw-r--r--. 1 root root 262764135 Oct 13 06:55 application.tsv

2) The `inventor' table (no issues with LOAD DATA INFILE)

mysql> create table inventor
    -> (
    -> id varchar(36),
    -> name_first varchar(64),
    -> name_last varchar(64)
    -> ) engine=InnoDB character set=latin1;
Query OK, 0 rows affected (0.29 sec)

mysql> load data infile '/var/lib/mysql-files/inventor.tsv' into table inventor character set 'latin1' fields terminated by '\t' lines terminated by '\r\n' ignore 1 lines;
Query OK, 3739764 rows affected (1 min 25.55 sec)
Records: 3739764  Deleted: 0  Skipped: 0  Warnings: 0

The inventor.tsv was retrieved from http://www.patentsview.org/data/2015/inventor.zip

[root@localhost mysql-files]# ls -ld inventor.tsv
-rw-r--r--. 1 root root 102659743 Oct 26 07:35 inventor.tsv

3) The table that fails: patent

mysql> create table patent
    -> (
    -> id varchar(20),
    -> type varchar(20),
    -> number varchar(64),
    -> country varchar(20),
    -> date date,
    -> abstract text,
    -> title text,
    -> kind varchar(10),
    -> num_claims int,
    -> filename varchar(120)
    -> ) engine=InnoDB character set=latin1;
Query OK, 0 rows affected (0.30 sec)

mysql> load data infile '/var/lib/mysql-files/patent.tsv' into table patent character set 'latin1' fields terminated by '\t' lines terminated by '\r\n' ignore 1 lines;
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/patent.tsv' (Errcode: 13 - Permission denied)

The patent.tsv was retrieved from http://www.patentsview.org/data/2015/patent.zip

[root@localhost mysql-files]# ls -ld patent.tsv
-rw-r--r--. 1 root root 4369760837 Oct 13 07:27 patent.tsv

[root@localhost mysql-files]# stat '/var/lib/mysql-files/patent.tsv'
  File: `/var/lib/mysql-files/patent.tsv'
  Size: 4369760837	Blocks: 8534704    IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 1577079     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2015-10-13 07:23:17.000000000 -0700
Modify: 2015-10-13 07:27:36.000000000 -0700
Change: 2016-02-25 16:08:29.266785855 -0800

For comparison (again, there were no issues with these two files):

[root@localhost mysql-files]# stat '/var/lib/mysql-files/application.tsv'
  File: `/var/lib/mysql-files/application.tsv'
  Size: 262764135 	Blocks: 513216     IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 1576960     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2016-02-25 15:14:05.046709896 -0800
Modify: 2015-10-13 06:55:06.000000000 -0700
Change: 2016-02-25 16:08:29.266785855 -0800

[root@localhost mysql-files]# stat '/var/lib/mysql-files/inventor.tsv'
  File: `/var/lib/mysql-files/inventor.tsv'
  Size: 102659743 	Blocks: 200512     IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 1576962     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2016-02-25 15:45:16.238898321 -0800
Modify: 2015-10-26 07:35:08.000000000 -0700
Change: 2016-02-25 16:08:29.266785855 -0800

How to repeat:
Follow steps in Description.

Suggested fix:
Change to MyISAM? 

Split patent.tsv into two separate files and issue two separate LOAD DATA INFILE?

Upgrade to 64-bit OS?

I changed the table `patent' to use MyISAM; no joy:

mysql> alter table patent engine=MyISAM;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> load data infile '/var/lib/mysql-files/patent.tsv' into table patent character set 'latin1' fields terminated by '\t' lines terminated by '\r\n' ignore 1 lines;
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/patent.tsv' (Errcode: 13 - Permission denied)

Given that I am using a 32-bit version of MySQL, I suspect that when it calls `stat' on a file with a size greater than 2,147,483,647 bytes (for signed integer) or 4,294,967,295 (for unsigned integer), the MySQL code is under- or over-flowing the value for the size of the file to the value of -1 and interpreting this as an error.

In other words: a 32-bit OS may have a file size greater than what can be expressed by the 32-bit integer (presumably) being used by the MySQL code.

Hi,

This does not look like a file size problem at all.

It looks like a simple permission problem. Since server is, most probably, running under the UID/GID of "mysql", you have to check the permissions on the file and all of the directories above it.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".