The First Big One: 64-bit MySQL and mysql Gem on Snow Leopard

NOTE: This Post describes how to install a 64-bit version of MySQL with the 64-bit Gem on a 64-bit system. If you are unsure about how to figure out if you are on a 64-bit system, please check this. Please be sure to read the entire article before proceeding. If you are still unsure, you can simply install the 32-bit versions of MySQL and it’s gem by simply following the guide and replacing everywhere it says the phrase “x86_64″ with “x86″ by itself. The guide should still be the same overall.

Prequisits:

• OS X 10.6: Snow Leopard – This is the OS I am using, it might work the same on 10.5, but it is untested on that machine, so proceed with caution.
• OS X Developer tools – These should come on the Snow Leopard install disc, however, you can download a more up to date version on their website.
• Ruby 1.8.x – This should be installed by default with the developer tools, but if it is not or it is not preinstalled on your machine, you may get it here.
• Gem 1.3.* – I have 1.3.7, but any version within the last year or so should do. This may or may not be installed depending on how Ruby was installed, so you can check by doing a “gem -v” in your terminal to see if you even have it.
• Rails 2.3.* – I set mine up with 2.3.5, however, I was also able to get it working on 2.3.8. By default the latest version gets installed when you use the gem install, so if you want to installed 2.3.5 (which is recommended do to all the issues with 2.3.6-2.3.8), use the following command to install it:
sudo gem install rails v=2.3.5

Installing MySQL Server:

1. Download MySQL: OS X 10.6 MySQL 5.1.* (x86, 64-bit), DMG Archive or Mac OS X 10.5 (AMD64, installer format): While I started this saying that this guide is meant for 10.6, I am using a previous version of MySQL 5.0.91 because it is what I use at work as well as the web server I’m currently using. Note: Even though it says it’s meant for 10.5, it just means that there is no specialized version meant for 10.6.
2. Install MySQL from the DMG: I installed it in the default location on the drive containing my OS. If you install it in a different location, please take note of it now as you will need to replace all instances of ‘/user/local/mysql’ with this location in the guide. I would also recommend installing the Pref panel and the startup utility because it makes your life a lot easier when you want to turn the server on and off.
3. Start MySQL Server: If you installed the Pref Panel, simply go into the System Preferences >> MySQL, and hit “Start MySQL Server.”
4. Check the character set on the server: Use the following command to figure out what character set your MySQL server is running by default. If you’re output has ‘utf8′ listed in all 4 character sets, then you can skip to the gem installation section. If you’re getting a “ERROR 2002 (HY000)” message, it means you skipped step 3 and forgot to turn MySQL server on! NOTE: The double arrow is my prompt and should not be typed in.
>> mysql -uroot -e'status'
--------------
mysql  Ver 14.12 Distrib 5.0.91, for apple-darwin9.8.0 (i386) using readline 5.1
Connection id:      3
Current database:   wtmagm_development
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.0.91-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /tmp/mysql.sock
Uptime:         6 sec
Threads: 1  Questions: 6  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6  Queries per second avg: 1.000
--------------
5. Setting up my.cnf file: Check the ‘/etc’ directory for a my.cnf file, if you have it, skip to step 7. If you don’t have one, it means you need to create one for yourself.
6. Creating a my.cnf file: You can either create one from scratch, or you can use some preset my.cnf setting files provided by MySQL. These template files exist in the ‘/usr/local/mysql/support-files/’ directory, I personally used ‘my-medium.cnf’ as my config file. When you have decided on one, copy it to your ‘/etc’ directory and rename it to ‘my.cnf’.
7. Restart the server and check the character set: Follow set 3 on how to restart the server, and then follow step 4 on how to check the character set. As before, if all 4 character sets are set to ‘utf8′, then skip to the gem section.
8. Setting MySQL to use UTF8: Open the ‘/etc/my.cnf’ file and add the following lines to the ‘[mysqld]‘ and ‘[mysql]‘ sections to set them to use UTF8 as the default character sets and save the file.
[mysqld]
collation_server = utf8_unicode_ci
character-set-server = utf8
[mysql]
default-character-set=utf8
9. Confirm that the character set has been changed to UTF8: As before, restart MySQL and run the command from step 4. At this point all 4 character sets should be set to ‘utf8′ and you should proceed to the next step of the guide. If they are not, please confirm you have followed the previous step properly. If that does not work, check the official MySQL documentation to see if these setting names/values have changed. Again, this was setup on 5.0.91, if you are using a newer version of MySQL, these setting names/values might have been changed.

Installing mysql Gem:
Use the following command to install the mysql gem, but please read what each line says before you proceed. All you need to do is copy the following code bit (in it’s entirety) into Terminal.

There are several things that need to be pointed out here:

• While some guides have said you only need to add the ‘with-mysql-config’ line, I have found that unless you include all of them, this gem does not install properly.
• If you need to stall the latest version of the gem and not 2.7, simply remove the entire ‘-v=<version number>’ section out of the command and it will auto select the most recent version on RubyGem.org.
• If you have decided to install MySQL onto a different location then the default, you will need to update the locations on the command before running it.

Setting up Rails:
If you open your rails project, in the ‘config/database.yml’ should be the configuration information for that project. Depending on the version of rails, it would look something like this:

(This is for development and test sections only, test should look similar to development but with a different database name.) While your development section does not need to look exactly like this, there are several required fields: adapter, database, username, password, socket, and host. By default, the username, password, socket and host should be the same as the one in this example. If you made a custom user/password for this project in the MySQL database, here is where you should update those values. Since we’re using MySQL instead of SQLite, you will need to change this field to match the example as well. The only thing that may look different between this example and your file is the database name and fields that I didn’t match before.

Note: There seems to be an error in the way my code coloring is outputted, so ignore everything before the word development.

Possible Errors:

This stems from a character set mismatch between the gem defaults and the MySQL Sever defaults. Make sure you set up the my.cnf properly. Please Reread the section on setting up MySQL Server (Parts 4 – 8).

If you happened to switch between different versions of rails in a single project, this error will pop up. Unfortunately, the only way I know off the top of my head to fix this is to create a new project and copy over the specific config and other app files you had created into this new project. Depending on what you copy, this error might appear again, I would recommend looking inside the files you copy to make sure that version specific files are not being moved. If they are, it might be easier to just edit the files individually to have the same non-version based settings.

Unfortunately I after trying so many different combinations of installs and trying to fix this a couple times, I forgot to copy down the exact error. Needless to say, I have figured out why this or other similar dylib errors occur. It is because there is a mismatch in architecture between the MySQL server and the mysql gem. Make sure if you’re installing the 64-bit version of each they both contain the proper flags. In the case of the server, just make sure it says “x86_64″, “AMD64″, etc. and for the gem you add the correct ARCFLAG (please look in the mysql gem installation section for the correct flag for 64-bit). If you happened to use this guide to install the 32-bit version, there shouldn’t be a “64″ listed anywhere in the installs for either of these things.