Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Resolve LOAD DATA INFILE Error : "The used command is not allowed with this MySQL version"

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 558
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: