Getting bit by MySQL Dates

Yesterday morning, Jack sent out a rather perplexing problem to Josh and myself.

Basically, he reported, this query doesn't return any results, despite there being plenty of valid records:

select * from injections where DATE(created_at) = DATE('2008-03-11');

And this query returns the expected records:

select * from injections where DATE(created_at) = DATE('2008-02-18');

The only difference between the two queries is the date provided. Crazy.

But wait, it's gets worse!

This query yields 293 rows (distinct days):

select distinct DATE(created_at) from injections;

While this one only returns 277 rows (days):

select distinct DATE(created_at) from injections where DATE(created_at) = DATE(created_at);

Josh provided some interesting links to MySQL documentation with tidbits like:

"MySQL automatically converts a date to a number if the date is used in a numeric context"

and

"However, this conversion is subject to the following exceptions: ... For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison."

and this one, which I suspect is the culprit:

"A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions."

I emphasized the use of the word "some" in that quote. When an API is sometimes consistent - an oxymoron if I've ever heard one - it should set off the klaxons in your noggin.

We didn't solve the puzzle, but Jack did find a clever resolution:

...it turns out that "created_at LIKE '2008-03-11%'" is about 10% faster.

Rails Scales

Last month, Stewart dropped me a little note...

"I've heard a few people mention that they heard that Rails doesn't scale [...] any suggestions on how we might alleviate their doubt?"

So I rallied the troops (Jack and Josh) and we set out on a little informal fact-finding mission. Here's what we've come up with thus far...


How 7 Mongrels Handled a 550k Pageview Digging

Not a lot of in-depth analysis here, just an impressive scaling success story with a pretty bar chart of the traffic load and an itemization of the hardware and software involved along with costs.


It's boring to scale with Ruby on Rails

Granted, this one is written by the man himself, Mister Rails, so you have to take it with a grain of salt, but he hits upon the common themes, which are caching, load balancing, and:

"Scaling the database is the `hard part'"

Unfortunately he gets a little off track in the second half of the essay and goes into the cost analysis of productivity and happiness, which is where you'll lose the attention of most investors.


Scaling Twitter: Making Twitter 10000 Percent Faster

I love the High Scalability blog; it's the dessert of my weekly reading. Granted Twitter still has its problems to this day, but you can't deny its monumental achievement in wide-spread adoption and ungodly levels of traffic. In addition to the aforementioned staples of caching, load balancing, and database optimization, they add partitioning and queueing to the mix.


Engine Yard Bets Big on Rubinius

What does this have to do with the scalability of Rails? Well nothing explicit in the article, but it supports two important movements that will eventually lead to a greater good.

First of all, Rubinius is one of three now mainstream Ruby implementations (the other two being Matz's original and Sun's JRuby). Competition here is good. This means better, faster, more stable Ruby environments, which will translate into a faster and more stable Rails.

Secondly, Rubinius is eventually going to lead to mod_rubinius, which will lead to even more scalability of the Rails platform by allowing us to leave separate single-process servers (like Mongrel) on the wayside.

Personally my money is on JRuby (and Glassfish) for the long haul; if you haven't checked out Glassfish yet, you're doing yourself a disservice!


Benchmark Invests in RoR Provider

Pretty much a no-brainer here. Benchmark Capital invested $3.5 million into Engine Yard, whose main business is Rails hosting.


Riding the Rails with WebSphere

If Glassfish is a little too cutting-edge for you, and you're all about being "enterprisey", then IBM's WebSphere is probably right up your alley.

At this point, between IBM and Sun's support of Ruby and Rails, and the multi-million dollar investments other Rails-based ventures are receiving, I'm beginning to wonder how savvy are these potential investors Stewart is getting flak from.

But we're not done yet...


Can Rails Scale? Absolutely!

This article is a pretty good round-up of the more recognizable names using Rails, such as Yellow Pages, Basecamp, and the infamous Twitter, as well as a rehashing of the same old story: caching, spreading the load, and tuning your database.


Friends for Sale Architecture - A 300 Million Page View/Month Facebook RoR App Todd Hoff's picture

Here we are again at my favorite feed, High Scalability, with another Rails scalability success story. This time it's a Facebook app, and a rather popular one at that. I won't spoil the surprise for you, because their is no surprise -- it's the same old story: cache, distribute, and:

"The most important thing we learned is that your scalability problems is pretty much always, always, always the database."

So in summary, there's plenty of good ammunition out there against the naysayers, and soon there will be one more when AdPickles launches and sets a whole new bar by which Rails scalability will be measured. No pressure Jack and Josh.

Hyphenated URLs in Rails

The Problem

Rails, by default, produces underscored URLs for multi-word methods and controllers. For example, a controller by the name of ContactUs with the method about_advertising will respond, with the default routing, to the path /contact_us/about_advertising. This is all well and good except when it comes to search engines. Underscores are seen, at least by Google at the moment, as part of a word, whereas hyphens (dashes) are seen as word separators. This means that our above path will hit for searches on "contact_us" and "about_advertising" but not for "contact" or "advertising". That's no good! How can we get Rails to use /contact-us/about-advertising so that our site might get indexed for the keyword "advertising"?

Action Names

Fixing the action name is easy enough as hyphens are valid characters in method names. Well, almost-- hyphens may be valid in method names, but they are not valid in symbols, which means that we can't use the standard "def" syntax to declare methods with hyphens in their names. To get around that, we turn to define_method. To change /contact_us/about_advertising to /contact_us/about-advertising, we can change the method declaration from:

def about_advertising
...
end

to:

define_method('about-advertising') do
...
end

Controller Paths

It looks like we are halfway to where we want to go, but the controller part of the path turns out to be a more difficult part of the problem. There is no way, at least that I know of, to use an underscore in a class name. Therefore, we turn to Rails routing for some help. The easiest solution is to create a special route for the ContactUs controller in config/routes.rb as follows:

ActionController::Routing::Routes.draw do |map|
map.connect '/contact-us/:action/:id', :controller => 'contact_us'
end

and we get /contact-us/about-advertising. And we're done! Well, not quite. Having to add route entries for every multi-word controller is no fun, certainly not very DRY, and doesn't handle the RESTful map.resources. Enter hyphenated_controller_routes, the plugin I wrote to address those problems, and the reason for this blog post.

Hyphenated Controller Routes

The plugin, located at http://svn.vickeryj.com/public/hyphenated_controller_routes/trunk can automatically create hyphenated routes for all multi-word controllers, and can update map.resources to generate hyphenated URLs for multi-word resources. After installing the plugin, update config/routes.rb to make use of map.add_hyphenated_routes() and map.use_hyphenated_resources() as desired.

An example config/routes.rb:

ActionController::Routing::Routes.draw do |map|

#a resource that will be mapped with an underscore
map.resources :non_hyphenated_resources

#turn on hyphenated resources
map.use_hyphenated_resources()

#all following resources will use hyphens (contact-us)
map.resource :contact_us

# add hyphenated routes with higher priority than the default routes
map.add_hyphenated_routes

# Install the default routes
map.connect ':controller/:action/:id.:format'
map.connect ':controller/:action/:id'

end