LOAD DATA INFILE syntax error

Question

query: load data infile 'systemmessage.txt' ignore into table systemmessage (message) lines starting by 'a,' terminated by '\0' ignore 1 lines

gives me a syntax error near 'lines starting by'. If I remove the 'starting by' part, the error is now with 'terminated by'. If I remove that too, the error is with 'ignore 1 lines'. Where the hell is the problem?? The file exists, the table exists, if I remove all checks it loads, but with the wrong data.

prepare again your systemmessage.txt to be exactly follow mysql specification - dev.mysql.com/doc/refman/5.1/en/load-data.html — ajreal
– ajreal, Commented Nov 16, 2010 at 17:56
OMG the problem is not in systemmessage.txt! It's an SQL syntax error! I'm ready to blow up with this incredibly dumb problem and you suggest me something I've been reading hundreds of times in the past few hours! Anyway, I finally managed to get past this but wasted more nerves than in the past week together... — jurchiks
– jurchiks, Commented Nov 16, 2010 at 18:12
you should just fool proof your systemmessage.txt is 100% compliance with mysql specification, did you try just insert a single line (single record) to ensure at least it will work for 1 line ? — ajreal
– ajreal, Commented Nov 16, 2010 at 18:43

Ike Walker · Accepted Answer · 2010-11-16 19:48:51Z

1

You just need to move the column list to the end of the SQL statement.

As described in the manual, the format info (lines starting by, skip 1 lines, etc) need to be specified before the (optional) column list:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Here's the fixed query:

load data infile '/tmp/systemmessage.txt' 
ignore into table systemmessage 
lines starting by 'a,' 
terminated by '\0' 
ignore 1 lines
(message)

answered Nov 16, 2010 at 19:48

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LOAD DATA INFILE syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related