Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql_connect with --local-infile parameter

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?

like image 821
Andrés Chandía Avatar asked Sep 07 '15 12:09

Andrés Chandía


1 Answers

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

like image 152
Hatem Jaber Avatar answered Oct 03 '22 14:10

Hatem Jaber