LOAD DATA LOCAL INFILE causes an error packet error using mysql2 gem

Question

I am trying to issue a LOAD DATA LOCAL INFILE query to load some CSV data into a table using the mysql2 gem (0.3.11) under rails 3.1.1:

class Foo < ActiveRecord::Base def self.load_csv query = "LOAD DATA LOCAL INFILE 'test/foo.csv' REPLACE INTO TABLE foos LINES TERMINATED BY '\n' (title)" ActiveRecord::Base.connection.execute(query) end end 

(This is an example application for reproducing the error of this github problem ). This results in a crash in OS X (Lion) with the following error:

 Mysql2::Error: Malformed packet: LOAD DATA LOCAL INFILE 'test/foo.csv' REPLACE INTO TABLE foos LINES TERMINATED BY ' ' (title) 

Local information is allowed on the server:

 mysql> show variables where variable_name like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | ON | +---------------+-------+ 

and on the client using this directive in application.rb:

 Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES 

The same LOAD statement works great with the MySQL client. Changing the way a DB connects from a socket to TCP / IP does not matter. MySql is installed via homebrew, and the version

 mysql Ver 14.14 Distrib 5.5.15, for osx10.7 (i386) using readline 5.1 

I do NOT get this error when working with the same code under Linux. It also works if I omit the LOCAL modifier, but this is not an option, since the file is actually local in production and a remote database server. This has nothing to do with file permissions, as in this question.

It drives me crazy, any ideas are greatly appreciated.

Answer 1

It turns out that this is simply solved by reinstalling gem mysql2. I think I installed it with the version of MySQL compiled from the source code, and then later switched to MySQL installed via brew, which led to incompatibility in the client code used by the stone. This explains why he worked with the command line client, but not when using the gem.

 gem uninstall mysql2 gem install mysql2 

Doh ...

Answer 2

The problem should be fixed for mysql2 v> 0.3.12b4

This did not work for me using the LOCAL_FILE flag, but adding :local_infile => true to the parameters did the trick

 1.9.3p194 :011 > a = Mysql2::Client.new(:username=>'root', :host=>'localhost', :password=>'', :database=>'bhl_indexer', :local_infile => true) 

https://github.com/brianmario/mysql2/issues/293