2

I'm a new Rails Developer, and I'm working on a legacy Rails app. Whenever I run the rake db:create command, I get an error that the database couldn't be created. I have found many StackOverflow questions related to this, but in troubleshooting nearly all permutations of solutions, I couldn't resolve the issue.

I created the three Dbs (dev, prod, test), created the user with all access privileges to these dbs, and ran rake db:create.

I'm running Mac OS X Lion, MySQL 5.5.28, Rails 2.3.5, Ruby 1.8.7. Here are my settings 

development:
  adapter: mysql
  encoding: utf8
  database: adva_development
  username: adva
  password: ****
  host: localhost
  socket: /tmp/mysql.sock

Here's the error:

Couldn't create database for {"adapter"=>"mysql", "username"=>"adva", "host"=>"localhost", "encoding"=>"utf8", "database"=>"adva_development", "socket"=>"/tmp/mysql.sock", "password"=>"****"}, charset: utf8, collation: utf8_unicode_ci (if you set the charset manually, make sure you have a matching collation)

I have done the following troubleshooting:

  1. Verified user and password are correct, and the user has access to the DB. (Double checked user access with SELECT * FROM mysql.db WHERE Db = 'adva_development' \G; User has all privileges.)

  2. Verify the socket is correct. I don't really understand sockets, but I can plainly see it at /tmp/mysql.sock.

  3. Checked collation and character set. I found out I had created the DB in latin charset and collation, so I recreated them. I ran show variables like "collation_database"; and show variables like "character_set_database"; and came back with utf8 and utf8_unicode_ci respectively.

  4. I followed the instructions in this question. After uninstalling mysql gem, I ran the following but came up with the same error:

    gem install --no-rdoc --no-ri mysql -- --with-mysql-dir=/usr/local/mysql-5.5.28-osx10.6-x86_64/bin --with-mysql-config=/usr/local/mysql-5.5.28-osx10.6-x86_64/bin/mysql_config

  5. Following Matt's suggestion, here's what a rake --trace db:create reveals:

    ** Invoke db:create (first_time)
** Invoke db:load_config (first_time)
** Invoke rails_env (first_time)
** Execute rails_env
** Execute db:load_config
** Execute db:create
Couldn't create database for {"database"=>"adva_development", "adapter"=>"mysql", "host"=>"127.0.0.1", "password"=>"woof2adva", "username"=>"adva", "encoding"=>"utf8"}, charset: utf8, collation: utf8_unicode_ci (if you set the charset manually, make sure you have a matching collation)

After 3 days and six or seven hours, I have pretty much run out of options. I tried various random things, like replacing localhost with 127.0.0.1 to no avail.

Could there be something wrong related to my specific environment? Mac OS X Lion + MySQL 5.5.28? I plan on trying on setting up everything in a Linux environment.

Thanks!

Community
  • 1
  • 1
