MySQL Bugs: #12982: LOAD DATA fails without any error for big files with big read buffer

Bug #12982	LOAD DATA fails without any error for big files with big read buffer
Submitted:	5 Sep 2005 8:20	Modified:	23 Jun 2006 8:25
Reporter:	martin koegler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Linux (Linux x86_64 2.6.11.4, Suse 9.3, 2.4.21, RHEL 3)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
If read_buffer_size is >= 2G and read_rnd_buffer_size>=2G, LOAD DATA INFILE loads for files bigger than 2 GB no rows. It also returns no error message.

Precompiled as well as self compiled mysql server are affected.

How to repeat:
Set in my.cnf for mysqld
read_buffer_size=2G
read_rnd_buffer_size=2G

Create a table, eg:
CREATE TABLE `x` (
  `x` char(2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Run
echo '"  "'>x
and repeat 
cat x x > y; mv y x
until x is bigger than 2GB (2684354560 bytes)

Issue SQL command:
LOAD DATA INFILE '/path_to/x' into table x FIELDS TERMINATED BY ';'  ;

The return messages is:
Query OK, 0 rows affected (3.38 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

Suggested fix:
The read system calls seems to return EINVAL, if the count parameter is >= (2<<31). So my_read should probably limit count to (2<<31)-1.

Additionally, in READ_INFO::read_field(), the error stored in this->cache while executing the macro GET, should be propagated to this->error.

Thank you for the bug report. I was able to repeat the described behaviour (although, with a server crash) on Red Hat also:

-bash-2.05b$ echo 'aa' > x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk       30 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk      300 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk     3000 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk    30000 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk   300000 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk  3000000 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk 30000000 Sep 24 15:44 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk 300000000 Sep 24 15:45 x
-bash-2.05b$ cat x x x x x x x x x x > y; mv y x; ls -l x
-rw-r--r--    1 vkravchuk vkravchuk 3000000000 Sep 24 15:48 x

So, that is the file >= 2G in size.

-bash-2.05b$ bin/mysqld_safe --skip-networking &
[1] 2065
-bash-2.05b$ Starting mysqld daemon with databases from /users/vkravchuk/dbs/4.1-nocona/var

-bash-2.05b$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE `x` (
    ->   `x` char(2) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> load data infile '/users/vkravchuk/dbs/4.1-nocona/x' into table x FIELDS
TERMINATED BY ';'  ;
Query OK, 1000000000 rows affected (14 min 44.56 sec)
Records: 1000000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> drop table x;
Query OK, 0 rows affected (19.19 sec)

mysql> exit
Bye

So, it was successfully loaded with default values.

-bash-2.05b$ bin/mysqladmin -uroot shutdown
STOPPING server from pid file /users/vkravchuk/dbs/4.1-nocona/var/nocona.mysql.c
om.pid
050924 16:11:01  mysqld ended

[1]+  Done                    bin/mysqld_safe --skip-networking
-bash-2.05b$ bin/mysqld_safe --skip-networking --read_buffer_size=2G --read_rnd_buffer_size=2G &
[1] 2142
-bash-2.05b$ Starting mysqld daemon with databases from /users/vkravchuk/dbs/4.1-nocona/var

-bash-2.05b$ bin/mysql -uroot ng --read_buff
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE `x` (   `x` char(2) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> load data infile '/users/vkravchuk/dbs/4.1-nocona/x' into table x FIELDS
TERMINATED BY ';'  ;
bin/mysqld_safe: line 1:  2164 Killed                  nohup /users/vkravchuk/db
s/4.1-nocona/libexec/mysqld --basedir=/users/vkravchuk/dbs/4.1-nocona --datadir=/users/vkravchuk/dbs/4.1-nocona/var --pid-file=/users/vkravchuk/dbs/4.1-nocona/var/nocona.mysql.com.pid --skip-locking --skip-networking --read_buffer_size\=2G
--read_rnd_buffer_size\=2G >>/users/vkravchuk/dbs/4.1-nocona/var/nocona.mysql.com.err 2>&1
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
050924 16:14:20  mysqld restarted

mysql> show variables like 'read%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| read_buffer_size     | 2147479552 |
| read_only            | OFF        |
| read_rnd_buffer_size | 2147479552 |
+----------------------+------------+
3 rows in set (0.27 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14    |
+-----------+
1 row in set (0.03 sec)

mysql> select count(*) from x;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

mysql> exit
Bye
-bash-2.05b$ uname -a
Linux nocona... 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:46:36 EDT 2005 x
86_64 x86_64 x86_64 GNU/Linux

-bash-2.05b$ cat /etc/issue
Red Hat Enterprise Linux AS release 3 (Taroon Update 2)
Kernel \r on an \m

Table x was create as MyISAM, if this does matter...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7358

The patch looks ok to push.

The patch looks ok to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7687

Pushed into trees currently marked as 5.0.23, 5.1.12.

Noted in 5.0.23, 5.1.12 changelogs.

Will it be fixed in MySQL 4.1?

This problem will not be fixed in 4.1.