h1ghlevelb1ts

I just started a little side project using Ruby on Rails and decided today to start developing against the database I will be using in production - MySQL. So far I have been using SQLite which worked fine, but it is always good to stay as close as possible to the target environment as possible. Anyways, let my odyssey begin...
In theory I just have to edit config/database.yml and change the adapter parameter to mysql. Then restart the rails server script/server -e development. Done!
Well, not so fast. The first restart tells me that mysql is not part of the default rails application anymore. The error message is quite clear and basically recommend to just run gem install mysql. Fair enough, I run the gem install and restart the server. Now I get:

> script/server -e development

=> Rails 2.3.4 application starting on http://0.0.0.0:3000
/Users/hardy/.gem/ruby/1.8/gems/radiant-0.8.1/vendor/rails/activesupport/lib/active_support/dependencies.rb:440:in `load_missing_constant': uninitialized constant MysqlCompat::MysqlRes (NameError)

Ok, not so funny anymore and quite cryptic. Uninitialized constant that cannot be good.
A word to my environment. I run Max OS X (Snow Lepard) on a Mac Book Pro. MySQL is installed via MacPorts and can be found somewhere under /opt/local. The MySQL version is 5.1.40.
What does one do in such a situation? I start googling and end up in some cryptic mail archives. Bugger, worst case scenario. Digging through mail archives is a real pita. Information is often unverified and skimming through through a million >>>>>>>>>> is just no fun. After some searching I suspect that the problem I am experiencing is that the mysql gem needs to be build for 64 bit. I decide to try:

sudo env ARCHFLAGS="-arch x86_64" gem install mysql –with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

After that I start the server again. This time I get:

/Users/hardy/.gem/ruby/1.8/gems/radiant-0.8.1/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:585:in `real_connect': Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2) (Mysql::Error)

Argh, another error. At least it is something more familiar. MySQL lets you either connect via TCP/IP or via Unix sockets. But why does it try to via /opt/local/var/run/mysql5/mysqld.sock. I am pretty sure I am using a different socket file. I locate my MySQL config file and find that the socket file is /tmp/mysql.sock. Why did that not get picked up? I add:

socket: /tmp/mysql.sock

to my config/database.yml. One restart later and I get:

/Users/hardy/.gem/ruby/1.8/gems/radiant-0.8.1/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:585:in `real_connect': Access denied for user 'foobar'@'localhost' (using password: YES) (Mysql::Error)

Will this never end? At least it's another classic. MySQL permissions. First step is always the mysql.user table. I probably forgot to add an entry for my user and database. I connect as root and execute:

CREATE USER 'johndoe'@'localhost' IDENTIFIED BY 'foobar';
GRANT ALL PRIVILEGES ON mydb.* TO 'johndoe'@'localhost' WITH GRANT OPTION;

After that I try to start the ruby server again. Same problem. Some quick swearing, but then I remember - flush! Back to the mysql shell:

FLUSH PRIVILEGES;

And now I get an error in the mysql shell:

Table 'mysql.servers' doesn't exist

WTF! After some more swearing (this time in German) I start googling again. I cannot find a reasonable explanation on why this table is needed or why it is not created in my mysql database. Best I find is a table creation script which I decide to run:

CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';

Ahh, now flushing privileges works. One more script/server -e development - success! Finally I am up and running again, but I wonder:"Do things really have to be so painful?"

--Hardy

P.S.: With MySQL permission/user problems I recommend to go through these guides: access- denied and adding-users.

P.S.S: Another problem which had me puzzled for a while one was that I was not able connect to MySQL using:

mysql5 -u foobar -p

Whenever I tried to connect with a password I would get the Access denied for user (using password: YES) error. Connecting without the -p option worked though. I kept looking at the mysql.user table and the entry for foobar. What I did not notice was that I had effectively something like this:

Host       User      Password       
localhost 
%          foobar    

The first line allows any user to connect from localhost using no password. Since localhost is more specific then the % wildcard this rule was always applied before the actual foobar entry.