Wednesday, August 17, 2011

UghSQL

I spent a good deal of time wrestling with getting MySQL installed on my new MacBook Air (OSX Lion), and in the name of saving someone else from repeating that amount of frustration I want to share the insights gained.

The problem I was having was that the install all went fine (using Macports) but the post-install setup of the database, using mysql_install_db, was failing. When it seemed to succeed, it left me with an empty users table and inability to gain access (or set the password for the root mysql user).

I found this: https://trac.macports.org/ticket/28772 which perfectly described the situation I was in.

In the comments of that I got to this stackoverflow answer which was even better.

The first problem I had, for whatever reason, was related to permissions, and that got me past that.

However, it continued to fail.

If you're currently in that situation, I feel your pain. And here is the nugget of info you might need.

The mysql_install_db script attempts to not overwrite tables that already exist when it is called. I believe the mechanism used to do this is a little too coarse. The scripts use the @@warning_count variable to try and detect if a table already exists, setting other variables, like in this case I hit @had_user_table.

After having done an uninstall/install to get me back to clean, I ran the mysql_install_db script again, but also got warnings. It probably doesn't matter, but I specifically got this:

110816 23:42:06 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.

It may have been these, or it may have been some other warnings that were silent, but I think the (empty) user table got created but the initial entries for the root user didn't get populated. Once the empty table exists, subsequent executions of mysql_install_db then avoid writing new entries into the table. The net result being you never get the initial entries for the root user that you need.

The simple fix to this was to modify the mysql_system_data_tables.sql file (which for me was in the /opt/local/share/mysql5/mysql/ directory) to change:

INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0;

to be:

INSERT INTO user SELECT * FROM tmp_user;

This is basically setting it up to ignore the fact that it encountered warnings the table existed and instead you're telling it that you know better.
If you're going to do this, you should make sure your table really is empty. Run the server with the --skip-grant-tables option and run mysql and verify that the following:

SELECT * FROM mysql.user;

returns no results.

That we use MySQL for such a very small part of our platform at Yieldbot made it that much more painful. That it kept me from what was going to be my focus for the night, working with HBase, was all the more ironic.

Happy troubleshooting!

No comments:

Post a Comment