Enabling local-infile for loading data into remote

问题:

Im using connection_ninja (https://github.com/cherring/connection_ninja) to connect to a remote mysql database from my rails applicaion. I have a method in my model which loads a csv file using 'load data local infile..' from the server running my rails app into the remote mysql db.

The code is as follows :

class Product < ActiveRecord::Base
  @conn = use_connection_ninja(:rl_op)
  self.table_name = 'RlProduct'

  def self.update(file_path)
    sql = "LOAD DATA LOCAL INFILE '#{file_path}'
           INTO TABLE RlProduct
           FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
           LINES TERMINATED BY '\n'
           (name,price,productId)"               

    @conn.connection().execute(sql)
  end      
end

This is giving me the following error :

Mysql2::Error: The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE..

I have set local-infile=1 in [mysql] section of /etc/mysql/my.cnf of the server running my rails app. And that allows me to import the data into remote db if i directly log in to mysql on the server and run the load data local.. command there.

How can i set local-infile=1 for my rails code as well ?

回答1:

add this to database.yml:

local_infile: true

回答2:

The user that is running this query should have FILE access on the remote server.