Geek Answers Handbook

LOAD DATA LOCAL INFILE does not work - from php to mysql (on Amazon rds)

We move our database from the web server to a separate server (from the Amazon EC2 web server to an RDS instance).

We have a LOAD DATA INFILE that worked before the LOCAL keyword is needed, which is now added when the database is located on another computer on the web server.

Testing on my dev server, it turned out that it does not work:

  • I can still load LOAD DATA INFILE from php since I was
  • I can LOAD DATA LOCAL INFILE from mysql command line (with -local_infile = 1)
  • I can not LOAD DATA LOCAL INFILE from php.

Between the two things that work, this eliminates:

  • problems with sql or php code
  • Download file issues, including syntax and file permissions.
  • Problems with mysql server settings.

The error I get: ERROR 1148 (42000): The command used is not allowed with this version of MySQL (I get this error from the mysql command line if I do not use --local_infile = 1)

A few other bits of relevant information:

  • Ubuntu 12.04, mysql 5.5.24, php 5.3.10
  • I use php mysql_connect (instead of mysqli, because we plan to use the hiphop facebook compiler that does not support mysqli.)

  • Because of this, the connect command requires an additional flag:

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

The only thing I read about what I have not tried is to compile the mysql server on my development server with the flag turned on to allow local penetration ... but even if I get this work on my dev server, I’m not going to help me with Amazon RDS. (In addition, LOAD DATA LOCAL INFILE works from the mysql command line.)

It looks like this is a php issue with mysql_connect ()

Does anyone use a LOAD DATA LOCAL INFILE (possibly from Amazon RDS) that knows about this to make this work?

+7
source share
4 answers

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

Instead, I will bypass it by making a call to system () 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");

This works for me.

+9
source

As indicated in this post , adding the third and fourth parameters to mysql_connect is necessary for LOAD LOCAL DATA INFILE to work. It helps me. Any other suggestions (apparmor, local-infile = 1 in my.cnf, widely discussed on the Internet) did not help. PHP code worked after me!

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

True, this is in manual .

+5
source

Here is a checklist to rule out this nasty bug:

1. Grant FILE user privileges in MySQL, phpMyAdmin sharing does not apply to this privilege:

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

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

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

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

 mysql.allow_local_infile = On

Perhaps you can run ini_set in your script:

 ini_set('mysql.allow_local_infile', 1);

4- The database handler library must use the correct parameters.
<i> PDO

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

<i> MySQLi

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

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

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

6- Make sure the target file and parent directory are read by PHP and MySQL.

 $ sudo chmod 777 file.csv

7- If you work locally, you can remove LOCAL from your SQL:

 LOAD DATA INFILE

Instead:

 LOAD DATA LOCAL INFILE

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

Hope this helps someone.

+5
source

use the following line that the client activates with infile true

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

+2
source

More articles: