7

We're moving our database from being on the webserver to a separate server (from an Amazon EC2 webserver to an RDS instance.)

We have a LOAD DATA INFILE that worked before that is going to need the LOCAL keyword added now that the database will be on a different machine to the webserver.

Testing on my dev server, it turns out that it doesn't work:

  • I can still LOAD DATA INFILE from php as I have been
  • I can LOAD DATA LOCAL INFILE from mysql commandline (with --local_infile=1)
  • I can't LOAD DATA LOCAL INFILE from php.

Between those 2 things that do work, it rules out:

  • problems with the sql or php code
  • problems with the upload file, including syntax and file permissions
  • mysql server settings problems

The error I get is: ERROR 1148 (42000): The used command is not allowed with this MySQL version (I get that error from the mysql commandline if I don't use --local_infile=1)

A few other bits of relevant info:

  • Ubuntu 12.04, mysql 5.5.24, php 5.3.10
  • I'm using php's mysql_connect (instead of mysqli, because we're planning on using facebook's hiphop compiler which doesn't support mysqli.)

  • Because of that, the connect command needs an extra flag set:

    mysql_connect($dbHost, $dbUser, $dbPass, false, 128);
  • I've used phpinfo() to confirm that mysql.allow_local_infile = On
  • I've tried it on Amazon RDS (in case it was a problem in my dev server) and it doesn't work there either. (With the local_infile param turned on.)

The only thing I've read about that I haven't tried is to compile mysql server on my dev server with the flag turned on to allow local infile... but even if I get that working on my dev server it's not going to help me with Amazon RDS. (Besides which, LOAD DATA LOCAL INFILE does work from the mysql commandline.)

It seems like it's specifically a problem with php's mysql_connect()

Anybody using LOAD DATA LOCAL INFILE (maybe from Amazon RDS) that knows the trick to getting this to work?

Redzarf
  • 2,578
  • 4
  • 30
  • 40
  • Thanks, according to [http://php.net/releases/NEWS_5_4_0_beta1.txt](http://php.net/releases/NEWS_5_4_0_beta1.txt) that was fixed in php 5.3.9 and I'm using php 5.3.10-1ubuntu3.4 though I might try compiling php with the changes myself if noone else has any suggestions. – Redzarf Oct 22 '12 at 18:17
  • Actually, looking again, that bug appears to be purely related to PDO (which you're not using). – eggyal Oct 22 '12 at 18:48
  • For anyone else who comes looking at this later, the bug that @eggyal mentioned was https://bugs.php.net/bug.php?id=54158 – Redzarf Oct 23 '12 at 12:13

5 Answers5

9

I've given up on this, as I think it's a bug in php - in particular the mysql_connect code, which is now deprecated. It could probably be solved by compiling php yourself with changes to the source using steps similar to those mentioned in the bug report that @eggyal mentioned: https://bugs.php.net/bug.php?id=54158

Instead, I'm going to work around it by doing a system() call and using the mysql command line:

$sql = "LOAD DATA LOCAL INFILE '$csvPathAndFile' INTO TABLE $tableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\\"' ESCAPED BY '\\\\\\\\' LINES TERMINATED BY '\\\\r\\\\n';";
system("mysql -u $dbUser -h $dbHost --password=$dbPass --local_infile=1 -e \"$sql\" $dbName");

That's working for me.

Redzarf
  • 2,578
  • 4
  • 30
  • 40
  • 1
    For what it's worth, it looks like this is in fact a PHP 5.3.X bug. I'm seeing the exact same problem, it looks like the PHP source for the PDO MySQL driver is, simply and infuriatingly enough, missing a "local_infile = 1;" Supposedly the 5.4 release fixes this, although I haven't verified that for myself yet. – Chris Tonkinson Nov 28 '12 at 04:11
  • @Redzarf why are also those escaping slashes necessary? – codecowboy Mar 27 '14 at 17:31
  • @codecowboy first escaping because it's inside php strings (") and then escaping because it's inside sql strings ('). It does get a bit crazy, but on the first line \\\\ ends up being used as a single \ – Redzarf Mar 28 '14 at 16:22
  • I am getting an error ERROR 1046 (3D000) at line 1: No database selected – Ronak Shah Jul 23 '16 at 11:26
8

Here's a check list to rule out this nasty bug:

1- Grant the user FILE privileges in MySQL, phpMyAdmin generaly does not cover this privilege:

GRANT FILE ON *.* TO 'db_user'@'localhost';

2- Edit my.cnf in /etc/mysql/ or your mysql path:

[mysql]
local-infile=1
[mysqld]
local-infile=1

3- In php.ini at /etc/php5/cli/ or similar:

mysql.allow_local_infile = On

Optionally you can run ini_set in your script:

ini_set('mysql.allow_local_infile', 1);

4- The database handler library must use the correct options.
PDO:

new PDO('mysql:host='.$db_host.'.;dbname='.$db_name, $db_user, $db_pass,
array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

mysqli:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

5- Make sure that the INFILE command uses the absolute path to the file and that it exists:

$sql = "LOAD DATA INFILE '".realpath(is_file($file))."'";

6- Check that the target file and parent directory are readable by PHP and by MySQL.

$ sudo chmod 777 file.csv

7- If you are working locally you can remove the LOCAL from your SQL:

LOAD DATA INFILE

Instead of:

LOAD DATA LOCAL INFILE

Note: Remember to restart the MySQL and PHP services if you edit their configuration files.

Hope this helps someone.

pachanka
  • 391
  • 4
  • 16
5

As referred in this post, adding 3rd and 4th parameter to mysql_connect are required to get LOAD LOCAL DATA INFILE working. It helped me. Any other suggestions (apparmor, local-infile=1 in my.cnf widely discussed in internet) did not help. Following PHP code worked for me!

mysql_connect(HOST,USER,PASS,false,128);

True, this is in manual, too.

Community
  • 1
  • 1
Kristjan Adojaan
  • 527
  • 7
  • 10
2

use the following line that client activates with infile true

mysql --local-infile=1 -u root -p

user2408935
  • 45
  • 3
  • Great checklist @pachanka! When i did a PHP update yesterday to 7.2.17 my LOAD LOCAL DATA INFILE script gave me permission errors. Your no.3- solved it. Seems my /etc/php/7.2/cli/php.ini was overwritten and I fixed/uncommented the line ***mysql.allow_local_infile = On*** . I did not need to restart PHP since my script uses PHP CLI. Everything works fine now. – Asle Apr 24 '19 at 14:54
0

If you're doing this in 2020, a tip for you is to check your phpinfo.php or php --ini for the location of the configuratin file. For me I was using virtualmin and changing the php ini file but my site had it's own specific ini file. Once I located it's location and changed it everything went back to normal.

Justin Terrell
  • 23
  • 3