Bug #75546 load data local infile mishandles \N when it is last field on line
Submitted: 18 Jan 2015 20:53 Modified: 19 Jan 2015 23:36
Reporter: J Scavok Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.16-log OS:Microsoft Windows (Microsoft Windows XP Professional Service Pack 3 build 2600)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[18 Jan 2015 20:53] J Scavok 
Description:
load data local infile mishandles \N when it is last field on line.

E.g. given this schema:
mysql> describe metadata;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| idmetadata | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| when       | datetime         | NO   |     | NULL    |                |
| artist     | varchar(255)     | NO   |     | NULL    |                |
| album      | varchar(255)     | YES  |     | NULL    |                |
| year       | year(4)          | YES  |     | NULL    |                |
| song       | varchar(255)     | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Here is the command I used to load the table:
mysql> load data local infile 'c:/kxnv/kxnv.tsv' into table kxnv.metadata;

The file kxnv.tsv is tab-separated.

Now then when a line of this file ends with \N, e.g.,

\N	2015-01-18 08:59:16	william van dyke	BVD-bktpro1	\N	\N

doing a select results in the last \N becoming N:

mysql> select * from metadata where album="BVD-bktpro1"\G
*************************** 1. row **********************
idmetadata: 295
      when: 2015-01-18 08:59:16
    artist: william van dyke
     album: BVD-bktpro1
      year: NULL
      song: N
1 row in set (0.00 sec)

When the last \N is changed to NULL the result is correct:

mysql> select * from metadata where album="BVD-bktpro1"\G
*************************** 1. row ***********************
idmetadata: 422
      when: 2015-01-18 08:59:16
    artist: william van dyke
     album: BVD-bktpro1
      year: NULL
      song: NULL
1 row in set (0.00 sec)

How to repeat:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema kxnv
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `kxnv` ;
CREATE SCHEMA IF NOT EXISTS `kxnv` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `kxnv` ;

-- -----------------------------------------------------
-- Table `kxnv`.`metadata`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `kxnv`.`metadata` ;

CREATE TABLE IF NOT EXISTS `kxnv`.`metadata` (
  `idmetadata` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `when` DATETIME NOT NULL,
  `artist` VARCHAR(255) NOT NULL,
  `album` VARCHAR(255) NULL,
  `year` YEAR NULL,
  `song` VARCHAR(255) NULL,
  PRIMARY KEY (`idmetadata`),
  UNIQUE INDEX `idmetadata_UNIQUE` (`idmetadata` ASC))
ENGINE = InnoDB
COMMENT = '		';

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Here is the tab-separated values file (with \N throughout):

