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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With