Syntax error in load data infile

Question

I don't know why I am getting a syntax error. I have checked my query carefully many times and read up on mysql reference for load data infile and everything seems fine. Here is my query:

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight

This is the error that I am getting:

Notice: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option=@option, option_value=@option_value, required=@required, quantity=@quanti' at line 1
Error No: 1064
LOAD DATA LOCAL INFILE '/tmp/phpO6k4u3' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight

• user52733: in my syntax for load data in tables database just like this root@localhost:~# cd /tmp/phpnm6Zek && ls | grep phpnm6Zek | xargs -I {} mysql -uroot -pyour_password -e "use your_database; LOAD DATA LOCAL INFILE '{}' INTO TABLE loaddata_temp FIELDS TERMINATED BY ''"; and it works...

Answer 1

The word option is a reserved word :

You need to surround the word option with back quotes.

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek' 
IGNORE INTO TABLE loaddata_temp 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, 
@quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, 
`option`=@option, option_value=@option_value, required=@required, quantity=@quantity, 
subtract=@subtract, price=@price, points=@points, weight=@weight

Give it a Try !!!