\N	2015-01-18 06:59:53	Larry Hosford	KFAT #13	1974	The Month Of May
\N	2015-01-18 07:00:37	Hot Tuna	Hot Tuna	\N	Uncle Sam Blues
\N	2015-01-18 07:05:49	Ray Charles & James Taylor	Genius Loves Company	2004	Sweet Potato Pie
\N	2015-01-18 07:10:06	william van dyke	william van dyke's Album	2015	BVD-blurb6
\N	2015-01-18 07:13:06	Angela Strehli	Blonde & Blue; Rounder CD 31217	1993	Never Lke (sic) This Before
\N	2015-01-18 07:16:06	Todd Snider	Happy To Be Here	2000	Ballad of the Devil's Backbone Tavern
\N	2015-01-18 07:19:16	Bonnie Raitt	Nick Of Time; Capitol CDP 7 91268 2	1989	Nobody's Girl
\N	2015-01-18 07:23:12	Grateful Dead	The Grateful Dead	1967	Morning Dew
\N	2015-01-18 07:27:36	Terri Hendrix	Left Overalls	\N	Waiting on Trains
\N	2015-01-18 07:31:16	Bobby Freeman	The Great Rock'N'Roll Collection (Disc 3)	1999	Do You Wanna Dance
\N	2015-01-18 07:33:56	Real Science News	REALSCI-promo end 17jan15	\N	\N
\N	2015-01-18 07:34:36	Joni Mitchell	Court And Spark	1974	Down To You
\N	2015-01-18 07:40:07	Hayes Carll	Trouble In Mind	2008	I Don't Wanna Grow Up
\N	2015-01-18 07:44:40	The Subdudes	Behind The Levee	2006	Let's Play
\N	2015-01-18 07:47:17	John Prine	The Missing Years	1991	Everybody Wants To Feel Like You
\N	2015-01-18 07:50:27	Madeleine Peyroux	Bare Bones	2009	Instead
\N	2015-01-18 07:55:36	Zachary Richard	AAA	\N	Roll Me
\N	2015-01-18 07:59:16	BVD	IDEE-bvd2	\N	\N
\N	2015-01-18 07:59:36	The Blasters	4-11-44	2005	Dry River
\N	2015-01-18 08:03:07	Bob Dylan	The Freewheelin' Bob Dylan	1963	Corrina, Corrina
\N	2015-01-18 08:05:57	Various Artists	The Many Faces Of Boogie Woogie [Disc 2]	2000	In Cow Cow's Footsteps
\N	2015-01-18 08:10:20	Shawn Colvin	Cover Girl	1994	Window To The World
\N	2015-01-18 08:13:20	Todd Snider	New Connection	2002	Anywhere
\N	2015-01-18 08:19:07	Todd Snider	New Connection	2002	Anywhere
\N	2015-01-18 08:19:08	Ray Charles & Gladys Knight	Genius Loves Company	2004	Heaven Help Us All
\N	2015-01-18 08:23:37	Doc Watson & David Grisman	Doc & Dawg	1997	Doc & Dawg
\N	2015-01-18 08:25:36	The Temptations	The Ultimate Collection	1997	I Can't Get Next to You
\N	2015-01-18 08:28:37	Jackie Edwards	\N	\N	\N
\N	2015-01-18 08:31:57	Free Beer	KFAT #4	1975	Coupe de Ville
\N	2015-01-18 08:36:16	Sonny Landreth	Outward Bound	1992	Back To Bayou Teche
\N	2015-01-18 08:40:07	Traffic	Smiling Phases [Disc 2]	1974	When The Eagle Flies
\N	2015-01-18 08:44:26	BVD	william van dyke's Album	2014	SPOT-vsam1
\N	2015-01-18 08:44:46	Girls Of The Golden West	Cattle Call-Early Cowboy Music And It's Roots	1934	My Love Is A Rider (Bucking Bronco)
\N	2015-01-18 08:47:26	Rosanne Cash	Retrospective	1990	What We Really Want
\N	2015-01-18 08:50:48	Little Richard	The Great Rock'N'Roll Collection (Disc 1)	1958	Good Golly Miss Molly - Little Richard
\N	2015-01-18 08:52:56	Cornbread	Silvertown	2015	SPOT-gates1
\N	2015-01-18 08:53:36	James Brown	20 All-Time Greatest Hits!	\N	I Got the Feelin'
\N	2015-01-18 08:56:07	J.J. Cale	Naturally	1972	Crazy Mama
\N	2015-01-18 08:58:37	IDEE-TW2	\N	\N	\N
\N	2015-01-18 08:58:56	Steve Funk	KXNV's Album	2014	SPOT-ztc1
\N	2015-01-18 08:59:16	william van dyke	BVD-bktpro1	\N	\N
\N	2015-01-18 08:59:56	Tom Waits	Blood Money	2002	Coney Island Baby
\N	2015-01-18 09:00:17	Real Science News	REALSCI-promo end 17jan15	\N	\N
\N	2015-01-18 09:00:56	KXNV	KXNV's Album	2015	Real Science News 2 1:18:15
\N	2015-01-18 10:00:56	Don Darue	IDEE-dd2	\N	\N
\N	2015-01-18 10:01:07	Real Science News	REALSCI-promo end 17jan15	\N	\N
\N	2015-01-18 10:01:46	Someone to Watch over Me	The Ultimate Diva Collection	\N	07 - Blossim Dearie - Someone to Watch over Me Someone to Watch over Me BD0DD910
\N	2015-01-18 10:07:37	Ray Wylie Hubbard	KFAT #18	1978	Redneck Mother
\N	2015-01-18 10:11:47	Jerry Lee Lewis	KFAT #14	1980	I Only Want a Buddy, Not a Sweetheart
\N	2015-01-18 10:15:27	Tom Petty & The Heartbreakers	Pack Up The Plantation: Live!	1985	Refugee
\N	2015-01-18 10:20:47	The Dusty Chaps	KFAT #1	1977	Don't Haul Bricks on '66
\N	2015-01-18 10:24:07	Los Lobos	Good Morning Aztlan	2002	Mammoth 2061-65518-2 - Good Morning Aztlan
\N	2015-01-18 10:28:06	Buddy Holly	The Buddy Holly Collection	\N	Maybe Baby
\N	2015-01-18 10:30:17	Martin Pahinui, George Kuo, Danny Akaka, Steven Hall	Waialea Jammin' (Disc 1)	2001	Nani Kaua'i
\N	2015-01-18 10:34:36	Chris Webster	Something In The Water	2006	Get Rhythm
\N	2015-01-18 10:40:36	Rosanne Cash	Rules Of Travel	2003	Will You Remember Me
\N	2015-01-18 10:43:16	Boz Scaggs	Some Change	1994	Some Change
\N	2015-01-18 10:49:27	Clarence 'Gatemouth' Brown	No Looking Back; Alligator ALCD 4804	1992	Dope
\N	2015-01-18 10:52:46	Steve Funk	KXNV's Album	2014	PCPA-end24jan15
\N	2015-01-18 10:53:17	Steve Funk	SHOPRO-ndow1	\N	\N
\N	2015-01-18 10:53:46	Martin Mull	KFAT 25	1973	Licks Offa Records
\N	2015-01-18 10:56:57	BVD	IDEE-bvd4	\N	\N
\N	2015-01-18 10:57:06	Real Science News	REALSCI-promo end 17jan15	\N	\N
\N	2015-01-18 11:00:46	Austin Lounge Lizards	Strange Noises In The Dark	2003	We Always Fight When We Drink Gin
\N	2015-01-18 11:03:26	Al Green	The Supreme Al Green: The Greatest Hits	\N	Full of Fire
\N	2015-01-18 11:08:36	B. B. King	Till The Night Be Gone: A Tribute To Doc Pomus	1995	Blinded By Love
\N	2015-01-18 11:13:07	Ten Years After	Cricklewood Green	1970	Working On The Road
\N	2015-01-18 11:17:27	Amos Milburn	The Best Of Amos Milburn	2001	Bad Bad Whiskey
\N	2015-01-18 11:20:16	Leonard Dillon	\N	\N	\N
\N	2015-01-18 11:23:37	Commander Cody & His Lost Planet Airmen	KFAT 22	1975	Roll Your Own
\N	2015-01-18 11:26:57	Sam & Dave	The Best Of Sam & Dave	1969	You Got Me Hummin'
\N	2015-01-18 11:29:46	Hoyt Axton	The A&M Years [Disc 2]	1998	Southbound
\N	2015-01-18 11:32:17	California Zephyr	KFAT #6	1976	Smokin' My Last Cigarette
\N	2015-01-18 11:36:27	The Kinks	The Singles Collection	1968	Days
\N	2015-01-18 11:39:18	The Subdudes	Annunciation	1994	Angel To Be
\N	2015-01-18 11:43:07	Elvis Presley	The Essential Elvis Presley	1960	It's Now Or Never
\N	2015-01-18 11:46:17	The Black Lillies	100 Miles of Wreckage	2011	Ain't My Fault
\N	2015-01-18 11:50:06	Weather Report	Heavy Weather	1977	Palladium
\N	2015-01-18 11:54:57	BR5-49	BR5-49	2000	Lifetime To Prove
\N	2015-01-18 11:57:56	Cornbread	IDEE-cbc3	\N	\N
\N	2015-01-18 11:58:06	Elvis Presley	The Essential Elvis Presley	1962	Return To Sender
\N	2015-01-18 12:00:17	Trout Fishing In America	Closer To The Truth	1999	Old Things
\N	2015-01-18 12:03:46	Bonnie Raitt	Luck Of The Draw	1991	Not The Only One
\N	2015-01-18 12:08:46	Taj Mahal	The Hot Spot	1990	Empty Bank (w/Miles Davis)
\N	2015-01-18 12:11:06	Hoyt Axton	The A&M Years [Disc 2]	1998	Speed Trap (Out Of State Cars)
\N	2015-01-18 12:13:36	Lloyd Price	Creole Kings of New Orleans	\N	Lawdy Miss Clawdy [Alternate Take 1]
\N	2015-01-18 12:16:07	Moon Mullican	Moonshine Jamboree	1993	Nine Tenths Of The Tennessee River

