Database Query Caching in Rails 2.0

I have just encountered some pain related to what is referred to as query caching in Rails 2.0. The database I am dealing with in staging and production is Microsoft SQL Server (I sure hope that Sybase is still making some sort of money off Microsoft for providing them with a leg up on database server development).

The first problem I ran into was with an application that worked absolutely-just-fine-thank-you-very-much under Rails 1.2. I updated it to Rails 2.0 to take advantage of multiple view paths, ran all my tests, made adjustments, ran all my tests again, and everything looked good — against the local MySQL database server I use for development and testing.

Upon deploying the application to staging (with MS SQL Server as the database), I started to encounter some really weird behavior related to the data being inserted into the database. After some snooping, I discovered something new in the log file. It was a SQL statement that was prefixed with the word CACHE. When I noticed the SQL statement that was ostensibly hitting cache instead of the database, then the weird behavior started to make sense:

CACHE (0.000000) SELECT @@IDENTITY AS Ident

This is the SQL statement you issue in order to find out the value of the ID that was assigned to the most recent INSERT statement. So, even though each INSERT is supposed to clear cache, I was seeing this particular statement hitting cache and thus returning a stale and incorrect value for the most recent INSERT. This wasn’t working out so well for creating new rows for my has_many relationships.

Request #0 for query caching: Improve the documentation and provide more example code. I am confused over whether or not hitting cache for the above SELECT statement directly after an INSERT is a defect or a feature.

Request #1 for query caching: Allow a connection adapter to specify statements, perhaps using regular expressions, that should NOT be cached EVER.

So, I discovered that there was support for explicitly turning off query caching for a particular code block, but I was unclear on specifically how it should be done:

Model.uncached do...

Model.uncached do, e. g. Order.uncached do....

I finally decided the most effective would be to hit up the connection directly:

ActiveRecord::Base.connection.uncached do...

This solved the problem for creating the one set of relationships. I thought I was done. Then stuff elsewhere starting acting weird. Sure enough, additional investigation of the logs yielded my old friend and troublemaker:

CACHE (0.000000) SELECT @@IDENTITY AS Ident

So, given the time pressure, it seemed like the most effective treatment for this problem would be simply to extinguish query caching for the entire application. This would be an acceptable solution. Unfortunately, there is apparently no easy way to do this. I tried creating an initializer that literally disabled query caching in my connection:

ActiveRecord::Base.connection.query_cache_enabled = false

Unfortunately, I was foiled by ActionController which goes ahead and runs all actions inside of a block passed into ActiveRecord::Base.cache. Consequently, my final solution was simply to disinclude SqlCache from my controllers directly at the beginning of action_controller/caching.rb (line 18 for those of you keeping score).

Request #2 for query caching: Make it dead simple to enable/disable query caching for the entire application without affecting any other caching Rails is doing.

This cache fun made me think that, although this is a unique case where a specific SQL statement should NOT EVER be cached, there are situations where you probably want to fine tune caching on a per-model basis. A simple and ubiquitous example is one where your Rails app is not the actual source of data, but it does display the data. In this case, you never want to cache your query results — you always want them going to the database. I remember from days of old with JBoss that they had several caching strategies per entity bean. Perhaps we can learn something from either that or whatever the newer thinking is for caching that comes along for the ride with Hibernate / EJB 3.0.

Request #3 for query caching: Allow query caching to be enabled/disabled on a per-model basis.

If you have gotten this far, I’m sure you’re thinking, “Great, Michael; what are you going to do about it?” If anyone can point me to the appropriate person on the Rails team — core or extended — whom I should work with on these issues, I am happy to contribute.

Xcode View Project Window Shortcut

Yes, it would probably help if I spent more quality time working with Xcode, especially since I’ve been using it for probably four years now. Nevertheless, I just discovered an abundantly useful command key shortcut for when you’re buried in windows and just want to see the main project window: ⌘0 (command-zero) brings the main project window to the front. This equates to selecting the “Project” item from the “Project” menu.

Ubuntu, Samba, and Filenames Containing Unicode

I have a share on Windows that is mounted to Ubuntu. The filenames on Windows contain Unicode characters. When mounted to Ubuntu, the “special” characters appeared as question marks.

The solution to this problem required a lot of research, but in the end turned out to be straightforward:

In the entry for mounting the share in /etc/fstab:

  1. Make sure the file system type is “cifs” not “smbfs.”
  2. Add the following option: iocharset=utf8

Running ActiveRecord Tests

I have the occasion to need to extend ActiveRecord to support inserting and updating to nvarchar columns in MS SQL Server.  In order to accomplish this, I wanted to be able not only to run but also to extend the existing AR tests against SQL Server. So, I edited my ODBC data sources in /etc/odbc.ini and added a new connection underneath activerecord/test/connections for my database. Then, I needed to run the test/fixtures/db_definitions/sqlserver.sql script to have the tables in place. This wasn’t working out of the box with sqsh because of the format of the SQL script with semicolons as statement terminators. To solve this problem, I added the -L semicolon_hack=1 option to my sqsh command line, and the script ran no problem.

$ sqsh -U user -P password -S server -D database -L semicolon_hack=1 -i sqlserver.sql

A Short Lesson in Unicode, nvarchar, and MS SQL Server

Even if you have a column in a table that is specified as nvarchar, you still need to tip off SQL Server that you are handing it a string that contains Unicode when you’re setting values in the column. Witnesseth:


1> create table interjunk(id int not null, inter nvarchar(255))
2> go
1> insert into interjunk(id, inter) values (1, 'Unicode string ψ')
2> insert into interjunk(id, inter) values (2, N'Unicode string ψ')
3> go
(1 row affected)
(1 row affected)
1> select * from interjunk
2> go -m vert
id: 1
inter: Unicode string ?

id: 2
inter: Unicode string ψ

(2 rows affected)

Can someone PLEASE explain…

… why We think “Chasing Cars” by Snow Patrol and “Hey there, Delilah” (punctuation added for correctness) by the Plain White T’s are such awesome songs they have to be played CONSTANTLY EVERYWHERE?  Shit, they only have maybe 10 unique notes if you count both songs together!  If there were ever two songs that made me change the radio station when they came on, these are they.

Capistrano Lazy Evaluation

I setup a new environment in one of my Rails projects so that it would deploy to the same staging machine but to a different directory. Consequently, I did the following inside a task to override the default value:

set :deploy_to, "/var/apps/different/directory"

This caused the first half of the deployment to work fine (svn checkout to the correct directory, for example), but the symlink failed and then things went to hell after that.

I ran across this discussion on the Capistrano list, and decided to change the default setting of :deploy_to as follows:

set(:deploy_to) {"/var/apps/different/directory"}

Still problematic. But I noticed that references to #{shared_path} were correct by #{current_path} were not. That’s when I noticed this comment. That’s exactly what my problem was. I modified the deployment to set the mongrel_conf value as prescribed.

set(:mongrel_conf) {"#{current_path}/config/mongrel_cluster.yml"}

And it worked fine.