php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #55737 LOAD DATA LOCAL INFILE - The used command is not allowed with this MySQL versio
Submitted: 2011-09-20 09:49 UTC Modified: 2012-05-04 13:46 UTC
Votes:15
Avg. Score:4.5 ± 0.8
Reproduced:10 of 12 (83.3%)
Same Version:10 (100.0%)
Same OS:3 (30.0%)
From: stefan dot kaifer at hartmann dot info Assigned: mysql
Status: Not a bug Package: MySQL related
PHP Version: 5.3.8 OS: opensuse 11.0
Private report: No CVE-ID:
 [2011-09-20 09:49 UTC] stefan dot kaifer at hartmann dot info
Description:
------------
Hello

I've compiled php myself with this command:

./configure --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd ....

Now my php-applications can't use 
"LOAD DATA LOCAL INFILE '/tmp/import-20110919173927-78613.txt' INTO TABLE ..." anymore!

On the command prompt I can use "LOAD DATA LOCAL INFILE", that means mysql server allows me to use "LOAD DATA LOCAL INFILE", the server variable "local infile" is setted to "ON" (with "local-infile=1" in the my.cnf).

It seems a php-mysqlnd problem. Neither the mysql nor the mysqli extensions allows me to use "LOAD DATA LOCAL INFILE". This is the Connect-line in my code:

$Connect = mysql_connect($DB_Host .":".$DB_Port,$DB_User,$DB_Password, FALSE, 128);

The error is:
#1148 - The used command is not allowed with this MySQL version 

Mysql-server: 5.5.16
PHP: 5.3.8
PHP-MYSQL-client: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $
PHPINFO:

mysql
MySQL Support	enabled
Active Persistent Links 	0
Active Links 	0
Client API version 	mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

Directive	Local Value	Master Value
mysql.allow_local_infile	On	On
mysql.allow_persistent	Off	Off
mysql.connect_timeout	60	60
mysql.default_host	no value	no value
mysql.default_password	no value	no value
mysql.default_port	no value	no value
mysql.default_socket	no value	no value
mysql.default_user	no value	no value
mysql.max_links	Unlimited	Unlimited
mysql.max_persistent	Unlimited	Unlimited
mysql.trace_mode	Off	Off

mysqli
MysqlI Support	enabled
Client API library version 	mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $
Active Persistent Links 	0
Inactive Persistent Links 	0
Active Links 	0

Directive	Local Value	Master Value
mysqli.allow_local_infile	On	On
mysqli.allow_persistent	On	On
mysqli.default_host	no value	no value
mysqli.default_port	3306	3306
mysqli.default_pw	no value	no value
mysqli.default_socket	no value	no value
mysqli.default_user	no value	no value
mysqli.max_links	Unlimited	Unlimited
mysqli.max_persistent	Unlimited	Unlimited
mysqli.reconnect	Off	Off

mysqlnd
mysqlnd	enabled
Version 	mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $
Compression 	supported
SSL 	supported
Command buffer size 	4096
Read buffer size 	32768
Read timeout 	31536000
Collecting statistics 	Yes
Collecting memory statistics 	No
Tracing 	n/a 

I hope, that somebody can help.

Best regards

Stefan

Test script:
---------------
$Connect = mysql_connect($DB_Host .":".$DB_Port,$DB_User,$DB_Password, FALSE, 128);
$StrSQL = "LOAD DATA LOCAL INFILE '/tmp/test.txt' INTO TABLE testtable 
          FIELDS TERMINATED BY '</td><td>' LINES
          STARTING BY '<tr><td>'
          TERMINATED BY '</td></tr>' 
          IGNORE 1 LINES .....";
$Result1 = mysql_db_query($DB,$StrSQL,$Connect);


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-10-03 21:08 UTC] richardpq at gmail dot com
Hi I have exact the same problem, like you I compile php using those options, I 
have the same php version and mysql 5.5.15, have you resolve the problem?
 [2011-10-18 16:27 UTC] denis_truffaut at hotmail dot com
Related bugs :

https://bugs.php.net/bug.php?id=46964
https://bugs.php.net/bug.php?id=54158

A PHP Dev said it was resolved in 5.3.6, but it came back.
As it is a very common usage of MySQL, especialy to save/import data, it had to be fixed in priority.

The override PDO::MYSQL_ATTR_LOCAL_INFILE => 1 also doesn't not work.
What to do ?! :O
 [2011-10-18 20:23 UTC] andrey@php.net
-Status: Open +Status: Feedback
 [2011-10-18 20:23 UTC] andrey@php.net
mysqlnd allows LOAD DATA LOCAL in all cases but
if open_basedir is enabled. If open_basedir is set it is disabled regardless where the file to be loaded resides. This might be too strict for shared envs.
Do you have open_basedir enabled?
 [2011-10-18 20:23 UTC] andrey@php.net
