user74091 user74091 - 2 years ago 111
SQL Question

what is the error in my syntax preventing me from loading my data file into mysql

i am attempting to upload a .txt file into my sql database i just created. i was able to load several lines of data into the table using

, but when i tried to utilize
LOAD DATA LOCAL INFILE '/pathto/file.txt' INTO TABLE mytable
, it first gave me the error that command is not allowed in my version of mysql. so after i read How can I correct MySQL Load Error i used the
--local-infile=1 -u mysqlname -p
followed by the above command i have repeatedly been awarded the syntax error. ive tried this to load the .txt file with all sorts of different combinations of the above, and still get one of the two errors. below is a screen shot.

this is with ubuntu 15.10 and mysql version 5.6.28-0ubuntu0.15.10.1.

screen shot of terminal in question

enter image description here

Answer Source

--local-infile is a server and client parameter. It's not valid syntax as part of a statement such as LOAD DATA or INSERT statement.

You would specify server variables and options either in the appropriate sections of the my.cnf file, or as command line parameters to the MySQL program being executed.

For example, at the OS prompt...

# mysql -h myserverhost -u mysqlname -p --local-infile=1 

That option has to be specified for the MySQL server.

If you are connecting as user@localhost, you don't need LOCAL. You can give the MySQL user (whichever OS user the mysql server is running under) read privilege on the file you want to load... chmod ugo+r /mypath/myfile (and read execute on the directories in the path.

You only need LOCAL if the msyql user isn't @'localhost'.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download