Cannot rake db:create:all -- Couldn't create database for {"encoding"=>"utf8", "username"=>"root", "adapter"=>"mysql"

Question

I'm trying to get a rails app up and running on my computer, and I'm having issues with creating the databases. I've properly installed/setup rails, mysql and have installed the mysql 2.8.1 gem (I verified this with gem list).

So now, I'm trying to run 'rake db:create:all' and I'm getting the following error:

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

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

I'm currently running 5.5.10 MySQL Community Server (GPL) on Snow Leopard (10.6.6)

And here is what is in my database.yml file

development:
  adapter: mysql
  encoding: utf8
  database: pyo
  username: root
  password:
  socket: /tmp/mysql.sock
  host: localhost

test:
  adapter: mysql
  encoding: utf8
  database: pyo_test
  username: root
  password:
  socket: /tmp/mysql.sock
  host: localhost

I notice how the end of the error says "charset: utf8, collation: utf8_unicode_ci (if you set the charset manually, make sure you have a matching collation)" -- Is that the issue? And if so, how do I fix it?

I've been stuck on this thing for hours and can't find anything that helps on Google. So any help at this point would be much appreciated.

Thanks!!

Solution 1

So, after about 3 or 4 days of googling and trying so many different things, I stumbled on this somehow:

http://geryit.com/blog/2011/01/installing-mysql-with-rails-on-mac-os-x-snow-leopard/

And guess what? IT WORKS! PERFECTLY. Which leads me to believe that the problem lies within MySQL 5.5.10. Something about it will not just play nice with my ruby/rails environment. However, once I followed the instructions on the link above (which include uninstalling mysql and re-installing 5.1), my app now runs perfectly on my local machine.

Hope this helps someone!

Solution 2

I know this thread is old but being a IT guy who believes in keeping good solid documentation in one place here goes:

The problem actually stems from your "config/database.yml" file. You are pointing ROR to the wrong location for the MySQL Socket.

I ran into this problem and what it turned out to be is the gem was built on a mac system and the developer did not account for other operating systems by putting his "mysql.sock" file in the "/tmp" directory rather than the "/var/run/mysqld/mysqld.sock" .

So his "database.yml" file looked like:

development: adapter: mysql2 #encoding: utf8 database: somedatabase pool: 5 username: someusername password: somepassword socket: /tmp/mysql.sock

Because I was on a Ubuntu System I had to change mine to the following:

development: adapter: mysql2 #encoding: utf8 database: somedatabase pool: 5 username: someusername password: somepassword socket: /var/run/mysqld/mysqld.sock

Yes the error can be misleading however it has nothing to do with your "encoding" or whether you are using localhost or 127.0.0.1 (At least in linux/UNIX systems these translate to the same thing and are synonymous)

Begin Edit

Also it is not good to have encoding commented out like I do above. So I am providing the modification here for reference.

development: adapter: mysql2 encoding: utf8 database: somedatabase pool: 5 username: someusername password: somepassword socket: /var/run/mysqld/mysqld.sock

End Edit

Solution 3

I was getting the same issue with the mysql2 gem. Then I found my MySQL is listening in 'localhost' only and not '127.0.0.1', so I changed it to 'localhost' and now I don't get that issue anymore. Everything works now.

Solution 4

think I had something like this...fixed by adding this to database.yml:

host: 127.0.0.1

Or in your case, changing it from localhost.

View more solutions

Comments

[Bryan]

Came across a similar error message today and this method worked for me, thanks.

[Ndeto]

In my case, if you are switching from Ubuntu to Mac. Goes without saying but here's to a quick solution socket: /tmp/mysql.sock