-Assigned To: +Assigned To: mysql
 [2011-10-19 02:30 UTC] richardpq at gmail dot com
No, I dont have enable, my php.ini:

; open_basedir, if set, limits all file operations to the defined directory
; and below.  This directive makes most sense if used in a per-directory
; or per-virtualhost web server configuration file. This directive is
; *NOT* affected by whether Safe Mode is turned On or Off.
; http://php.net/open-basedir
;open_basedir =

Also has the first guy said... I can use LOAD DATA LOCAL INFILE, from MySql 
client but not from an application using PHP.
 [2011-10-20 12:57 UTC] denis_truffaut at hotmail dot com
Some bug fix is planned for PHP 5.4 :

http://php.net/releases/NEWS_5_4_0_beta1.txt

- PDO MySQL driver:
  . Fixed bug #54158 (MYSQLND+PDO MySQL requires #define MYSQL_OPT_LOCAL_INFILE)
  (Andrey)
 [2011-10-20 13:07 UTC] stefan dot kaifer at hartmann dot info
-Status: Feedback +Status: Assigned
 [2011-10-20 13:07 UTC] stefan dot kaifer at hartmann dot info
Thanx, it's very good!
 [2011-10-20 13:29 UTC] richardpq at gmail dot com
"Some bug fix is planned for PHP 5.4", what is that mean? no solution for php 
5.3?

I would prefer a solution for this version, rather than wait for the final 
release of the new version, testing and see if it not affect others thing.
 [2011-10-21 11:20 UTC] andrey@php.net
-Status: Assigned +Status: Feedback
 [2011-10-21 11:20 UTC] andrey@php.net
Can you try 5.3-svn? Altough the NEWS entry mentions 5.4 the fix has landed in 5.3 too (committed on Sep 2). 5.3.8 was released on Aug 23rd.
 [2011-10-25 18:36 UTC] richardpq at gmail dot com
andrey@php.net,

Neither 5.3snv and 5.3.8 snv work, the same problem
 [2011-10-25 18:53 UTC] andrey@php.net
Which is the last version of 5.3 which worked for you?
 [2011-10-25 19:21 UTC] richardpq at gmail dot com
I haven't test this functionality before, this is the first time that I tested 
it. 

@Stefan point that it was working before and now not, but I don't know which 
version he use... At the moment I am testing locally so I can put the files in my 
mysql data directory and it works, but I don't know, what I will do when I have 
to upload to the server since is not a private server.
 [2012-02-02 14:55 UTC] stefan dot kaifer at hartmann dot info
Hi

sorry for the late answer: 5.3.4 worked for me, 5.3.4 to 5.3.7 I don't tested!
5.3.8 and 5.3.9 dont't works for me. I've compiled all versions with the same "configure"-parameters.

I hope, that somebody can solve the problem.

Best reguards

Stefan
 [2012-05-04 13:46 UTC] uw@php.net
-Status: Feedback +Status: Not a bug
 [2012-05-04 13:46 UTC] uw@php.net
So, what is this report about? If it's on ext/mysql, I call it not a bug. Thus, closing. If config is correct, things work just fine.

----------
$host = "localhost:/var/run/mysql/mysql.sock";
$user = "root";
$pass = "";
$flags = 128;
$db = "test";

printf("PHP %s\n", PHP_VERSION);

$file = getcwd() . DIRECTORY_SEPARATOR . "foo.txt";
if (!($fp = fopen($file, "w")))
	die(sprintf("Failed to open '%s' for writing\n", $file));

if (!fwrite($fp, "1;a\n") || !fwrite($fp, "2;b\n"))
	die(sprintf("Failed to write to '%s'\n", $file));

fclose($fp);

$sql = sprintf("LOAD DATA LOCAL INFILE '%s' INTO TABLE test FIELDS TERMINATED BY ';'", $file);

if (!($mysql = mysql_connect($host, $user, $pass, true, $flags)))
  die(sprintf("Failed to connect to MySQL\n"));

printf("MySQL %s\n", mysql_get_server_info($mysql));

mysql_select_db($db);
mysql_query("DROP TABLE IF EXISTS test", $mysql);
mysql_query("CREATE TABLE test(id INT, col_a VARCHAR(255))", $mysql);

mysql_close($mysql);

if (!($mysql = mysql_connect($host, $user, $pass, true, $flags)))
  die(sprintf("Failed to connect to MySQL\n"));

if (!mysql_db_query($db, $sql, $mysql))
  die(sprintf("LOAD DATA failed: %s\n", mysql_error($mysql)));


$res = mysql_query("SELECT * FROM test", $mysql);
while ($row = mysql_fetch_row($res))
	var_dump($row);

