CODEWITHSUNDEEP
mysqlcsvmariadb
gabsong
gabsong

Reputation: 45

MySQL / MariaDB LOAD DATA INFILE with CSV without an id column (primary key) and trying to auto increment via the DBMS does not recognize SET command

I have created a table that has the following definition:

CREATE TABLE vacayhome.photo (
  id INT NOT NULL AUTO_INCREMENT,
  url_path CHAR NOT NULL,
  caption CHAR NOT NULL,
  space_type CHAR NOT NULL,
  is_main BOOLEAN NOT NULL,
  listing_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (listing_id) REFERENCES listing (id)
    ON UPDATE RESTRICT
);

The data I'm trying to insert to this table has the following form:

url_path,caption,space_type,is_main,listing_id
JFK/015ec48e93480.jpg,An interior designer dream,,true,10000000
JFK/9184bd57e9f80.jpg,"Ready for you, YASS",,false,10000000
BCN/5ccd9b138c76.jpg,"Stay -- you're welcome",,false,10000001
BCN/5fbb3a5ac2b30.jpg,Warm sunlight throughout,,false,10000001

And this is my LOAD DATA statement into the empty table from the MariaDB and MySQL documentation:

ALTER TABLE photo DISABLE KEYS;
BEGIN;
LOAD DATA INFILE '/path/to/photos.csv' INTO TABLE photo
  FIELDS
    OPTIONALLY ENCLOSED BY '"'
    TERMINATED BY ','
  LINES
    TERMINATED BY '\n'
  IGNORE 1 ROWS (url_path,caption,space_type,is_main,listing_id);
  SET id=NULL;
COMMIT;
ALTER TABLE photo ENABLE KEYS;

The OPTIONALLY ENCLOSED BY is being used for the CHAR columns that have a comma in the text, and the IGNORE 1 ROWS is used to ignore the header row.

When I try to load the data, I get the following error:

ERROR 1193 (HY000) at line 33: Unknown system variable 'id'

I've also tried adding SET id = NULL from answers in other StackOverflow posts like this one. What am I doing wrong?

Upvotes: 0

Views: 1696

Answers (2)

jpaljasma
jpaljasma

Reputation: 1612

You have an extra semicolon before the SET ID=NULL; directive. Below code ran successfully in my local MySQL environment:

ALTER TABLE photo DISABLE KEYS;
BEGIN;
LOAD DATA LOCAL INFILE '/path/to/photos.csv' INTO TABLE photo
  FIELDS
    OPTIONALLY ENCLOSED BY '"'
    TERMINATED BY ','
  LINES
    TERMINATED BY '\n'
  IGNORE 1 ROWS (url_path,caption,space_type,is_main,listing_id)
  SET id=NULL;
COMMIT;
ALTER TABLE photo ENABLE KEYS;

You might want to check the column types as well (for example, use VARCHAR instead). Good luck.

Upvotes: 2

C-Dog
C-Dog

Reputation: 125

Have you tried putting the path in quotes?

As shown below...

url_path,caption,space_type,is_main,listing_id
"JFK/9184bd57e9f80.jpg","Ready for you, YASS",,false,10000000

Upvotes: 0

Related Questions