Access denied during Rails db:migrate

software development

#1

I’m trying to migrate an existing Rails app and I’m having trouble with the db:migrate process. One of the migrations attempts to LOAD DATA, and fails with

#1045 - Access denied for user ‘[my_user]’@‘66.33.192.0/255.255.224.0’ (using password: YES)

I’ve checked [my_user]'s permissions, and it can do everything, at least according to PHPMyAdmin. What am I missing here?
Thanks for any suggestions!


#2

[quote]I’m trying to migrate an existing Rails app and I’m having trouble with the db:migrate process. One of the migrations attempts to LOAD DATA, and fails with

#1045 - Access denied for user ‘[my_user]’@‘66.33.192.0/255.255.224.0’ (using password: YES)[/quote]
That error message can be a little misleading, because it tends to make you think the problem is with the user’s credentials, when in fact the “access denied” could be for just a given operation (which I suspect you realize from your next statement).

You may not be missing anything at all, and it may be a true mystery, but I’m thinking it is more likely that phpMyAdmin is not telling the whole story. There are things that phpMyAdmin on DreamHost thinks it can do that it actually can’t, and I think that your migration process may be trying to use one of those “gotchas”.

For instance, from the DH wiki article on MySQL:

[quote]On Dreamhost shared accounts, some of the SQL commands listed in the MySQL documentation link below or in references such as the O’Reilly book Managing & Using MySQL by Reese, et al. are not available for scripting nor in phpMyAdmin.

Their functionality is available through the Dreamhost Control Panel described in the Creating a MySQL Database above.

Included are:

  • create database (works from shell on databases created in panel)
  • drop database (works from shell on databases created in panel)
  • grant
  • revoke [/quote]
    Of course, I’m just guessing here because I haven’t seen the queries your migration process employs, but you did ask for suggestions, and this is what comes immediately to my mind.

–rlparker
–DreamHost Tech Support


#3

Thanks for your reply. I apologize for not giving me more detail; I wasn’t sure what was relevant.
The reason I’m trying to use phpMyAdmin in the first place is because the LOAD DATA fails when I’m executing
rake db:migrate RAILS_ENV=production
to create my app’s database. The rake process fails with the exact same “access denied” error as phpMyAdmin. I thought maybe something was fishy about the permissions of the rake process, even though it’s running as [my_user] from the shell.
Here’s the contents of the migration that fails:

[code]class CreateGeoData < ActiveRecord::Migration
def self.up
create_table :geo_data do |t|
t.column :zip_code, :string
t.column :latitude, :float
t.column :longitude, :float
t.column :city, :string
t.column :state, :string
t.column :county, :string
t.column :type, :string
end
add_index “geo_data”, [“zip_code”], :name => “zip_code_optimization”

csv_file = "#{RAILS_ROOT}/db/migrate/geo_data.csv"
fields = ‘(zip_code, latitude, longitude, city, state, county)’

execute “LOAD DATA INFILE ‘#{csv_file}’ INTO TABLE geo_data FIELDS " +
“TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY “””” " +
"LINES TERMINATED BY ‘\n’ " + fields
end

def self.down
drop_table :geo_data
end
end
[/code]I hand-entered the LOAD DATA data with the appropriate substitutions into phpMyAdmin’s SQL tab and got the same error as the rake process.
In light of this further information, do you have further suggestions?
Thanks!