CODEWITHSUNDEEP
mysqlsql
Arthur Nobrega
Arthur Nobrega

Reputation: 25

LOAD DATA LOCAL INFILE skipping rows without warnings

I have created the following table:

CREATE TABLE table_name
(
primaryid INT PRIMARY KEY,
caseid INT,
pt varchar(80)
);

Here is what my data looks like:

primaryid$caseid$pt
100640841$10064084$Pulmonary embolism
32990183$3299018$Pyrexia
32990183$3299018$Rash maculo-papular
32990183$3299018$Swelling face
35387833$3538783$Obesity
375988510$3759885$Gastric polyps
375988510$3759885$Gastritis atrophic

I Have loaded it into MySQL using the following statement:

LOAD DATA LOCAL INFILE '/path/to/file/input.txt' INTO TABLE table_name FIELDS TERMINATED BY '$' LINES TERMINATED BY '\n' IGNORE 1 LINES (primaryid, caseid,pt) ;

and I get as a result:

Query OK, 171925 rows affected (2.75 sec)
Records: 545256  Deleted: 0  Skipped: 373331  Warnings: 0

Can someone please tell me where I am going wrong? Why are 373331 records skipped and there are no warnings?

Is this an syntax issue or a problem with the data file, or both?

Upvotes: 1

Views: 1712

Answers (1)

Mr. Llama
Mr. Llama

Reputation: 20909

You've defined your primaryid column as a PRIMARY KEY which means that it must be unique. In your sample records alone, there's three lines with 32990183 as primaryid. Only one of those records will the loaded, the rest will be skipped.

You either need to fix your data, or not make primaryid a primary key.

Upvotes: 2

Related Questions