Running as Root

The short version of this story is that I had a test server that was inadvertently configured to use the MyISAM engine of MySQL. This engine doesn't support foreign keys. It will quietly ignore your attempts to add them. I meant to use the InnoDB engine (which does support foreign keys). Of course, who hasn't done that? Am I right?

I fixed the engine problem quickly enough. Next I wanted to take a version of our production / dev / whatever that had the foreign keys and export the necessary "alter table" statements to add them to the fixed version of the test database. I couldn't find anything so I whipped up this SELECT statement to generate a script based on my limited understanding of MySQL. If it helps someone else then great.

SELECT concat('ALTER TABLE `',  table_name, '` ADD CONSTRAINT `', CONSTRAINT_NAME, '` FOREIGN KEY (`', column_name, '`) REFERENCES `', referenced_table_name, '`(`', referenced_column_name, '`);') from information_schema.key_column_usage where referenced_table_name is not null and constraint_schema = 'ourserverdb' order by table_name, column_name

This of course results in a whole bunch of rows of the form:

ALTER TABLE `licensekeys` add constraint `FK_keysIssuerId__appuserId` FOREIGN KEY (`issuer_id`) REFERENCES `app_user`(`id`);
ALTER TABLE `subscription` add constraint `FK_subscription_entity_group_id__entityGroupId` FOREIGN KEY (`entity_group_id`) REFERENCES `entityGroup`(`id`);
ALTER TABLE `user_role` add constraint `FK_userRoleRoleId__roleId` FOREIGN KEY (`role_id`) REFERENCES `role`(`id`);

From there it's just a little copy / paste into MySQL command prompt and I'm done. Incidentally mysqldump with the --no-data flag didn't do quite what I wanted since the foreign key creation is in the middle of a CREATE TABLE statement. There are surely other ways to do this but this is what worked for me.

Every now and then I bask in the beauty of the simple things. I'm not talking about children smiling, flowers, or any of that other crap. Shell scripting, baby! Today I had to move some SQL statements in some XML document into a Java class. So I needed to change this (which I didn't write):

SELECT CASE
    WHEN primaryStartAge < 20  THEN ' 0 to 19'
	WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29'
	WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39'
	WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49'
	WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59'
	WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69'
	WHEN primaryStartAge > 70 THEN '70 and up'
	END as "Primary Start Age Range",
	count(1) as "Count" FROM analyticsResults
	WHERE calculatorType like ?
	GROUP BY CASE
	WHEN primaryStartAge < 20  THEN ' 0 to 19'
	WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29'
	WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39'
	WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49'
	WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59'
	WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69'
	WHEN primaryStartAge > 70 THEN '70 and up'
END
ORDER BY 1 ASC

