Geek Answers Handbook

Mysql_connect with the --local-infile option

It is not possible to load data from a downloaded (local) file since mysql was updated (current version: server version: 5.5.44-0 + deb8u1 (Debian)), implied files:

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, the files can be downloaded:

 mysql --local-infile -p

So my question is: can I set this parameter in the dbconnection.php file, I have tried many ways many times, but I do not have time, I read the messages about my.cnf configuration and some other materials, but nothing works, any suggestion ?

thanks

UPDATE: I was absent, changing the code of the entire network to mysqli, ufff !!, well following the recommendations from the answers below. I made the following code, but did not succeed, I still get the message: "The command used is not allowed using this version of MySQL." The implied files are as follows:

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?

+5
source share
4 answers

Ok, finally I found a 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));

Hope this helps.

0
source

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

 local-infile=1

Restart the 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 it and see if it works. Link options mysqli options

+3
source

LOCAL INFILE is a mechanism by which a database server can request more or less a file from a database client (= PHP server). If you cannot fully trust your server and network, this can be dangerous. Therefore, LOCAL INFILE must be allowed both on the server (like you) and on the database client.

You are using an outdated and unsupported mysql PHP extension. This extension does not support flag setting. You must 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); ?>
0
source

As mysql_connect () has been deprecated since PHP 5.5.0 (and will be removed in the future), this does not exclude that the problem comes from your web server (Apache?), And not your version of SQL.

Another thing that can cause this problem is the contents of your file, which ultimately contains the defreacted command (which, in your opinion, is an error message).

-1
source

More articles:


All Articles