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.
For anyone who’s left their feedreader and visited my homepage in the last few days, you may have been surprised to see that the most recent post in the leftmost column is 15 seconds of fame from Tuesday, February 13, 2007 11:01am. Yes, 2007.
Of course that’s not actually the most recent post—it should be showing Caught between PulseAudio and a quiet place from Wednesday, May 21, 2008 4:39pm, (and now this). But what’s altogether weirder is that the neatlinks column is completely up to date.
In my index.php template file, I actually have two loops, the main one which shows all posts without neatlinks, and the neatlinks loop which only shows posts from the neatlinks category. To exclude neatlinks from the main column, I just add query_posts('cat=-23'); above the loop.
After a little digging into the PHP code behind WordPress, I had it spit out the query it sends to the database to pull out the data for the posts in the “The Loop”. What’s crazy (at first) is that rather than generating one big query joining every possible table to wp_posts, WordPress runs preliminary queries based on the query_posts attributes, to return a list of eligible or ineligible post IDs. Then it does the final posts select using giant IN() or NOT IN() lists in the where clause. The main reason it structures it this way is so that it can use the LIMIT clause for paging.
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
When I ran this giant query directly on the database, I got back the old blog posts visible on my homepage. But when I ran it on my laptop with a copy of my WordPress data, I got back the correct list of recent posts. When I deleted a few IDs from the thousands in the NOT IN() part of the query, and ran that on the database, I got the correct posts back.
So it appears there’s a devastating bug in MySQL’s handling of long IN() lists that’s since been fixed. Unfortunately Dreamhost is running MySQL v5.0.24a, which was released in August 2006, almost two years ago! Locally I’m running 5.0.51a from January 2008.
The MySQL docs say that the IN() list is limited only by the max_allowed_packet size, which is 16 megabytes by default, and which this query, as giant as it is, is nowhere near reaching. I skimmed through the release notes between those two versions of MySQL, and the closest thing I could find to a related bug was in the 5.0.41 release notes from May 2007:
For exprIN(value_list), the result could be incorrect if BIGINT UNSIGNED values were used for expr or in the value list. (Bug#19342)
So what did I immediately go do?
alter table wp_posts modify ID int unsigned not null auto_increment;
No difference.
Once again I’m caught between a rock and a hard place. On one hand, I have no idea what’s the likelihood of Dreamhost upgrading MySQL on their database servers (though I sent them a very detailed support request), and on the other hand, my blog is broken, and that makes me very grumpy. I generally like the laid-back Dreamhost culture, but it might be time to pay for something a little more dedicated…
This is a proto-meme for people running a GNOME-based OS, like Ubuntu.
Go to Applications > Accessories > Disk Usage Analyzer. Click “Scan Filesystem”. Take a screenshot (Ctrl+Print Screen). Paste it to your blog. Show your friends. It’s fun.
Red is my home dir, with the purple and blue subdirectories all photos. Green is .Trash, 2.7 gigs worth! Brown is my Desktop and .wine. Orange is /usr/share and /usr/lib.
Started the upgrade process to Ubuntu 8.04 (Hardy Heron) last night, and finished it up this morning. Overall impressions are very positive, thanks to a few (but not all) of the things that broke in Gutsy fixed. The updates to Gnome and Firefox mean I can probably upgrade to Hardy from the rock solid Feisty Fawn I’ve been running at work.
The bad:
My network drives on the desktop (and in Places) disappeared
I don’t care for the default Heron background, switched back to the one from Gutsy
Immediately got a crash report on reboot about Gimp and Firefox, not sure what that was about
Suspend works (which I’ve gotten out of the habit of using) but Resume still doesn’t—C’MON, this is important!
Still seeing green cursor ghosting in Gimp (this only seems to occur on my ThinkPad X23)
The native Gnome widgets in Firefox are cool, but the corners of Gnome’s slightly round buttons look funky on non-gray backgrounds
The thumbnail for a transparent png on the desktop now has a border, guess it’s time to retire my collectible thumbnail icon
Text in Terminal looks a little fuzzy
Other observations:
When creating links in my WordPress post in Firefox 3, the pre-populated “http://” does not always get pre-selected, leading to potential “http://http://example.com/” errors from pasting in URLs
Firefox 3 appears to be using new delimiter rules for breaking long lines of text (like URLs) across lines in textareas, as in they break now, where before they did not
For my minimalist firefox ui which used to just have the back button on the menubar, I had to drag the new combined navigation button set from the navigation toolbar to the menubar
