rails/rails — Issue #5121: Prepared statements leak when using model.association#find
Author: dbalatero
Created: 2012-02-21 21:30:26+00:00
State: open

When you call `.find` on a `has_many` association, the database adapter uses prepared statements. However, this has the unintended side effect of creating a new prepared statement per model, as the association `WHERE` clause does not use bind variables.

This had the unfortunate side-effect of generating these messages in my production app (MySQL 5.1, Rails 3.1.3) after a lengthy enough uptime:

```ruby
ActiveRecord::StatementInvalid: Mysql::Error: Can't create more than max_prepared_stmt_count statements (current value: 16382): SELECT `articles`.* FROM `articles` WHERE `articles`.`id` = ? LIMIT 1
```

Since MySQL caps the number of prepared statements to prevent flood attacks, if your app accidentally leaks prepared statements, it will eventually hit the `max_prepared_stmt_count` limit.

Here's the basic test case:

```ruby
class Article < ActiveRecord::Base
has_many :drafts
end

class Draft < ActiveRecord::Base
end
```

This sample code:

```ruby
article = Article.find(params[:id])
article.drafts.find(10)
```

generates the following SQL:

```sql
SELECT `drafts`.* FROM `drafts` WHERE `drafts`.`article_id` = 2 AND `drafts`.`id` = ? LIMIT 1
```

and passes in `[10]` as bind variables. Unfortunately, this generates a new prepared query for each available `Article` in the database that gets hit in this manner, as the `WHERE `drafts`.`article_id` = 2` portion is hard-coded into the query.

I spoke to @tenderlove about this yesterday, and he mentioned wanting to enhance the visitor (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb#L5-11) to take in the bind parameters and apply them as part of `#to_sql`.

A separate ticket might be worth opening as well for completely disabling prepared statements for those who don't want them on, or for those who need to temporarily disable them to deal with issues like this in production.