5

Can not load data from uploaded (local) file since upgrade of mysql (current version: Server version: 5.5.44-0+deb8u1 (Debian)), files implied are:

dbconnection.php

<?php
$server = "localhost";
$user = "TheUser";
$pass = "ThePass";
$db_name = "DbName";
$link = mysql_connect($server, $user, $pass);
mysql_select_db($db_name);
mysql_set_charset('utf8', $link);
?>

send2db.php

<?php
include 'dbconnection.php';
mysql_select_db("DbName") or die(mysql_error());
$query = "LOAD DATA LOCAL INFILE '$file' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' ";
mysql_query($query) or die(mysql_error());
?>

The error says:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Inside mysql:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

But if I access mysql this way, files can be loaded:

mysql --local-infile -p

So my question is, can I set this option in the dbconnection.php file, I've tried many ways already with no success, I've been reading posts about my.cnf configuration and some other stuffs but nothing works for me, any suggestion?

Thanks

UPDATE: I've been away changing the code of the entire web to mysqli, ufff!!, well following the suggestions from the answers bellow I did the next code but no success, I still get the message: "The used command is not allowed with this MySQL version". Implied files are next:

acessdb.php

<?php
$link = new mysqli($server, $user, $pass, $dbname);
?>

send2db.php

<?php include 'acessdb.php';
$link->options(MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_query($link, "LOAD DATA LOCAL INFILE 'upfiles/file.csv' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'") or die(mysqli_error($link));
$link->options(MYSQLI_OPT_LOCAL_INFILE, false);
?>

Any suggestions?

Andrés Chandía
  • 999
  • 1
  • 16
  • 32
  • What does the file path look like? Are you using '/' or '\'? This actually makes a difference. – Hatem Jaber Sep 07 '15 at 12:19
  • 'upfiles/$file', but as I mentioned with update this stopped to work, i. e., it was working with previous versions of mysql – Andrés Chandía Sep 07 '15 at 13:26
  • It seems that as if the update disabled the server/client settings in your mysql configuration to not allow local-infile. I just read that last line where you said that it works in the terminal. I'm going to provide an answer below, please mark it as the right answer. – Hatem Jaber Sep 07 '15 at 13:39

5 Answers5

5

Set the option in my.cnf (or mysql configuration file on your system):

local-infile=1

Restart MySQL service and this should fix the problem for you.

UPDATE Another option to try with PHP

$conn = mysqli_init();
$conn->options(MYSQLI_OPT_LOCAL_INFILE, true);

Try that and see if that works. Options link mysqli options

Hatem Jaber
  • 2,341
  • 2
  • 22
  • 38
  • I already tried this: [mysqld] local-infile=1 / [mysql] local-infile=1 or [mysqld] local-infile=On / [mysql] local-infile=On and always the result is: The used command is not allowed with this MySQL version – Andrés Chandía Sep 07 '15 at 13:49
  • And always that I add these commands on restarting mysql I get these lines: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) – Andrés Chandía Sep 07 '15 at 13:51
  • Did that work? Was that the right answer? If it was please make sure to mark it as the right answer so that others can benefit as well. – Hatem Jaber Sep 07 '15 at 13:57
  • I guess I'm doing it right, my new dbconnection.php file looks like this now: `$link = mysql_connect($server, $user, $pass); $link->options(MYSQLI_OPT_LOCAL_INFILE, true); mysql_select_db($db_name); mysql_set_charset('utf8', $link);` but now I get this error in apache2/error.log: `PHP Fatal error: Call to a member function options() on resource in dbconnection.php` – Andrés Chandía Sep 07 '15 at 13:59
  • just a piece of advice, and keep in mind my knowledge of PHP is limited. If you are using 4.1.3 or later, it is recommended that you use the mysqli extension. You will have to modify some of your code, but this is what is recommended. – Hatem Jaber Sep 07 '15 at 14:01
  • Ok, I did it, as I comment on the update of my question – Andrés Chandía Sep 10 '15 at 12:59
2

Ok, finally I found the way, here is the working code:

file: connectdb.php

$link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($link, $server, $user, $pass, $dbname);

file: send2db.php

mysqli_query($link, "LOAD DATA LOCAL INFILE 'upfiles/file.csv' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'") or die(mysqli_error($link));

I hope it helps.

Andrés Chandía
  • 999
  • 1
  • 16
  • 32
0

LOCAL INFILE is a mechanism by which the database server can request more or less any file from the database client (= the PHP server) If you can't fully trust your server and network this can be dangerous. Therefore LOCAL INFILE has to be allowed both on the server (as you did) as on the database client.

You are using the old outdated and not maintained mysql extension of PHP. That extension doesn't support setting the flag. You should switch to mysqli which has the MYSQLI_OPT_LOCAL_INFILE option to enable this mode.

<?php
$m = new mysqli(...);
$m->option(MYSQLI_OPT_LOCAL_INFILE, true);
$m->query("LOAD DATA LOCAL INFILE ....");
$m->option(MYSQLI_OPT_LOCAL_INFILE, false);
?>
johannes
  • 15,807
  • 3
  • 44
  • 57
0

Look through the comments above and create your checklist of sorts as follows:

  1. Can I run "Load Data Local Infile" via mysql command line? If no, make sure this is enable by checking your global variable as discussed above.
  2. Can I run a basic mysql script via my php code? If yes, then replace with the "Load Data Local Infile" script show about (same as what was used in step 1 via mysql).
  3. Check database to determine if the load worked. If yes, "great job". If no, you need to turn on this Load feature via PHP. Look at the instructions above where you add $xxx->options(MYSQL_OPT_LOCAL_INFILE, true); this should be followed by a command to turn this off.

I struggled through this same issue for 5+hours. These are the summary steps that would have helped me troubleshoot and solve this issue faster. I would try these steps before messing with config files, which could lead to unintended consequences. Thanks for everyone's contributions.

Rudedog
  • 1
-1

As mysql_connect() is deprecated since PHP 5.5.0 (and will be deleted in the future), it's not impossible the issue comes from your web server (Apache ?) and not your SQL version.

Another thing that can cause this issue is the content of your file which eventually contains depreacted command (that's what the error message seems to tell you).

Pauloscorps
  • 564
  • 1
  • 6
  • 16