to something like this (which I still didn't write):

"SELECT CASE "
            + "    WHEN primaryStartAge < 20  THEN ' 0 to 19' "
            + "    WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29' "
            + "    WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39' "
            + "    WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49' "
            + "    WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59' "
            + "    WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69' "
            + "    WHEN primaryStartAge > 70 THEN '70 and up' "
            + "    END as \"Primary Start Age Range\", "
            + "    count(1) as \"Count\" FROM analyticsResults "
            + "    WHERE calculatorType like ? "
            + "    GROUP BY CASE "
            + "    WHEN primaryStartAge < 20  THEN ' 0 to 19' "
            + "    WHEN primaryStartAge BETWEEN 20 AND 29 THEN '20 to 29' "
            + "    WHEN primaryStartAge BETWEEN 30 AND 39 THEN '30 to 39' "
            + "    WHEN primaryStartAge BETWEEN 40 AND 49 THEN '40 to 49' "
            + "    WHEN primaryStartAge BETWEEN 50 AND 59 THEN '50 to 59' "
            + "    WHEN primaryStartAge BETWEEN 60 AND 69 THEN '60 to 69' "
            + "    WHEN primaryStartAge > 70 THEN '70 and up' "
            + "END "
            + "ORDER BY 1 ASC";

I could copy and paste and fix it manually, use a text editor with regex search and replace, or something equally bland. Since it was Friday though i decided to treat myself and do it from a Cygwin shell. This got me close enough and made me giddy with satisfaction:

getclip |sed -e 's/"/\\"/g' -e 's/^/"/g' -e 's/$/ " +/g' |putclip

This grabs the contents of the clipboard, replaces all quotes with escaped quotes, replaces the beginning of each line with a double quote, and replaces the end of each line with a space / double quote / space / plus combo. It then sticks it back into the clipboard. It's not fancy, it could be better, but it was a minor bright point. And thanks to Cygwin it happened in Windows. Sort of.

So I'm actually using my old iPod again rather than using the ultra-fantastic Pandora (highly recommended by the way) and I had an idea for a feature it should have. Keep in mind I'm several generations behind on my personal media player so this may be in the current iteration. What I'd like to be able to do is suspend shuffle mode, listen to a few songs by the artist that came up in shuffle, and finally resume shuffle mode. That's it. I can do this but it requires navigating through multiple menus. Not so convenient.

I'm not a Ruby programmer but I thought this was kind of cool. While poking around on Stack Overflow the subject of storing letter frequency for words came up. While there may be a better solution, the idea of alphabetizing the word and storing letter frequencies of 3 or over as the number of occurrences followed by the letter seemed like a passable solution. For instance, "mississippi" is alphabetized to "iiiimppssss" and the multiple occurrences are further reduced to result in "4impp4s". Seems simple enough and in the case being discussed it would result in very little impact on the storage mechanism or the code around it.

The whole thing turns out to be pretty easy as a Ruby one liner:

"mississippi".split( // ).sort.join.gsub(/(.)\1{2,}/) { |s| s.length.to_s + s[0,1] }

That can probably be made a lot better by a Ruby expert. The regular expression finds any character followed by the same character two or more times and then passes the matching string to the following block as a parameter s. It then returns the replacement string which will be the length of the matched string (the character count) followed by one of characters from the matching string. It executes this as a global substitution on the original string. Wha-bam!!! I wonder if there's an odd edge case where this breaks.

I just got through reading a post that Google doubled its Street View coverage. I will admit that I know roughly zero about the inner workings of Google but this gave me an idea that I can in no way use to my benefit.

What if the setup for equipping a car with the "Street View hardware" was sufficiently unobtrusive that you could easily mount it on your rental car? Would it be worth it for Google to partner with a rental car company and offer a $5 or $10 per day discount on your rental fee? Or better yet, what about partnering with U-Haul? Would the information be too redundant (everyone driving around in the same locations) or just plain useless (like empty stretches of interstate highways)?

Just a random thought.

The footholds of Linux in small Windows shops are skunkworks projects and discarded hardware. Inevitably the old mail server or the equivalent is considered woefully underpowered and gets replaced. The old hardware sits in a corner of the server room and collects dust. That is until I need a "no money down" VMWare solution.

Of course the downside of this is that you will find yourself installing on frequently inadequate, old hardware that may or may not work–no one ever seems to be sure. When something goes wrong it's Linux's fault. Such was the case when I had to install on an old Dell PowerEdge 600SC. Of course, the install didn't work right off the bat.

The install hung with the last message being "Uniform CD-ROM driver Revision: 3.20″. I randomly upgraded the BIOS hoping it's some weird problem with the on-board IDE and see the same problem. Then I noticed that the CD-ROM is attached to the tertiary channel. I can't recall ever having that setup before so I moved the CD-ROM drive from the tertiary to the secondary channel (by accident because the order of the IDE connectors from bottom to top of the motherboard appears to be secondary, primary, tertiary).

After the install it appears that I can't get either DHCP or a static IP to work. Everybody assures me that it's not the IP address they gave me or our DHCP server. I try a different network card with the same effect. Finally, I figure out that it is in fact the network of the IP address they gave me that is to blame (and our DHCP server seems to have crapped out at the same time, and no it isn't running on Linux). But, people stubbornly insist that it's Linux's fault until I waste my time proving otherwise. While I'm gathering evidence they make a point of wandering by my desk and asking why I'm not just using Windows. When society collapses they've got a special place on my post apocalyptic TODO list.

I finally get it all working with a fresh install of VMWare 2.0 (hate the new management web app, by the way) and a migrated VM from my desktop that has a copy of Zenoss Core happily monitoring our new production environment on EC2. Everything in that setup is new from the point of view of this organization. Of course while I'm patting myself on the back over a job well done, someone asks how to get to the desktop UI. Although it probably won't help them much I go ahead and install GNOME, VNC, and Webmin on the box even though I consider it a waste.

Now I get to sit back and eagerly await the opportunity to bask in the criticism the next time anything goes wrong with the box. I'm sure it'll be the fault of that darn Linux.

I just don't feel funny this week.

Before I started putting the comic directly on my blog, I had a few other episodes of Flat Organization. Since many people never click through to my BitStrips page I'm going to lump all of the "missing" episodes into this one post. I could post them individually but that seems more annoying and like it would require more effort. Apologies to those that have already seen these.

Unfortunately this week's comic just feels like I'm getting more and more bitter. Maybe the next one will be funny.