Bug #64883 ROW_COUNT() returns -1 for LOAD DATA INFILE
Submitted: 5 Apr 2012 22:35 Modified: 6 Apr 2012 4:07
Reporter: David Berg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.61, 5.5.22 OS:Any (Windows 7 (64 bit), Mac OS X)
Assigned to: CPU Architecture:Any
Tags: LOAD DATA INFILE, row_count()
Triage: Needs Triage: D3 (Medium)

[5 Apr 2012 22:35] David Berg
Description:
The 5.5 users manual states that ...

As of MySQL 5.5.5, ROW_COUNT() returns a value as follows: 

■ DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATE, INSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE. 

However, ROW_COUNT() returns -1 following LOAD DATA INFILE.

How to repeat:
Create a file containing some number of lines > 0 ...

Row  1
Row  2
Row  3
Row  4
Row  5
Row  6
Row  7
Row  8
Row  9
Row 10

Load this file into a table.
Note the action message.
Fetch ROW_COUNT().
Fetch table contents.

   CREATE TABLE Tab1 (Col1 CHAR(6));

   LOAD DATA LOCAL INFILE 'c:\\_data\\TestFile.txt' INTO TABLE Tab1 
        LINES TERMINATED BY '\r\n';

10 row(s) affected Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

   SELECT ROW_COUNT();

-1

   SELECT * FROM Tab1;

Row  1
Row  2
Row  3
Row  4
Row  5
Row  6
Row  7
Row  8
Row  9
Row 10

Suggested fix:
Enable ROW_COUNT() for LOAD DATA INFILE, as documented.
[6 Apr 2012 4:07] Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.61 on Mac OS X also:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tab1(c1 int);
Query OK, 0 rows affected (0.45 sec)

mysql> load data local infile '/tmp/test.txt' into table tab1;
Query OK, 1 row affected (0.51 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.09 sec)

mysql> select * from tab1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.09 sec)
[6 Apr 2012 5:51] Davi Arnaut
It might be worth replacing all references to "LOAD DATA INFILE" with "LOAD DATA LOCAL INFILE".