Mysql "LOAD DATA LOCAL INFILE" does not work after reconnect


> ActiveRecord::Base.connection.disconnect! > ActiveRecord::Base.verify_active_connections! > ActiveRecord::Base.connection.execute("LOAD DATA INFILE '/tmp/some_file.txt' INTO some_table")

Actual console output

ruby-1.8.6-p399 :003 > ActiveRecord::Base.connection.execute("LOAD DATA LOCAL INFILE '/tmp/test.infile' INTO TABLE users")
[Sat Jan 08 00:09:29 2011] (9990)   SQL (1.7ms)   LOAD DATA LOCAL INFILE '/tmp/test.infile' INTO TABLE users
 => nil 
ruby-1.8.6-p399 :004 > ActiveRecord::Base.connection.disconnect!
 => #<Mysql:0x104c6f890> 
ruby-1.8.6-p399 :005 > ActiveRecord::Base.verify_active_connections!
[Sat Jan 08 00:09:58 2011] (9990)   SQL (0.2ms)   SET SQL_AUTO_IS_NULL=0
 => {…connection stuff…} 
ruby-1.8.6-p399 :006 > ActiveRecord::Base.connection.execute("LOAD DATA LOCAL INFILE '/tmp/test.infile' INTO TABLE users")
[Sat Jan 08 00:10:00 2011] (9990)   SQL (0.0ms)   Mysql::Error: The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/Users/Ngan/Development/streamsend/ngan.infile' INTO TABLE users
ActiveRecord::StatementInvalid: Mysql::Error: The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/tmp/test.infile' INTO TABLE users
    from /Users/Ngan/.rvm/gems/ruby-1.8.6-p399@streamsend/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:in `log'
    from /Users/Ngan/.rvm/gems/ruby-1.8.6-p399@streamsend/gems/activerecord-2.3.8/lib/active_record/connection_adapters/mysql_adapter.rb:323:in `execute'
    from (irb):6

Is the --local-infile option commented on here http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html relevent?

...and probably this too: http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html

I've gone through all the MySQL documentation and made sure everything has permissions. You'll notice in my console output, the first I do the LOAD DATA LOCAL INFILE, I am able to do it just fine. It is only after the reconnect that it has the error.

BTW, I tested this further and it's only happening for LOAD DATA LOCAL INFILE, not LOAD DATA INFILE. The LOCAL is key.

Note at the bottom of http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html, the last comment details how to configure client and server to always allow LOAD DATA LOCAL INFILE. Yes LOCAL is key - it has to be enabled explicitly because otherwise it is a security risk to MySQL.

As for why it works initially, and not after a reconnect, I have no idea other than that the connect is clearly enabling it, whilst the reconnect is not. I'll leave that to someone more knowledgeable about ActiveRecord, MySQL AR adapter, MySQL gem (or is it mysql2 you are using), mysql client libraries, etc.

I'm using Rails 2.3.8, so it's on the mysql gem (not mysql2). I've also updated to the latest gem version of mysql, and still get the same problem.

Awesome! I confirmed that it fixed it for me as well. Any case someone can make a patch for this?

Jonathan, do update on how it goes with mysql2.

Thanks!

Yup, this also works with the mysql2 gem.

Is this happening for anyone else?

Here's a patch that I did in my Rails app to fix this:

module ActiveRecord
  module ConnectionAdapters
    class MysqlAdapter
    private
      def connect_with_local_infile
        @connection.options(Mysql::OPT_LOCAL_INFILE, 1)
        connect_without_local_infile
      end
      alias_method_chain :connect, :local_infile
    end
  end
end