Enabling local access to load data into remote mysql from rails

Question

I am using connection_ninja ( https://github.com/cherring/connection_ninja ) to connect to a remote mysql database from my rails application. I have a method in my model that downloads a csv file using "load data local infile .." from the server where my rails application is running to 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 gives me the following error:

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

I set local-infile=1 in [mysql] in the /etc/mysql/my.cnf section of the server my rails application is running on. And this allows me to import data into a remote db if I directly enter mysql on the server and run the local load data command ..

How can I set local-infile = 1 for my rails code?

Answer 1

add this to database.yml :

 local_infile: true 

Answer 2

The user executing this request must have FILE access on the remote server.