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:
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.)
Verify the socket is correct. I don't really understand sockets, but I can plainly see it at /tmp/mysql.sock.
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.
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
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!
