0

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 INSERT INTO, 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.

I've 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

Elydasian
  • 2,016
  • 5
  • 23
  • 41
user74091
  • 301
  • 3
  • 13
  • have you migrate it from windows OS? ubuntu is unix system so it case-sensitive, try check your table and column name should be same – keronconk Apr 17 '16 at 05:57
  • I used generics names in the question, all of the names of tables and the files were correct as seen in the screen shot. No I haven't used any of this on Windows strictly Ubuntu – user74091 Apr 17 '16 at 05:59
  • have you try insert only 1 row for example from your text file? – keronconk Apr 17 '16 at 06:09
  • Using the 'insert into' command? Yes. It worked fine. But that was not from a .txt file, I did that straight from the cl. – user74091 Apr 17 '16 at 06:11
  • try to look your text file with tools like notepad++, what is the carriage return character in your text file? – keronconk Apr 17 '16 at 06:21

1 Answers1

0

--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'.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Ok, so specify '--local-infile=1' when logging onto the MySQL, this should give me the privileges of using the "load data infile '/path/file.txt into table mytable" command and will prevent a syntax error. – user74091 Apr 17 '16 at 06:30
  • And omit the 'local' because that's redundant – user74091 Apr 17 '16 at 06:30
  • It's not redundant. Either you need both, or you don't need either. Connect to MySQL like you are doing, where you are going to run the LOAD DATA statement. Run this SQL statement... SELECT USER(). If that returns a value ending in '@localhost', then you don't need the LOCAL keyword. Otherwise, you do need the LOCAL keyword. None of this secret voodoo. This is all adequately documented in the MySQL Reference Manual. – spencer7593 Apr 17 '16 at 06:36
  • user is root@local host, but when i tried without LOCAL it gave me the error of **ERROR 29 (HY000): File '/home/mkaepernik/Documents/soiree.txt' not found (Errcode: 13 - Permission denied)**. anyways i tried with LOCAL and it worked, it uploaded my data. i guess i was including the **'--local-infile=1'** in the wrong location, but your recommendation to include it when logging on was sufficient. – user74091 Apr 17 '16 at 06:58
  • i am confused why i had to include **LOCAL** but im sure the man pages will explain. thanks – user74091 Apr 17 '16 at 06:59
  • 1
    The LOCAL keyword specifies that the MySQL *client* will read the file, and pass the rows to the server. Without the LOCAL keyword, we're telling the MySQL *server* to look on the server box to read the file. Given that you are connected as '@localhost', you don't really need the LOCAL keyword. The error that was reported "Permission denied" means that the MySQL *server* was not able to read the file because of OS permissions on the file (or a directory in the path to the file) prevented MySQL from reading the file. Normal OS security stuff. – spencer7593 Apr 17 '16 at 07:28
  • As a quick workaround to the OS permissions issue, copy the file to /tmp/myfile.csv. Then do a chmod ugo+r /tmp/myfile.csv, and then try starting the mysql client and runing a LOAD DATA .INFILE '/tmp/myfile.csv'. That ensures that the OS user that is running the MySQL server will have OS permission to read that file. – spencer7593 Apr 17 '16 at 07:31
  • I'll up vote when I have more points, that was a good answer. Thx – user74091 Apr 17 '16 at 11:47