CODEWITHSUNDEEP
phpmysqlyiiload-data-infile
Bhavin Solanki
Bhavin Solanki

Reputation: 1364

LOAD DATA LOCAL INFILE not working when mysql to a remote server

I am working on Load data functionality.I have mainly two server, One contain MYsql and other is for web portal.

When I try to perform load local infile using below query it will give me "The used command is not allowed with this MySQL version" Error.

Query :  "LOAD DATA LOCAL INFILE '/var/www/html/btisms/contact_data/".$file_name."' REPLACE 
                        INTO TABLE `tbl_recipient`
                        FIELDS
                        TERMINATED BY ','
                        ENCLOSED BY '\"'
                        LINES
                        TERMINATED BY '\n'         
                        (`gr_grp_id`, `gr_cust_id`, `gr_name`, `gr_number`)
                        ".

Below steps already perform by me.

1) Add local-infile = 1 into my.cnf and [client] and [mysql] tag.

2) Allow Access for directory in apparmod.

3) Grant file permission to particular user.

4) MySQL Version : 5.5.4 (CentOS-7)

Can any one suggest me if anything is missing.

It's working when i tried to perform this functionality when mysql and web server are on same server.

Upvotes: 1

Views: 1895

Answers (2)

Bhavin Solanki
Bhavin Solanki

Reputation: 1364

The issue regarding connection in PHP that it may use LOCAL INFILE

Using mysql:

mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);

Using mysqli:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

Upvotes: 0

Evhz
Evhz

Reputation: 9284

You are trying to load a local file.

In case you run in another server (remote or not), you must ensure that the file is located in the same folder location you use in the query.

If the file is not present, the mysql will not do any action or throw error.

In order to save problems developing Mysql, I recommend you to take a solution like the following:

$handle = fopen("your_file", "r");
$con = mysqli_connect($db_host, $db_user, $db_pass, 'your_db_name');
$niceArr = array();

if ($handle) {
    while (($line = fgets($handle)) !== false) {
        // process the line read.
        $lineArr = explode(",", trim($line) );

        foreach($lineArr as $line ) {
            // Remove ""
            $niceArr[] = trim($line, '"');
        }

        // should check if isset($niceArr[ x ])
        $q = "insert into tbl_recipient SET ".
            "gr_grp_id = '$niceArr[0]',".
            "gr_cust_id = '$niceArr[1]',".
            "gr_name = '$niceArr[2]',".
            "remoteaddr = '$niceArr[3]',".
            "gr_number = '$niceArr[4]'";

        $res = mysqli_query( $con, $q );
        if( !empty( mysqli_error($con) ) ) {
            throw new Exception("Error writing line in db: $db_host");
        }

    }
    fclose($handle);
    mysqli_close($con);

} else {
    // error opening the file.
}

This way you can:

  • track errors about the file location,
  • track errors about non properly formatted lines, and finally
  • mysql errors separately.

This will make your life much more easier.

Upvotes: 2

Related Questions