MySQL Bugs: #70931: \N is not an integer value while using LOAD DATA INFILE

Bug #70931	\N is not an integer value while using LOAD DATA INFILE
Submitted:	16 Nov 2013 21:57	Modified:	18 Nov 2013 20:23
Reporter:	Veli Atci	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	Community server 5.6.14	OS:	Windows (vista ultimate)
Assigned to:		CPU Architecture:	Any
Tags:	\N problem, LOAD DATA INFILE

Description:
Hi,
While using LOAD DATA FILE statement I noticed if a line in the file contains \N as last field and corresponding field type is integer then mysql raises an error as N is not an integer value.That problem was not existing mysql 5.1,I noticed that after upgrading to mysql version 5.6

How to repeat:
see explanation above

Suggested fix:
none

I cannot reproduce with 5.6.14 on Windows (7/64)

1) I have the table

/*Table structure for table `blu` */

DROP TABLE IF EXISTS `blu`;

CREATE TABLE `blu` (
  `id` int(11) NOT NULL,
  `txt` varchar(20) DEFAULT NULL,
  `ts` timestamp(6) NULL DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `blu` */

insert  into `blu`(`id`,`txt`,`ts`,`number`) values (1,'a',NULL,7),(2,'b',NULL,NULL);

2) and the file

"1";"a";\N;"7"
"2";"b";\N;\N

3) I execute the statement

LOAD DATA LOCAL INFILE 'C:\\Users\\Peter\\Desktop\\null.csv' INTO TABLE `bla`.`blu` FIELDS ESCAPED BY '\\' TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (`id`, `txt`, `ts`, `number`); 
--  no errors or warnings occur

4 Data are imported: 

SELECT * FROM blu
/* returns 

    id  txt         ts  number  
------  ------  ------  --------
     1  a       (NULL)         7
     2  b       (NULL)    (NULL)
*/

.. so I think you should detail your report here (a reproducible test case conisting of the CREATE TABLE statement for the table you are importing to, the data file, how exactly the LOAD DATA statement was framed).

Peter
(not a MySQL/Oracle person)

Hi,
I use following create table and load data infile statements and got error as displayed on item-3:(please attached infile_main.txt file,it raises an error at row 10 since it contains \N as last field and corresponding field type is integer.Thanks.

item-1:
CREATE TABLE ca_iups_small.cmain_backup (Frame integer PRIMARY KEY ,tmsi integer , new_tmsi integer , mshome integer , msin integer , imsi varchar(20) , lac integer , sac integer , rac integer , event varchar(20) ,
opc integer ,imsi_updated integer , teid integer , start integer , end integer , duration integer , time_val varchar(20) ,
direction integer , Date_Hour varchar(20),dpc integer,rncid integer,destlocalreference integer);

item-2:
LOAD DATA INFILE 'D:\\Old_E\\my_c\\USERS\\CPP_projects\\umts_lte\\v1.31\\infile_main.txt'
INTO TABLE ca_iups_small.cmain_backup
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

item-3:
18:02:20	LOAD DATA INFILE 'D:\\Old_E\\my_c\\USERS\\CPP_projects\\umts_lte\\v1.31\\infile_main.txt' INTO TABLE ca_iups_small.cmain_backup FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'	
Error Code: 1366. Incorrect integer value: 'N ' for column 'destlocalreference' at row 10	0.078 sec

There is no file attached yet as far as I can see! :-) 

Maybe you will to specify the OPTIONALLY keyword ("OPTIONALLY ENCLOSED BY ..")
("OPTIONALLY" means that strings a "quoted" in the file but numbers are not).

.. but we need to see a file where this problem occurs. Only with import data it can be decided if your escape settings in the statement is correct.

source file which is loaded into table

Attachment: infile_main.txt (text/plain), 6.36 KiB.

sorry I have chosen the file but not added.Please use attached file.As a workaround solution I added one more dummy field and exported 1 to that dummy field and I have no problem right now.That also shows there must be something wrong with mysql as I didn't change anything except just adding that dummy field at the end with 1s.

Thanks for your support.

Why do you specify "ENCLOSED BY .." when neither string nor numbers are enclosed in the file?

You may have hit some edge case where MySQL 5.1 and 5.6 differ, but simply omit "ENCLOSED BY .." should do the trick as far as I can see

Thanks,for the time being I will continue with my workaround as I changed some coding to align with that workaround.

By the way,not related with that topic but I found changing "innodb_flush_log_at_trx_commit" parameter from default value 1 to 0 speeds up my cdr processing time more than 50%.Would it be possible to change that setting default value 0 in next mysql releases?

Best regards,

Making InnoDB loose up to 1 second worth of already committed transactions by default doesn't sound like a good default setting, or does it to you?

The real problem seems to be 

  LINES TERMINATED BY '\r\n' 

vs.

  LINES TERMINATED BY '\n'

here.

The example file infile_main.txt has DOS/Windows style line endings using CR+LF,
so '\r\n' is the right line terminator to specify in LOAD DATA

When converting the file to have Unix style line endings (just LF) and changing the line terminator in the LOAD statement to '\n' the import works fine, too

When giving '\n' as LINES TERMINATED BY but actually using \r\n in the input file I'm getting the same errors as reported, and when giving '\r\n' as expected terminator but actually importing a file with unix line endings I get just a single row imported and a "Data truncated in line 1" warning.

So not a bug but simply the format specification in the LOAD DATA statement not matching the actual file format

Dear Mr.Holzgraefe,
In my case, setting innodb_flush_log_at_trx_commit to 0 improved my cdr processing time considerably(more than 50%),I guess it was a better trade-off flushing log file once per second rather than after each transaction commit in my case.I gave that feedback just to share information which might have been useful.Just for my better understanding, is there any harm to set that parameter 0?
Regards.

Thank you for the report and comments.

As Hartmut reasonably noted, issue occurred, because you specified wrong line termination symbol in case 3. This is not MySQL bug.

Regarding to innodb_flush_log_at_trx_commit this is different issue and if you want to change default value for it, you should open separate feature request. But I must alert you: most likely it will be closed as "Not a bug" too, because different users have different needs and we choose defaults to: 1) cover most of them; 2) to keep any installation safe which can not be the case with this variable set to 0. If this is OK for you, simply change it in your configuration file.

Dear Mrs.Smirnova,
Thanks for your suggestions.\r\n also solves problem.Earlier as a workaround I exported one more field to my table and filled them with 1s,that was also working with line terminator \n.I guess for this specific case if the last field  is filled with \N and data type integer mysql LOAD DATA INFILE was having problem.Anyway issue can be closed.

Regarding "innodb_flush_log_at_trx_commit" issue,is there a way to set this parameter for a specific database and keep the default unchanged?
Thanks.