Suggested fix:
Use NULL instead of \N when this appears at the end of a line within a tab-separated values file used by load data local infile.
[19 Jan 2015 15:53] Miguel Solorzano 
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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 DATABASE kxnv;
Query OK, 1 row affected (0.00 sec)

mysql> USE kxnv;
Database changed
mysql>
mysql> CREATE TABLE IF NOT EXISTS `kxnv`.`metadata` (
    ->   `idmetadata` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `when` DATETIME NOT NULL,
    ->   `artist` VARCHAR(255) NOT NULL,
    ->   `album` VARCHAR(255) NULL,
    ->   `year` YEAR NULL,
    ->   `song` VARCHAR(255) NULL,
    ->   PRIMARY KEY (`idmetadata`),
    ->   UNIQUE INDEX `idmetadata_UNIQUE` (`idmetadata` ASC))
    -> ENGINE = InnoDB
    -> COMMENT = '      ';
Query OK, 0 rows affected (0.36 sec)

mysql> load data infile 'c:/tmp/maybe.txt' into table metadata;
Query OK, 87 rows affected (0.08 sec)
Records: 87  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from metadata where album="BVD-bktpro1"\G
*************************** 1. row ***************************
idmetadata: 41
      when: 2015-01-18 08:59:16
    artist: william van dyke
     album: BVD-bktpro1
      year: NULL
      song: N
