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

Question

This is my full script.

Am trying to make a rake task which collects data from the files in the directory and loads them in the mysql.

I fixed the local-infile = 1, Nothing works. It just gives me the error

namespace :db do
  namespace :load do
    desc "Load Properties into DB"
    task :properties => :environment do
      Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES
      @files = Dir.entries("db/property_website_scripts/")
      connection = ActiveRecord::Base.connection()

      for file in @files
        next if file == "." || file == ".."
        sql = "LOAD DATA LOCAL INFILE '#{Rails.root}/db/property_website_scripts/#{file}'
               INTO TABLE properties
               FIELDS TERMINATED BY '|'
               LINES TERMINATED BY '\r\n'
               (property_type,property_for,city,state,country......);"

        connection.execute(sql)
      end

      #updating created at and updated at
      Property.update_all({:created_at => Time.now, :updated_at => Time.now}, "created_at IS NULL")
    end
  end
end

Monologue

possible dup of #10762739 – Guileless

I already gone through that post before – Monologue

In that post, mysql is accessed directly, this is a rakefile. There's no problem when am direclty using in the mysql, problem comes when i use it in the rakefile. – Monologue

You're executing the SQL in plain, which is basically the same as chucking it into the MySQL console. Besides, you get the error from MySQL and not from ruby nor from rails etc, so it doesn't matter that "it is a rakefile". – Guileless

I know that part. I mentioned rake file because, i getting problem from rakefile but not when chucking in the MySQL console, am expecting for the soution for the rakefile to work. – Monologue

I am getting the same error. The sql query works in sql console but not in rails console. Any solution on this problem? – Woodman

Answer 1

The solution from this post worked for me : Enabling local-infile for loading data into remote mysql from rails

add this to database.yml

local_infile: true

Constrained

Answer 2

I'm getting the same error with version 0.3.11 of the mysql2 gem all of a sudden. Since I've been using that version for a while, I assume it's some sort of dependency issue with another gem. Try upgrading to version 0.3.12b5, that fixed it for me.

Utgardloki

My colleague has the same problem, updating to 0.3.12b6 and updating the mysql dev libs did not solve it. :-( – Sublimity