load data local infile mysql server php Ubuntu

Question

I am posting the resolution since it drove me crazy for a few days -- it turned out to be the AppArmor settings. This thread finally got me solved: How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

I see this is marked as duplicate but all the other suggestions seem to point to editing my.cnf which is not working for me.

I cannot get Load Data Local Infile to work. My MySQL server version is 5.5.41 and my Ubuntu version is 14.04.2. My my.cnf has "local-infile" under mysql, mysqld and client. I am below specifying flag 128 in the connection string per another post on Stackoverflow. The code below works great when I run it on the client but now that it lives on the server it continuously throws

"The used command is not allowed with this MySQL version.

EDIT, running this from command line is successful:

mysql> LOAD DATA LOCAL INFILE '/home/aa/ER.csv'
    ->      INTO TABLE ER_import
    ->      FIELDS TERMINATED BY ','
    ->      LINES TERMINATED BY '\r\n'
    ->      IGNORE 1 LINES
    ->     (Paid_Amount,
    -> Pro_Number,
    -> Invoice,
    -> Ship_Date,
    -> BL,
    -> Carrier_Name,
    -> Check_Number,
    -> Paid_Date,
    -> Shipper_City,
    -> Shipper_State,
    -> Shipper_Name_1,
    -> Recipient_City,
    -> Recipient_State,
    -> Recipient_Name_1,
    -> Batch_Number,
    -> Actual_Weight,
    -> Billed_Amount
    -> );
Query OK, 8585 rows affected, 4 warnings (0.21 sec)
Records: 8585  Deleted: 0  Skipped: 0  Warnings: 4

yet this keeps retuning that the file is not found: "File '/home/aa/ER.csv' not found (Errcode: 13)" the file is definitely there and permission are correct:

$delete = "DELETE FROM ER_import";

 if (!mysqli_query($conn,$delete))
   {
   echo("Error description: " . mysqli_error($conn));
   }

$query = <<<eof
    LOAD DATA INFILE '/home/aa/ER.csv'
     INTO TABLE ER_import
     FIELDS TERMINATED BY ','
     LINES TERMINATED BY '\r\n'
     IGNORE 1 LINES
    (Paid_Amount,
Pro_Number,
Invoice,
Ship_Date,
BL,
Carrier_Name,
Check_Number,
Paid_Date,
Shipper_City,
Shipper_State,
Shipper_Name_1,
Recipient_City,
Recipient_State,
Recipient_Name_1,
Batch_Number,
Actual_Weight,
Billed_Amount
);
eof;


if($results = mysqli_query($conn, $query)){
  echo $file. " has been imported!";
} else die(mysqli_error($conn));

Is it something obvious??

See also [How can I correct MySQL Load Error](http://stackoverflow.com/q/16285864). — eggyal, Mar 23 '15 at 20:55

score 0 · Answer 1 · answered Mar 23 '15 at 21:01

try this way

$query = <<<eof
    LOAD DATA LOCAL INFILE DIR_FILE
     INTO TABLE ER_import
     FIELDS TERMINATED BY ','
     LINES TERMINATED BY '\r\n'
     IGNORE 1 LINES
eof;

$query = str_replace('DIR_FILE',$dir.$file,$query);

because <<<eof means you don't want php to parse anything inside your following string. But you are trying to $dir$file that means you are expecting thta php will replace this vars with values.

score 0 · Answer 2 · answered Mar 23 '15 at 21:05

There's a nonintuitive twist to the meaning of LOCAL in LOAD DATA LOCAL INFILE. You only need to use LOCAL when the file you're loading is not local to the server. LOCAL tells the MySQL client to pick up a local file and place it in a temporary directory on the server. https://dev.mysql.com/doc/refman/5.5/en/load-data.html

Try taking the word LOCAL out of your command when you run that php script on your server.

score 0 · Answer 3 · answered Mar 23 '15 at 21:10

0

I had the same issue a few years back.

local-infile needs to enabled for both the client and server.

I would recommend that you try running LOAD DATA LOCAL INFILE from the command line interface first before trying in PHP. To make sure that the connection from the client enables local-infile, run this command:

mysql --local-infile=1 -h REPLACE_HOST_IP -u username -p

Good luck!

answered Mar 23 '15 at 21:10

Ola

68
4

interesting, i got this error: Host ' hostname ' is not allowed to connect to this MySQL server – caro Mar 23 '15 at 22:57
Well, let's fix that first. We need to make sure that the server accepts remote mysql connection. For that, modify your my.cnf file accordingly. Also grant access the user `username` to connect remotely as such: `GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'` – Ola Mar 24 '15 at 14:28
i got past that -- do you see anything in my edits that are obvious? – caro Mar 24 '15 at 14:36

load data local infile mysql server php Ubuntu

3 Answers3