ridicter
  • 69
  • 7
  • 1
    Can you try to run `rake --trace db:create`? It may give you some more details on the error. – Matt Nov 11 '12 at 22:01
  • "Verified user and password are correct, and the user has access to the DB" - you mean the user has access to *create* the *non-existent* database? Wording it like this, it sounds like it's not creating it because it already exists? – Woahdae Nov 11 '12 at 22:03
  • 2
    Also, at this point, why not just create it yourself and get on with your life? Really, all that matters is that you can do things like `rake db:schema:load` etc. – Woahdae Nov 11 '12 at 22:05
  • @woahdae rake db:schema:load produces another set of errors. Also, Matt, I put the trace results in the original question. – ridicter Nov 11 '12 at 22:18
  • Have you tried checking if the sockets are the same for rails client and mysql server? See this question: http://stackoverflow.com/questions/4251009/mysql-hell-cant-connect-to-database-tmp-mysql-sock – Matt Nov 11 '12 at 22:24
  • I don't mean to be too simplistic, but I have to ask the "is it plugged in?" question: what happens when you `mysql -u root` and `show databases;`, is adva_development in the list? – Woahdae Nov 11 '12 at 22:26
  • @woahdae When I log in to mysql and type `show databases;`, I get all the proper databases listed. – ridicter Nov 11 '12 at 22:58
  • Sorry, I'll be more specific (again, forgive my if this is known territory). First, type `mysql -u root`. You should see something like `mysql>`. Now, type `show databases;`. This should output a table with the list of existing databases – Woahdae Nov 11 '12 at 22:58
  • @woahdae Ah, I edited at the same time you did, see above. Thanks. – ridicter Nov 11 '12 at 22:59
  • Is `adva_development` in that list? If so, `rake db:create` will fail because it already exists (imagine typing this in production by accident, you would *not* want that to go ahead and successfully overwrite your db) – Woahdae Nov 11 '12 at 23:00
  • @woahdae That will be pretty embarrassing if that's the issue. I somehow figured I would get a different error message if that was the issue. Here's the error I encounter when I run rake db:schema:load. `rake aborted! no such file to load -- /Users/richter/wrinkledog/adva/config/../vendor/adva/engines/adva_cms/boot` I guess I should search for this file and what it does... – ridicter Nov 11 '12 at 23:11
  • @woahdae I deleted adva_development and ran rake db:create, I still get the same error ("couldn't create database")! I actually remember trying this before. Back to square 1. – ridicter Nov 12 '12 at 00:27
  • Did you install mysql gem – Amrit Dhungana Mar 11 '14 at 07:37

3 Answers3

0

You can try changing localhost to 127.0.0.1 and removing socket to use TCP/IP instead of a local socket.

Or try to verify if your database server uses the same socket as Rails:

mysqladmin variables | grep socket

If this doesn't help, then try to connect to the database server with pure Ruby and create a new database:

require 'rubygems'
require 'mysql'

begin
  db = Mysql.new('localhost', 'root', 'yourpassword')
  db.query('create database sample_database;');
rescue Mysql::Error => e
  puts e
ensure
  db.close unless db.nil?
end

and see if this runs. You should have mysql gem installed (gem install mysql)

Matt
  • 5,328
  • 2
  • 30
  • 43
  • I receive the exact same set of errors using `127.0.0.1` and the socket checks out. Here's the result: `| socket | /tmp/mysql.sock` – ridicter Nov 11 '12 at 22:48
  • Do I type this in irb? If so, I get `NameError: uninitialized constant Mysql::Error from (irb):7` – ridicter Nov 11 '12 at 23:23
  • Save it into a, say, `test.rb` file and run `ruby test.rb` – Matt Nov 11 '12 at 23:30
  • Same error: `test.rb:7: uninitialized constant Mysql::Error (NameError)` According to woahdae, I shouldn't even be running rake db:create, and rake db:schema:load should work. I'm contacting my employer about github access to more repositories, because I think the issue with rake db:schema:load might lie there. – ridicter Nov 11 '12 at 23:37
  • What he means is you should connect to your db server with some mysql client, maybe some graphical one, and create the database manually yourself. To run db:schema:load you need to have a db created. It has nothing to do with git. – Matt Nov 11 '12 at 23:39
  • Right, I already created the DB's and manually created them. rake db:schema:load doesn't work at the moment. – ridicter Nov 11 '12 at 23:42
  • Uh, ok. I updated my answer again - try installing the mysql gem, if you haven't already. That script works on my machine, connects to the db and creates that database. – Matt Nov 11 '12 at 23:45
  • If you run `rake --tasks` you'll see that db:schema:load just loads `db/schema.rb` file. It won't exist in a fresh app. – Matt Nov 11 '12 at 23:48
  • It's not a fresh app; it's a legacy app (as I wrote above). The schema file exists and has content. – ridicter Nov 11 '12 at 23:58
  • FYI, I deleted adva_development and ran rake db:create, I still get the same error ("couldn't create database")! I actually remember trying this before. So the original question holds. – ridicter Nov 12 '12 at 00:28
0

Make sure you running a more recent version of the mysql gem. I ran into this problem with version 2.8. Upgrading to 2.9 fixed the issue.

The Who
  • 6,552
  • 5
  • 36
  • 33
0

change 'localhost' into '127.0.0.1' (loop back address) , hope its works

Navin
  • 543
  • 8
  • 27