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!

Are Amazon Spots Struggling?

[Update - heard directly from AWS and they report that the graph issue is a known bug and unrelated to the problem we were seeing getting m2.2xlarge instances. Hopeful we'll get to the bottom of the underlying problem]

[Update #2 - once we shared the info around time and type, AMZN was able to verify that there was a temporary issue involving only the m2.2xlarge, only in our region, and right in the window we happened to want them. Bit unlucky, but we should be all good now :-) ]

This seems pretty coincidental with the AWS announcement yesterday with per-AZ priced spot instances. At yieldbot we do some heavy lifting with clusters of m2.2xlarge servers, and haven't been able to get any spot instances for hours. Luckily we fall back to reserved instances if we have to, we'd just rather not pay the higher price. :-)

Here's a snapshot of the price history taken a few minutes ago for the m1.xlarge instances. Note that us-east-1b and us-east-1d seem to have stopped a while ago. us-east-1a and us-east-1c goes close to current, and our testing verified we could get us-east-1c instances.



Now check out the graph for the m2.2xlarge instances, taken about the same time. Availability seems to have fallen off a cliff hours ago, particularly in us-east-1c which appears to have not had availability for this instance size on the spot market for about 12 hours now.



The AWS health dashboard currently shows all green, which at some level is understandable. Spot instances are, after all, an as-available resource. The interesting thing here though is that the price shows no spikes, no price movement indicating an approaching shortage. The market didn't get expensive, there just stopped being a market.

Maybe AWS suddenly needed to add some instances to the pool of available reserved instances? Happily, reserved instances did come up and with no delays.

But we can't wait to start saving money with the spot instances again.

Anyone out there seeing the same or similar problem?

Amazon Saved Us a Bunch of Disk Space

Someone should start a webpage where people can post the beneficial things that happened to them as a result of the Amazon "cloudocalypse", just for the contrarians out there. Here's our entry.

We use MongoDB for a good piece of our platform, and have been meaning to swap in a backup of the database as the primary.

Because we had created some large collections a few months back that were subsequently dropped (for which MongoDB doesn't reclaim disk space) we found ourselves in a situation of pretty inefficient disk utilization. We'd recently bumped up to 90% on the database disk volume.

At the same time the backup with the same size volume was running at about 40%.

It was on our list to swap the backup in as the primary, but we hadn't gotten to it yet. Yesterday Amazon decided for us that it was time. And now we have all this free disk space in our database volume.

Thanks Amazon! :^)