4

I'm starting a new rails 3 app and when I run rake db:create it asks for my root mysql password then errors out saying:

Mysql2::Error: Access denied for user 'arcsite_mysql'@'localhost' to database 'arcsite_mysql_test': CREATE DATABASE arcsite_mysql_test DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

If I log into mysql on the command line and run show databases; I see this:

arcsite_mysql_development

And if I run SHOW GRANTS FOR arcsite_mysql@localhost I see this:

GRANT ALL PRIVILEGES ON `arcsite_mysql_development`.* TO 'arcsite_mysql'@'localhost' WITH GRANT OPTION

My database.yml:

# MySQL.  Versions 4.1 and 5.0 are recommended.
#
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: arcsite_mysql_development
  pool: 5
  username: arcsite_mysql
  password: password
  host: localhost

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: arcsite_mysql_test
  pool: 5
  username: arcsite_mysql
  password: password
  host: localhost

So, for some reason, only the development database is being created.

Versions:

Rails 3.2.13

MySQL: Server version: 5.6.10 Source distribution

mysql2: mysql2-0.3.11

EDIT

If I manually grant all the privileges to the arcsite_mysql user, I can create the tables. But is it expected that Rails would create the user and table for development but not for the test environment? The arcsite_mysql user is created by rails when I first ran the rake db:create command.

EDIT 2

Point 4 here: https://rails.lighthouseapp.com/projects/8994/tickets/1459-patch-better-dbcreate-for-mysql-do-not-assume-root-user

mentions something about "well known" process that involves mysql user creation.

Improve this question
5
  • 1
    does the user arcsite_mysql exist in mysql? I do not think it should be asking you for a root password if this user exists and has proper permissions. Maybe something like this will do the trick if not: GRANT ALL ON *.* TO 'arcsite_mysql'@'localhost' IDENTIFIED BY 'password'; Commented Apr 23, 2013 at 17:51
  • I thought Rails took care of user creation in mysql? The user didn't exist before I ran the rake db:create command. Commented Apr 23, 2013 at 17:54
  • Updated the question a little. The user creation behavior seems a little funky Commented Apr 23, 2013 at 18:04
  • i've typically gone about this by creating the DB user myself first, granting all needed access rights, and then specifying that user / pwd in the database.yml file. Commented Apr 23, 2013 at 18:27
  • added an answer for attention and formatting, I am not really sure how the first DB creation worked at all if the user did not exists usually you will get something along the lines of: FATAL: role "user" does not exist my guess would be that maybe it was made/existed before but with incorrect permissions? The command i posted only updates an existing user and does not create one Commented Apr 23, 2013 at 18:27

2 Answers 2

Reset to default
5

You need to grant access to this user in MySQL:

GRANT ALL ON *.* TO 'arcsite_mysql'@'localhost' IDENTIFIED BY 'password';

Rails will not create a user in MySQL it only uses a user that exists which is why when generating a database.yml you get something like:

development:
  adapter: mysql2
  encoding: utf8
  database: blog_development
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

Rails assumes the root user exists and has proper permissions in MySQL (which since root has everything it does) and if not you must create it (if using MySQL this will prompt you for the root password to create the user if it does not exst) or change it to one that does. For reference: http://guides.rubyonrails.org/getting_started.html#configuring-a-database section 3.3.2

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The only correction I would make is that rails WILL create a user (although with some funky side effects). Probably best for me to create the DB and user myself. Just wanted people to be aware of this "magic" in case they run into problems. Here's a conversation related to database creation: rails.lighthouseapp.com/projects/8994/tickets/…. Step 4 mentions something about "well known" process that involves mysql user creation
Huh, I did not know that! I use postgresql myself which to my knowledge does not have the same functionality in rails. I will update my answer. Here is a link to the current commit in rails for those interested: github.com/rails/rails/blob/master/activerecord/lib/… since the other is a bit dated It is strange though... I am curious why it failed now.
0

if you're looking to have your test database created, try

rake db:test:prepare
Improve this answer

2 Comments

per mr. truluck's comment above, looks like arcsite_mysql only has permissions on the one database. you need to grant that user ALL permissions across the server instance.
Updated the question a little. The user creation behavior seems a little funky

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.