MySQL hardships. Getting this to run with your localhost rails configuration.

[Scott Haines]

I wanted to start a discussion on MySQL issues that people may have
when trying to setup a local version of their Rails application.

Common Pitfalls
1. You installed mysql through a 3rd party installer (MySQL for MacOSX
dmg, or port install mysql) and now Rails can't find your database.

You need to provide Rails with the path to MySQL's socket
(mysqld.sock). You can find this value by looking at the mysql.conf
file, which should be in init.d (linux if installed via yum), or added
automatically when you did an RPM or Binary install of mysql, just do
a search of your machine for it. I digress, the socket location is
important so Rails can connect to the DB server.

database.yml (/app_root/Configuration/database.yml)

Sample Configuration

development:
host: 127.0.0.1
adapter: mysql
database: railsclass
port: 3306
username: root
password:
socket: /var/run/mysqld/mysqld.sock


----

(*Note: socket: /tmp/mysqld.sock is the typical socket location,
depending on your security needs, or preferences this can be moved to
a location of your choosing.)

Please note above, you don't ever want to run MySQL as root, this
means 1 oops and your database has granted root level access to your
system. It is a best practice to create layers of security in your
applications, one way of doing that is to create different users who
can accomplish different tasks , eg. godmode, has full read, write, db
create privileges while average_system_user may only have read access
to the database.

Please take a look at MySQL installation notes at
http://dev.mysql.com/doc/refman/5.1/en/installing.html. You will see
the best practices for setting everything up from scratch, as well as
interesting optional preferences.

Good Luck,
Scott Haines

[Scott Haines]

Also,
You may need to specify the mysql gem explicitly and give your current
MySQL architecture type as a flag.

env ARCHFLAGS="-arch x86_64" /usr/local/ruby19/bin/gem install mysql
-- --with-mysql-config=/usr/local/mysql/bin/mysql_config

(*Note: /usr/local/ruby19/bin is a custom path to my ruby 1.9.2
installation, you can just as easily use "gem" rather than the full
path to rubygems install location if you only have one version
installed on your machine (and your not using RVM for ruby version
management)

Once you have MySQL installed and the mysql gem also installed, you
can test that it is available by going into your Rails Application
root /, and issuing a rake command to see if you can create the
database, or run the setup procedure from the command line.

>> rake db:setup --trace

If you don't get a MySQL::Error issue, than you can use mysql from
your Rails application.

If your interested in using MySQL directly, without the use of
ActiveRecord, here is a link to a great article on using MySQL
directly through the Ruby language. http://www.kitebird.com/articles/ruby-mysql.html

Cheers,
Scott Haines

> Please take a look at MySQL installation notes athttp://dev.mysql.com/doc/refman/5.1/en/installing.html. You will see

[Chung Yang]

I think I got MySQL via MacPorts up and running now and that Ruby
seems to be connecting to it ok. Though I am not sure if I got the
users and permission up correctly.

However, a few things changed from the instructions I found here and
on the web.

1. For Mac Snow Leopard MacOS 10.6.x, there seems to be a user ID
"_mysql" already on the system (for single user mode) So MySQL
created the db by default for user, "_mysql". Do we need to create
a separate user "mysql" and re-install MySQL/DB etc. for multi user
access?

2. When testing out rake1.9 after creating a directory structure
with rails3, I found that now the default "mysql" gem is not being
used. Instead in the now the Gemfile requires "mysql2". So I had to
grab the "mysql2" gem to make rake work. From researching on the web,
it seems "mysql2" is being used more now. For the class are we using
the gem "mysql" or "mysql2"?

3. I created a "database.yml", but I don't know where to find the
default one (if it existed) or where to put the new one in relations
to Rails directory structure.

Thanks,

Chung

[BrianT]

On Windows it looks like the socket will be:

/tmp/mysql.sock

[BrianT]

A lot of the advice on the web says that on Windows working with Rails
requires MySQL version 5.0 (5.0.67 is often mentioned). I was having
problems with rake db:migrations blowing up, so I tried to revert to
the earlier version.

Be careful if you try installing 5.0.67...I uninstalled 5.1.52 and
then tried to install 5.0.67, but the 5.1.52 uninstall does not remove
the MySQL windows service. So when I tried to use the configuration
tool for 5.0.67 it cannot create the MySQL windows service because
it's still there. So now I have no working MySQL at all ;)...so far I
have tried removing the registry entry for the windows service but
that did not work. So I'm stuck at the moment.