This article provides the solution for the issue with LOAD DATA INFILE statement. PHP provides a lots of options to import data from text files. I faced this issue while I was trying to import data from a text file (data.txt) into mysql table using "LOAD DATA INFILE" statement with MySQLi Extension, but getting an error " The used command is not allowed with this MySQL version ".
Note : I was getting this error with MySQL 5.5 and Ubuntu 14.04.
Below is the data(data.txt) and PHP code used for importing data from text file, The purpose of given code is to import data from data.text file into Users table under the fileds (Name, Email, Password).
data.txt
Amit amit@test.com 2e75636563fsda7f4b4085c115
Rahul amit@test.com 6s75636563fsda7f4b4085c124
Ravi amit@test.com 8f75636563fsda7f4b40uh4524
Ram ram@yahoo.com 2e756hg893fsda7f4b4085ct25
test.php
<?php
//create connection link
$con = mysqli_connect("localhost","root","123456","test");
//if connection failed
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query = "CREATE TABLE IF NOT EXISTS `Users` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(100) NOT NULL,
`Email` varchar(150) NOT NULL,
`Password` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
//if table created successfully
if(mysqli_query($con,$query)){
$insert_query = "LOAD DATA LOCAL INFILE 'path of text file'
INTO TABLE tbl_Users
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\\n'(Name,Email,Password);";
if (mysqli_query($con, $insert_query)) {
echo "Affected rows: " . mysqli_affected_rows($con);
} else {
echo "Error: " . $insert_query . "<br>" . mysqli_error($con);
}
} else {
echo "Error in table creation";
}
?>
Output : Error : LOAD DATA LOCAL INFILE '/var/www/html/data.txt' INTO TABLE Users FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n'(Name,Email,Password);
The used command is not allowed with this MySQL version
After searching a lot on google, I found this Security Issues with LOAD DATA LOCAL. By default MYSQL_OPT_LOCAL_INFILE option is set to 0(false).
Solution : The solution for above defined problem is to set MYSQL_OPT_LOCAL_INFILE true using mysqli_options.
Change the $con = mysqli_connect("localhost","root","123456","test"); line with code given below :
$con = mysqli_init();
mysqli_options($con, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($con, "localhost","root","123456", "test");
Output : Affected rows: 4
0 Comment(s)