The Danger of Fingertip Memory
In the same vein as Deleting saved form entries on Firefox, I’ve noticed a bad habit I’ve developed recently of typing my password when no password is needed.
Most recently, and most tragically, I was mindlessly leaving a comment on a friend’s blog, a typical WordPress install that requires a name, email, and url. Except my fingertips apparently thought I was logging into Gmail and typed my name, email, and password—and then immediately hit submit! Ugh. Suddenly there’s my strong password sitting out in the open, live on the web for all to see (as if it was my blog’s URL), e.g. http://myp4ssw0rd!/
Now chances are anyone who stumbled upon that would have seen a jumbled array of characters and thought I sneezed instead of entering my URL. But I didn’t want to risk one clever or curious person who might have tried to use it to login to my Gmail account, or my blog, or about 5 other places I used that password—all of which I’ve now changed.
I also find myself mindlessly entering my password at the command line in Ubuntu. I’ve gotten so used to sudoing commands, that sometimes I forget I’ve already sudoed, and I’ll enter my password at the command line right after running a sudo command. Oops. There’s probably a lot of mineable passwords in the .bash_historys of the world. I wonder what my sysadmin friends do/recommend when that happens…
Broken blog = man obsessed
Databases are supposed to be these monolithic paragons of correctness and uniformity. So it’s a little unsettling to discover a database that returns grossly incorrect results. At least I wouldn’t expect WordPress to put version 5 of MySQL through its paces.
Here’s the scenario. Dreamhost, my webhost, is running MySQL Server v5.0.24a, released in August 2006. On my laptop I’m running MySQL Server v5.0.51a, released in January 2008. The difference between the two is a year and a half of bugfixes and performance improvements.
On each I’ve got a copy of the same database table, wp_posts, from my WordPress 2.5.1 install for Justinsomnia. And on each system I’ve got a copy of the giant SQL select statement generated by WordPress that returns the first 8 blog posts that should appear on my homepage. The unusual thing about this particular query is that it contains an insanely long NOT IN () list to filter out my neatlinks from the main column.
SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
WHERE 1=1
AND wp_posts.ID NOT IN ('876', '877', '878', '879', '880'...'5456', '5458', '5460', '5461', '5462', '5463')
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 8
The problem is that running the same SQL statement on each server returns different results—and I wanted to figure out why.
So I prefixed the query with EXPLAIN, to see what each version of MySQL would tell me about how they were dealing with the same query. I expected the results to be the same. They turned out to be quite different.
MySQL 5.0.24a: +----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | wp_posts | range | PRIMARY,post_status,type_status_date | PRIMARY | 4 | NULL | 611 | Using where; Using temporary; Using filesort | +----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+ MySQL 5.0.51a: +----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+ | 1 | SIMPLE | wp_posts | ref | PRIMARY,post_status,type_status_date | type_status_date | 25 | const,const | 5350 | Using where | +----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+
As you scroll to the right, you can see that v5.0.24a chose the PRIMARY key to find the most recent posts, while v5.0.51a opted for type_status_date. I’m not quite sure what that means, except that v5.0.24a obviously made the wrong choice. Which gave me an idea for an interim solution (while I wait for Dreamhost to upgrade MySQL): index hints.
If I could force MySQL to ignore the PRIMARY key, maybe it would “fix” my blog. So I added IGNORE INDEX (PRIMARY) to the query that selects posts, and voila, my blog homepage started working again! Here’s the diff of the code change I made. Luckily I have WordPress “installed” via Subversion, so any changes I make to core files get carried over with each upgrade.
Index: wp-includes/query.php
===================================================================
--- wp-includes/query.php (revision 7987)
+++ wp-includes/query.php (working copy)
@@ -1424,7 +1424,7 @@
if ( !empty($limits) )
$found_rows = 'SQL_CALC_FOUND_ROWS';
- $request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
+ $request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts IGNORE INDEX (PRIMARY) $join WHERE 1=1 $where $groupby $orderby $limits";
$this->request = apply_filters('posts_request', $request);
$this->posts = $wpdb->get_results($this->request);
Hopefully I haven’t inadvertently broken some other aspect of my blog. If anyone sees anything out of order, please leave a comment, mmmk?
Update: for Nate, here’s the EXPLAIN on 5.0.24a with the IGNORE INDEX:
+----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+ | 1 | SIMPLE | wp_posts | ref | post_status,type_status_date | type_status_date | 25 | const,const | 5351 | Using where; Using temporary; Using filesort | +----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+
Library Blogging
Jason Griffey, an LS friend of mine from grad school (part of our original blogging cabal) has just written a book called Library Blogging. How friggin’ cool is that?
That should probably get him into the SILS alumni hall of fame!
Introducing White Noise Lounge
After three weeks in not-so-stealth-mode, it’s time to take the wraps off a new joint venture between me and Stephanie, which we’re calling White Noise Lounge.
It all started about a month ago when we were joking with each other about the idea of an internet radio station that broadcast nothing but white noise—24 hours a day. At some point this joke crossed over into the realm of possibility, and we started working on it like a real project, albeit in the form of an audio blog.
The format is pretty simple. Every post is built around a short audio track that we’ve recorded from our environment. The sound could be anything, we’ve got a long list of ideas, but usually something continuous, rhythmic, and ambient. Every post contains a black and white photo of the sound’s source, in keeping with the site’s monochromatic color scheme (a nod to the “snow” of video static). As this is a blog at heart, we’ll also write something about the place or the sound.
Our goal is to post something new at least once a week, so please feel free to add the feed to your feedreader or audio podcast fetcher. We’ve submitted the site to iTunes, but haven’t heard back yet.
Update: We are no longer updating White Noise Lounge. All the old posts have been imported into Justinsomnia.
From the Belly of the Beasts, a eulogy
When I worked at O’Reilly Media, the Online Publishing Group (OPG) had this external-facing blog they called “From the Belly of the Beasts”, a reference to O’Reilly’s famous animal-branded technical books. The blog lived at blogs.oreillynet.com/beasts/.
It was started in October 2004 by Terrie Miller, the group’s manager. I arrived in May 2005, hired partly to assist with O’Reilly’s blog and feed projects. I loved that our group had a blog of its own, so I started posting to it within days of my arrival.
In order to justify blogging at work (to myself), I treated it as a knowledge sharing tool. I’d usually post useful things I found online and then pass the URL around to my co-workers. Frequently I’d refer back to an old post to jog my memory (or someone else’s). It was also a place to be a little silly, to lighten the mood. I posted there frequently until I left O’Reilly in June 2006. After my last post only two more posts appeared, one by Mark and one by Terrie, and from that point on the blog has lain dormant. Update: Terrie left O’Reilly in March 2008.
I’ve always been a little surprised, and of course happy that those old posts still exist out there on the web. They come from a special time in my life, my first year in California, my first job outside of the university. I definitely felt I was leaving behind something dear when I left O’Reilly. I expected the blog to just cease to exist one day, presumably after someone unplugged some dusty server in the corner that happened to be blogs.oreillynet.com.
Anyway yesterday I heard from Mark that the Beasts blog is indeed getting “unplugged”, and he wanted to know whether I wanted to save anything beforehand. Yikes, yes! So I’m going to recreate each of the posts I wrote there on Justinsomnia, categorize them in such a way that they can be pulled out together, and postdate them appropriately. I’m actually really excited about this, to me it’s like getting back a piece of myself for my archives.
The WordPress SQL Executioner
If I had a dime for every time someone said “WordPress Suicide is great, but I only want to delete posts that are more than a year old,” I’d probably have 50¢. Which is about when I start to think about solving the problem with a new plugin.
The programmer/database jockey in me knows the request above is insanely easy. Just start up the MySQL command line client, connect to the WordPress database and fire off the following query:
delete from wp_posts where post_date < date_sub(curdate(), interval 1 year)
Of course knowing SQL takes experience, but the first barrier for most people is just getting command line access to their WordPress database (especially on shared hosts). I figured if it was easier for the average WordPress user to gain direct access to their database from within the admin interface, it’d be a whole lot easier for me to help them, by giving them custom one-off SQL queries to run. And oh by the way, this would be pretty awesome for the power user.
So that’s what I did, I built a stripped-down PHPMyAdmin in the form of a WordPress plugin. WPMyAdmin if you will, though I opted to call it the WordPress SQL Executioner. Actually it’s more of a web-based version of the mysql command line client. It performs both selects and describes for analysis, and inserts, updates, and alters for modification. In fact you can send it just about any SQL command MySQL accepts. Here’s a screenshot to give you an idea of what I’m talking about:
Simply enter your SQL query and click “Execute SQL”. If it’s a select, describe, show, or explain, an HTML table will be returned with your results. If it’s any other command that modifies data or structure, you’ll get a message about the number of rows affected. You’ll have one chance to confirm that you want to execute an insert, update, delete, or alter, but after that, your fate is in your own hands.
The tables listed above the SQL box are shortcut references to all the tables in your database. This makes it possible to write a generic SQL query with variables in place of the actual table names so that it’ll work on any WordPress install, regardless of the table prefix (it’s usually wp_). Of course the actual table names with prefixes are accepted. For example, the query above could be rewritten as:
delete from $posts where post_date < date_sub(curdate(), interval 1 year)
Finally one handy bonus feature, if you click on any of the table names in the list, it will automatically describe the table for you.
Instructions
- Download wp-sql-executioner-1.0.zip (v1.0)
- Unzip the file and upload wp-sql-executioner.php to your plugins directory:
/path/to/wordpress/wp-content/plugins - Activate the plugin (don’t forget!)
- Goto Manage > SQL Executioner and have fun!
As always, questions, comments, and suggestions are always welcome.
Think globally, blog locally
I wish there was a blog where I could learn about the place I live and the people who live (and blog) here. I want to read about things to do, things to see, written by the people who are doing them.
The funny thing is there are so many people trying to solve this problem in different ways, all interesting, all addressing part of the problem, but no one gets close to what I want.
Here’s a quick survey of what’s out there:
Upcoming does events, Pollstar does concerts, Yelp does reviews, Daily Candy does fashion, craigslist does classifieds, Outside.in and EveryBlock do geo aggregation, SFist and Valleywag and Metroblogging do gossip and news, SF Gate and SF Weekly and the Bay Guardian have “sort-of” blogs, Eater SF does restaurants, Curbed SF does real estate, Rescue Muni and The N-Judah Chronicles do public transit.
What I want is something like a Boing Boing for San Francisco. A “directory of wonderful things” about the Bay Area, by and for the community. The MetaFilter model comes to mind, but less insular.
Of course the forward-looking part of me would like the concept to scale beyond San Francisco to any metro area, a la craigslist, because it’s hard to live here and not think about starting some kind of technology-cum-media company in your spare time. But let’s not get ahead of myself.
This, in essence, was the big idea that I alluded to last week. Not original or earth-shattering by any means (even MetaFilter Matt has blogged about this), but borne out of a frustration I’ve felt at the hands of the existing SF-specific blogs. And an awareness that I have a need that is not being met. And maybe I’m not alone.
It’s possible that the reason this type of blog doesn’t exist (in San Francisco, no less!) is because it’s actually impossible in practice. The very nature of a good blog has a lot to do with the personality and voice of the blogger, not the city the blogger lives in. Dooce is Dooce because of Heather Armstrong, and though Utah plays a primary role, I’m guessing Dooce would continue to be Dooce from anywhere. Because of our mobility, it’s hard for a blog to be rooted in a single place. And it’s hard to define San Francisco as a single place.
So how do you create a great blog about a place that doesn’t rely on a single voice?
Importing Haloscan comments into WordPress (v2.3 and up) from Blogger
Note: Even though I originally wrote this for WordPress v2.3, it continues to work for all versions of WordPress through v2.9.x.
Background
Who knew that when I migrated my Blogger blog to WordPress way back when, I’d still be supporting a hacked together Haloscan importer 3 years later!
Awhile back, I don’t remember if it was during WordPress 2.2 or 2.3, the folks at Google revamped Blogger, which meant the old Blogger importer no longer worked. So the heroic coders that do the heavy lifting for WordPress set out to write the-Blogger-importer-to-end-all-Blogger-importers, this time using Blogger’s feed-based “API”.
The problem for you classic Blogger folk with Haloscan comments is that the only way to link your Haloscan comments to their post is via Blogger’s postID. Which is apparently not accessible in the feed API. Grrr. Which meant my previous hack for associating the postID with the newly imported WordPress post no longer worked.
Last night, I finally went back to the drawing board and came up with a new solution. I’ve tested it successfully on two different blogs.
