Enabling local-infile for loading data into remote mysql from rails

Question

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 ?

Banbury

Answer 1

add this to database.yml:

local_infile: true

Doglike

I love you! Been looking everywhere for this – Nonanonage

yeah, that's not the most often used feature ) enjoy ;) – Doglike

where i can find this file... i am using mysql server 5.6 – Savina

it's in the config directory of your Rails project, you should add the line to your environment config (ie. development) – Doglike

One of the most under-rated answers on this site. – Hamner

Answer 2

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

Unfinished

Thanks for the reply.It works on my dev machine. I just have a problem on the server where the app is deployed. I need to enable local-infile for mysql client on the server.I have enabled it by making the entry in my.cnf as sated above.However,rails is not aware of that setting. So how can i enable it for my rails code as well ? – Banbury

you are right. the difference is local vs remote. The same user behaves differently when accessed via localhost or remote. Check the host value for this user. It should be % instead of localhost. – Unfinished