I am attempting to load data from a CSV file into MySQL during a migration. The query works fine from MySQL's query tool, but fails from within a migration. In both scenarios, I am connecting to MySQL as root@localhost.
Here is the raw query:
LOAD DATA LOCAL INFILE '/home/mrichman/rails_apps/myproject/db/migrate/geo_data.csv' INTO TABLE geo_data (zip_code, latitude, longitude, city, state, county);
Here is query in the migration:
csv_file = "#{RAILS_ROOT}/db/migrate/geo_data.csv"
fields = '(zip_code, latitude, longitude, city, state, county)'
execute "LOAD DATA LOCAL INFILE '#{csv_file}' INTO TABLE geo_data " +
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"\"\"\" " +
"LINES TERMINATED BY '\n' " + fields
This is the error produced by MySQL when calling rake db:migrate:
== 7 CreateGeoData: migrating =================================================
-- create_table(:geo_data)
-> 0.0232s
-- add_index("geo_data", zip_code?, {:name=>"zip_code_optimization"})
-> 0.0200s
-- execute("LOAD DATA LOCAL INFILE '/home/mrichman/rails_apps/myproject/db/migrate/geo_data.csv' INTO TABLE geo_data FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"\"\"\" LINES TERMINATED BY '\n' (zip_code, latitude, longitude, city, state, county)")
rake aborted!
Mysql::Error: #42000The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/home/mrichman/rails_apps/myproject/db/migrate/geo_data.csv' INTO TABLE geo_data FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY '
' (zip_code, latitude, longitude, city, state, county)
(See full trace by running task with --trace)
And here is my database.yml:
development:
adapter: mysql
database: myproject_development
username: root
password: mypassword
host: localhost
socket: /var/run/mysqld/mysqld.sock
I have verified that local-infile=1 is set and my user is granted all right, but this should make no difference unless ActiveRecord connects to MySQL with unusual parameters.
