Bug #62551 Getting error loading a file with "LOAD DATA INFILE" containing nulls (\N)
Submitted: 27 Sep 2011 14:31 Modified: 29 Apr 2013 12:14
Reporter: Jessica Perez Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.1.17 OS:Microsoft Windows
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: Connector/J, java, LOAD DATA INFILE, MySQL

[27 Sep 2011 14:31] Jessica Perez
Description:
I'm trying to make a simple query from my java program using mysql connector/j,
the query is "load data infile...";

Here my code:
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUser(dbUser);
    dataSource.setPassword(dbPass);
    dataSource.setDatabaseName(dbName);
    dataSource.setServerName(serverName);
    dataSource.setPort(3307);
    //dataSource.setJdbcCompliantTruncation(false);

    Connection c = dataSource.getConnection();

    sql = "LOAD DATA INFILE 'C:/Users/Jess/DB/PRUEBA.txt'"+ 
          "REPLACE INTO TABLE DB.prueba";
    PreparedStatement p = c.prepareStatement(sql);
    int x = p.executeUpdate();

    p.close();
    c.close();

Now, on the file PRUEBA.txt there is some field setted in null, 
Here the file:

1	4
2	\N
3	\N
4	6

First field is a id(int,pk....) the second one is int, default NULL.

When the executeUpdate() is called, a error is displayed:

java.sql.SQLException: Incorrect integer value: 'N
' for column 'charaaa' at row 2
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at com.becoblohm.cr.PruebaConexion.main(PruebaConexion.java:105)

I made some tests:
    First I tried to execute the query directly from mysql (using mysql client from a terminal/console), and got the same error. Looking on web, I found that I should comment the line sql-mode="STRICT_TRANS_TABLES.." on my.ini, that I shouldn't use Strict-mode, and this worked only on mysql client. (By worked, I should mention sometimes insert '0' instead NULL).

When using connector/j the problem persists. I read that connector/j forces strict mode, so I should set dataSource.setJdbcCompliantTruncation(false); . When I set JdbcCompliantTruncation=false, the error is not displayed but no row is inserted. 

How to repeat:
Windows 7.
mysql 5.5
mysql-connector-java-5.1.17.jar
java version "1.6.0_12"

This is the table:
CREATE TABLE `prueba` (
  `idprueba` int(11) NOT NULL,
  `charaaa` int(11) DEFAULT NULL,
  PRIMARY KEY (`idprueba`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

This is the query string:
sql = "LOAD DATA INFILE 'C:/Users/Jess/DB/PRUEBA.txt' REPLACE INTO TABLE DB.prueba";

This is the file: 

1	4
2	\N
3	\N
4	6

This is the error:

java.sql.SQLException: Incorrect integer value: 'N
' for column 'charaaa' at row 2
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
	at com.becoblohm.cr.PruebaConexion.main(PruebaConexion.java:105)
[28 Sep 2011 7:06] Valeriy Kravchuk
Assuming that you had created your .txt file on Windows, you have to change your LOAD statement a bit:

mysql> load data infile 'c:/tmp/prueba.txt' replace into table prueba lines term
inated by '\r\n';
Query OK, 4 rows affected (0.09 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from prueba;
+----------+---------+
| idprueba | charaaa |
+----------+---------+
|        1 |       4 |
|        2 |    NULL |
|        3 |    NULL |
|        4 |       6 |
+----------+---------+
4 rows in set (0.00 sec)

to tell server how lines are actually terminated. Read the manual, http://dev.mysql.com/doc/refman/5.5/en/load-data.html, about this.
[28 Oct 2011 23:00] Bugs System
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".
[29 Apr 2013 12:14] Alexander Soklakov
Hi Jessica,

I guess your problem was resolved, there is no feedback since Sep 2011, so I close this report as "Not a bug".

Please, feel free to reopen it if the problem still exists.