This project is archived and is in readonly mode.

#1459 ✓committed
Vladimir Dobriakov

Better db:create for mysql - do not assume root user

Reported by Vladimir Dobriakov | November 24th, 2008 @ 01:33 PM | in 2.x

The current mysql adapter implementation and especially the rake db:create implementation assume, that developers use the root account for working with their mysql server. I think, it is not a good thing. The second problem is, if the database creation fails, then it fails with a wrong error message - something with 'make sure you have a matching collation' instead of 'access denied'.

How it should work:

  1. a developer puts the (non-root) credentials for the new database into the database.yml like

    development: adapter: mysql database: myapp username: myapp password: xyz1148 host: 127.0.0.1 encoding: utf8

  2. run rake db:create

  3. there are not enough rights to create the new database, so rake asks for the root password
  4. run the well-known sequence CREATE DATABASE #{db_name}; GRANT ALL PRIVILEGES ON #{db_name}.* TO '#{user}'@'localhost' IDENTIFIED BY '#{password}' WITH GRANT OPTION;

The last sequence can be seen over and over again in all possible howtos and books. I think it is time to automate this.

If the interactive behaviour is not desired, than it can be replaced by 'failed' - 'rerun with additional parameter' loop. But probably you do not really want to provide the root password as a parameter on the command line.

The attached patch contains the implementation for the interactive scenario. Did not know, what is the common way to test such interactive stuff automatically; but at least extensively tested it manually.

Best Regards,

Vladimir Dobriakov AKA geekQ http://www.innoq.com/blog/vd

Comments and changes to this ticket

  • Pratik

    Pratik March 6th, 2009 @ 07:37 PM

    • Title changed from “[PATCH] better db:create for mysql - do not assume root user” to “Better db:create for mysql - do not assume root user”
  • Pratik

    Pratik March 6th, 2009 @ 07:50 PM

    • State changed from “new” to “wontfix”

    I am not sure it's the best idea to make this interactive. This is useful only in development machines and it's easy to just manually create the db if needed.

    Thanks !

  • Vladimir Dobriakov

    Vladimir Dobriakov March 7th, 2009 @ 01:41 PM

    The first problem is a misleading error message regarding charset and collation instead of "access denied".

    Regarding making the feature interactive - I had this concerns too. But what is the alternative?

    • keep the db:create feature non-working, especially for production where your root account (hopefully ;-) ) always has a non-empty password

    • provide the root password on the command line. The problem is the password is saved in the command history then

    • ask for the root password interactively when creating the database - always happens anyway, even with mysql

    Or did I overlooked something and there is a possibility for a secure password-less root account in MySQL? There is nothing like certificate-based ssh-like login in MySQL or is there?

    Could you outline, how do you deploy (production) or create (development) a new Rails application with a MySQL database.

    Thanks!

  • Pratik

    Pratik May 26th, 2009 @ 04:35 PM

    • Assigned user set to “Michael Koziarski”
  • Michael Koziarski

    Michael Koziarski May 26th, 2009 @ 04:36 PM

    • State changed from “wontfix” to “stale”
  • Vladimir Dobriakov

    Vladimir Dobriakov May 29th, 2009 @ 08:59 AM

    Checked against current 2.3-stable branch - applies cleanly.

    Checked with a sample application:

    rails app1
    cd app1
    vi config/database.yml
    

    Put following:

    production:
      adapter: mysql
      database: app1
      username: app1identity
      password: secret1
      host: 127.0.0.1
      encoding: utf8
    

    Now run the rake task

    RAILS_ENV=production rake db:create
    

    The old implementation returns:

    Couldn't create database for {"encoding"=>"utf8", "username"=>"app1identity", "adapter"=>"mysql", "database"=>"app1", "host"=>"127.0.0.1", "password"=>"secret1"}, charset: utf8, collation: utf8_general_ci (if you set the charset manually, make sure you have a matching collation)

    Please also note the misleading error message.

    The new one:

    Access denied for user 'app1identity'@'localhost' (using password: YES).
    Please provide the root password for your mysql installation
    >
    

    New database is created.

  • Repository

    Repository June 1st, 2009 @ 01:09 AM

    • State changed from “stale” to “committed”

    (from [64756e8f718c90a17664f2d35993696d8bf0f81e]) better db:create for mysql - do not assume root user

    Signed-off-by: Michael Koziarski michael@koziarski.com
    [#1459 state:committed] http://github.com/rails/rails/commit/64756e8f718c90a17664f2d3599369...

  • bingbing
  • csnk

    csnk May 18th, 2011 @ 08:31 AM

    We are the professional clothing manufacturer and clothing supplier, so we manufacture kinds of custom clothing manufacturer. welcome you to come to our china clothing manufacturer and clothing factory.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Referenced by

Pages