1 row in set (0.03 sec)

mysql> delete from metadata;
Query OK, 87 rows affected (0.08 sec)

-- editing the life changing last \N to NULL

mysql> load data infile 'c:/tmp/maybe.txt' into table metadata;
Query OK, 87 rows affected (0.10 sec)
Records: 87  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from metadata where album="BVD-bktpro1"\G
*************************** 1. row ***************************
idmetadata: 168
      when: 2015-01-18 08:59:16
    artist: william van dyke
     album: BVD-bktpro1
      year: NULL
      song: NULL
1 row in set (0.00 sec)
[19 Jan 2015 15:59] Peter Laursen 
I wonder if here:

mysql> select * from metadata where album="BVD-bktpro1"\G
*************************** 1. row ***************************
..
      song: NULL

..  if this NULL is a true *NULL* (== undefined value) or the literal string 'NULL'?

-- Peter
-- not a MySQL/Oracle person.
[19 Jan 2015 20:53] J Scavok 
In response to your comments, I again tested my submission and was able to reproduce it. 

Then I realized: I forgot to include "lines terminated by '\r\n'" to my LOAD DATA LOCAL INFILE statement (since I'm using Windows).

When it was included, MySQL behaved as expected.

I apologize for wasting your time.

(MY FACE IS RED WITH EMBARRASSMENT.)
[19 Jan 2015 21:14] J Scavok 
FWIW note the inability to query records when the bogus 'N' appears in the 'song' column:

mysql> delete from metadata;
Query OK, 87 rows affected (0.02 sec)

# Intentionally omitting LINES TERMINATED BY '\r\n' in next statement

mysql> load data local infile 'c:/tmp/test.tsv' into table metadata;
Query OK, 87 rows affected (0.01 sec)
Records: 87  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from metadata where album like "BVD-bktpro1"\G
*************************** 1. row ***************************
idmetadata: 1947
      when: 2015-01-18 08:59:16
    artist: william van dyke
     album: BVD-bktpro1
      year: NULL
      song: N
1 row in set (0.00 sec)

mysql> select * from metadata where song="N"\G
Empty set (0.00 sec)

mysql> select * from metadata where song is NULL\G
Empty set (0.02 sec)

mysql> select * from metadata where song like "N"\G
Empty set (0.00 sec)
[19 Jan 2015 21:18] J Scavok 
P.S. to my last comment: the following statement *does* include results from the bogus 'song' column:

mysql> select * from metadata where song like "N%"\G
18 rows in set (0.00 sec)