-------- gives -------------

PHP 5.3.12-dev                                                                                                                                                                      
MySQL 5.5.16-log                                                                                                                                                                    
array(2) {                                                                                                                                                                          
  [0]=>                                                                                                                                                                             
  string(1) "1"                                                                                                                                                                     
  [1]=>                                                                                                                                                                             
  string(1) "a"                                                                                                                                                                     
}                                                                                                                                                                                   
array(2) {                                                                                                                                                                          
  [0]=>                                                                                                                                                                             
  string(1) "2"                                                                                                                                                                     
  [1]=>                                                                                                                                                                             
  string(1) "b"                                                                                                                                                                     
}
 [2012-05-06 00:36 UTC] denis_truffaut at hotmail dot com
To : uw@php.net

This bug appears when you use both PDO (pdo_mysql) and mysqlnd (the php mysql 
native driver).

The last time I tested PHP 5.4, it was not solved so i tricked the sources to 
make it work, before compiling php.

A dirty fix is :

sudo sed -i -e 's/if (mysql_options(H->server, 
MYSQL_OPT_LOCAL_INFILE/local_infile = 1;if (mysql_options(H->server, 
MYSQL_OPT_LOCAL_INFILE/g' ext/pdo_mysql/mysql_driver.c
 [2012-11-22 03:10 UTC] major_sheisskopf at hotmail dot com
Guys. The answer to this is very easy if you are using the deprecated mysql 
functions.

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


Usually, you use mysql_connect without the last 2 optional arguments. But check 
out the manual page for it and you'll see that 128 enables LOAD DATA INFILE.

I don't know what the solution is for PDO, as I have not switched over to using 
it yet.
 [2013-03-22 12:27 UTC] haertl dot mike at gmail dot com
The bug still doesn't seem to be solved. I'm on Ubuntu 12.10 with php-fpm 5.4.6-
1ubuntu1.2 amd64 and still can call "LOAD DATA LOCAL INFILE" through PDO. It's 
still the same error:

#1148 - The used command is not allowed with this MySQL version 

I've also tried to set "local-infile" in the [client] and [mysql] section of 
MySQL`s my.cnf without success. LOAD DATA LOCAL INFILE works fine with the mysql 
client on console though.
 [2013-03-31 08:57 UTC] mamm at ya dot ru
I have same problem, ubuntu 12, and load data cant be enabled, works only from mysql
 [2013-04-03 21:16 UTC] haertl dot mike at gmail dot com
On a client machine this issue went away, when i replaced the MySQL clients with 
those from MariaDB.
 [2013-05-14 08:49 UTC] dimm-man at yandex dot ru
In Debian Wheezy amd64 (PHP 5.4), this problem has not been solved. "LOAD DATA LOCAL INFILE" a PDO does not work.
 [2013-05-20 19:54 UTC] sward at undergroundelephant dot com
This bug still exists and is affecting me on these versions:

PHP 5.4.6-1ubuntu1.1 (cli) (built: Nov 15 2012 01:18:34)
PHP 5.4.9-4ubuntu2 (cli) (built: Mar 11 2013 16:05:09)

Please remove the "not a bug" status and reopen this ticket.  The issue is *not* 
external.  The problem has something to do with the PDO driver.
 [2013-08-19 15:34 UTC] shag134 at hotmail dot com
Same here, bug still exists

PHP 5.4.9-4ubuntu2.2 (cli) (built: Jul 15 2013 18:23:35)
 [2013-10-01 06:15 UTC] sasaloginov at gmail dot com
The prolem is remains
PHP 5.5.4-1+debphp.org~raring+1 (cli) (built: Sep 27 2013 11:40:37)
Mysql: 5.5.32-0ubuntu0.13.04.1
 [2013-10-01 14:28 UTC] wces77 at gmail dot com
Hi, 

You'll need to modify apparmor's config file. And remove the 'local' word from your sentence.

See this.
http://stackoverflow.com/questions/4215231/mysql-load-data-infile-error-code-13

You need to add the path you want to import from...
 [2013-11-13 09:33 UTC] walkleyn at gmail dot com
Hi

The LOAD DATA LOCAL INFILE function does not work with mysql5.5 and php5.4.4 on Debian Wheezy with open_basedir restrictions in place.
It works perfectly fine when open_basedir is disabled or set to nothing.
So I guess I'm following on from what Andrey has posted: "If open_basedir is set it is disabled regardless where the file to be loaded resides. This might be too strict for shared envs.
Do you have open_basedir enabled?"
Indeed it is too strict for shared environments.
Is there any way to get around it?
I've tested it with the mysql lib (php5-mysql) as well as the mysqlnd drivers and both do the same.

thanks

Walter
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 23 18:01:55 2014 UTC