Rails Ruby on Rails

Comments and changes to this ticket

• You flagged this item as spam.
  

  Ngan January 8th, 2011 @ 07:24 AM

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

• You flagged this item as spam.
  

  Ngan January 8th, 2011 @ 08:19 AM

  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
  

• You flagged this item as spam.
  

  Jonathan Monahan January 10th, 2011 @ 10:28 AM

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

• You flagged this item as spam.
  

  Jonathan Monahan January 10th, 2011 @ 10:48 AM

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

• You flagged this item as spam.
  

  Ngan January 10th, 2011 @ 10:27 PM

  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.

• You flagged this item as spam.
  

  Ngan January 10th, 2011 @ 10:28 PM

  • Title changed from “Cannot execute mysql "LOAD DATA INFILE" query after using verify_active_connections!” to “Cannot execute mysql "LOAD DATA LOCAL INFILE" query after using verify_active_connections!”

• You flagged this item as spam.
  

  Jonathan Monahan January 11th, 2011 @ 09:11 AM

  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.

• You flagged this item as spam.
  

  Ngan January 11th, 2011 @ 06:04 PM

  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.

• You flagged this item as spam.
  

  Jonathan Monahan January 13th, 2011 @ 01:29 PM

  • Tag changed from “rails_2_3_stable” to “mysql adapter, rails_2_3_stable”

  We are running Rails 2.2.1 with the mysql gem, but I believe I have fixed the problem by adding

  @connection.options(Mysql::OPT_LOCAL_INFILE, 1)
  

  in mysql_adapter.rb in connect at line 555 or anywhere before the call to @connection.real_connect.

  Note also #1797 which fixes the :reconnect option if you are running Rails < 2.3: basically you move the line that sets @connection.reconnect after the call to @connection.real_connect.

  We are in the middle of upgrading to mysql2 gem, so I will confirm that the change also works there.

• You flagged this item as spam.
  

  Ngan January 13th, 2011 @ 07:34 PM

  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!

• You flagged this item as spam.
  

  Ngan January 13th, 2011 @ 08:48 PM

  • Title changed from “Cannot execute mysql "LOAD DATA LOCAL INFILE" query after using verify_active_connections!” to “Mysql "LOAD DATA LOCAL INFILE" does not work after reconnect”

• You flagged this item as spam.
  

  Jonathan Monahan January 13th, 2011 @ 11:18 PM

  Yup, this also works with the mysql2 gem.

• You flagged this item as spam.
  

  Ngan February 11th, 2011 @ 11:15 PM

  Is this happening for anyone else?

• You flagged this item as spam.
  

  Ngan February 16th, 2011 @ 07:41 PM

  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