0

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?

gabsong
  • 55
  • 10
  • *CREATE TABLE vacayhome. **photo*** and *INTO TABLE **listing***... – Akina Feb 19 '20 at 19:50
  • I found the issue on my original post, I had the incorrect table reference (had INTO TABLE listing, instead of INTO TABLE photo). – gabsong Feb 19 '20 at 21:32
  • After correcting the above, I'm running into a different issue. This is the error that I get now: ERROR 1193 (HY000) at line 33: Unknown system variable 'id' – gabsong Feb 19 '20 at 21:33
  • *This is the error that I get now: ERROR 1193 (HY000) at line 33: Unknown system variable 'id'* Remove `SET id=NULL;` operator - it is an error. *I've also tried adding SET id = NULL from answers in other StackOverflow posts like this one.* The topic by provided link contains nothing about `SET id=NULL;`. – Akina Feb 20 '20 at 04:53

2 Answers2

2

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.

jpaljasma
  • 1,612
  • 16
  • 21
  • 1
    That worked! It makes sense since that split up the statement so id became a system variable instead of a user variable (should have reviewed the code thoroughly). By the way, is there a reason to use VARCHAR instead? @peter – gabsong Feb 20 '20 at 19:26
  • 1
    Glad it worked, @gabsong. The differences between CHAR and VARCHAR is rather well explained here, but TL;DR - use VARCHAR if the lengh of the strings differ greatly row by row: https://stackoverflow.com/questions/1885630/whats-the-difference-between-varchar-and-char – jpaljasma Feb 20 '20 at 20:53
0

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
C-Dog
  • 125
  • 9