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 ?

score 23 · Accepted Answer · answered Jan 22 '14 at 21:35

23

add this to database.yml:

local_infile: true

answered Jan 22 '14 at 21:35

trushkevich

2,657
1
28
37

2

I love you! Been looking everywhere for this – Kevin Feb 09 '14 at 19:51
yeah, that's not the most often used feature ) enjoy ;) – trushkevich Feb 10 '14 at 08:03
where i can find this file... i am using mysql server 5.6 – saikiran May 12 '14 at 21:35
it's in the `config` directory of your Rails project, you should add the line to your environment config (ie. development) – trushkevich May 12 '14 at 23:43
One of the most under-rated answers on this site. – qu1j0t3 Mar 20 '18 at 22:10

score 0 · Answer 2 · answered Jan 21 '14 at 11:23

0

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

answered Jan 21 '14 at 11:23

Raj

22,346
14
99
142

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 ? – raza.sayed Jan 21 '14 at 11:28
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. – Raj Jan 21 '14 at 11:31

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

2 Answers2

Linked