# \[PATCH\] Support for DB Clusters/Replication in ActiveRecord

**URL:** <https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643>\
**Category:** Rails\
**Created:** [July 24, 2006, 6:16pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643 "2006-07-24T18:16:37Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 24, 2006, 6:16pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/1 "2006-07-24T18:16:37Z")

</div>

Hello,

I am still dressing this patch up for prime time but I would like to  
get  
some feedback from the community on the approach. This currently does  
pass  
a full “rake”

I have done some extensive work on active record to allow for databases  
to  
be defined in terms of “connection pools” rather than simply one  
database.  
Most of the work was completed in connection\_specification.rb and is  
done at  
this level to be compatible with any backend you choose. Also this  
patch  
maintains full compatibiltity with the current paradigm so no changes  
are  
necessary to current applications/documentation.

Defining a database pool is very easy and follows a common sense  
convention. I will assume you are running with RAILS\_ENV=development  
for  
this example. You first define your connections as you always have in  
database.yml . Your are then able to able to define  
development\_read\_pool: db1, db2, db3  
development\_write\_pool: db4, db5, db6 (In general, the name is  
RAILS\_ENV\_write\_pool so you can test your clusters in development and  
production with no config changes)

where each is a set of connection names that you have already defined.  
ActiveRecord::Base.connection will then return connections from the  
appropriate pool using round-robin when more than one connection is  
available to it. This is handy if you hvae a high traffic website for  
example and you want to load balance over several slave servers for  
reading  
while writing to the one master server consistently. The syntax for  
.connection is as follow

ExampleModel.connection # default “compatibility behavior”  
always  
returns a write connection.  
ExampleModel.connection(:read) # Return a connection from the read  
pool  
ExampleModel.connection (:write) # Return a connection from the write  
pool

I have also changed a few of the functions in base.rb to utilize the  
correction pool (for example, find\_by\_sql calls connection(:read)) This  
again makes the patch seamless to end applications while allowing them  
to  
use the new functionality.

A patch aganist the lastest CVS is attached. Some rough notes on  
implementation are also include below - there not 100% complete but give  
a  
good idea of what was changed.

Thanks,  
Stephen Blackstone

* * *

Implementation:

1. Added two arrays to act as pools for connections related to this  
connection.

@@read\_connection\_pool = {}  
@@write\_connection\_pool = {}

Each is later defined as an array such that

```
@@read_connection_pool[name] represenets all of the read connections

```

available to the current  
class. This allows us to stay fully backward compatiable with  
the  
old methodology where name  
is @active\_connection\_name

1. Define 2 variables to track index of last used connection. This is  
used  
when doing round-robin

2. Define a function for appending connections to pools  
(append\_spec\_to\_connection\_pools spec).  
If the config has an attribute read\_only == true then it is only  
entered  
into the read\_connection\_pool  
and vice versa for the write\_connection\_pool. @@defined\_connections  
also is defined at the same time.

3. Define a function establish\_connection\_pools which looks for two  
variables to be  
set in the configuration:

4. Define a function clear\_connection\_pool which clears the connection  
pools.

5. Modify establish\_connection  
a. When passed nil, call establish\_connection\_pools(RAILS\_ENV)  
b. When passed a ConnectionSpecification, clear all connection  
pools as  
well as the  
active\_connection\_name

6. Move code from establish connection to ConnectionSpecification  
constructor so an object can be made  
out of any spec thats passed to it. (Avoiding breakage of DRY  
principle)

7. Remove settings @@defined\_connections[name] in establish\_connection.  
This is now done by  
calling

8. Define two functions, round robin read and round robin write which  
returns AbstractAdapters from the various pools.

9. Modify retreive connection so that it takes an id of an object that  
should be in @@defined\_connections  
Remove it’s dependency on connection= as this will break things.

10. Modify connection= to call establish\_connection on  
ConnectionSpecification.

11. Moidfy self.remove\_connection to call clear\_connection\_pool

12. Modify active\_connection\_name to check if a pool exists instead of  
defined\_connections

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 24, 2006, 7:43pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/2 "2006-07-24T19:43:38Z")

</div>

On Jul 24, 2006, at 9:13 AM, Stephen wrote:

> Defining a database pool is very easy and follows a common sense  
> convention. I will assume you are running with  
> RAILS\_ENV=development for this example. You first define your  
> connections as you always have in database.yml . Your are then  
> able to able to define  
> development\_read\_pool: db1, db2, db3  
> development\_write\_pool: db4, db5, db6 (In general, the name is  
> RAILS\_ENV\_write\_pool so you can test your clusters in development  
> and production with no config changes)

This is very cool, Stephen.

Couldn’t this be read\_pool, and write\_pool, within the current block  
of YAML?

–  
– Tom M.

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 24, 2006, 7:50pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/3 "2006-07-24T19:50:05Z")

</div>

Here is an example. I considered the merits of allow an attribute like  
pools: p1,p2,p3 but I think this makes more sense  
from a configuration standpoint.

It would look something like this;

development:  
adapter: mysql  
database: project\_development  
username: someuser  
password: happyland  
host: 10.0.0.1  
port: 3306

development2:  
adapter: mysql  
database: project\_development  
username: root  
password: somethingelse  
host: 10.0.0.2  
port: 3306

development\_write\_pool: development  
development\_read\_pool: development,development2

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 25, 2006, 11:16pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/4 "2006-07-25T23:16:10Z")

</div>

Interesting. I’ll take a look this evening. Please let me know if  
you find out any more.

Tom

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 25, 2006, 4:15pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/5 "2006-07-25T16:15:28Z")

</div>

Hi,

I’ve tried creating a new ticket for this issue, but I’m just getting  
500 server errors.

There appears to be a problem with the ActiveRecord SqlServer adapter  
that results in illegal SQL being generated…

ActiveRecord::StatementInvalid in ArticlesController#index  
DBI::DatabaseError: 37000 (170) [Microsoft][ODBC SQL Server Driver][SQL  
Server]Line 1: Incorrect syntax near ''.: SELECT \* FROM (SELECT TOP 1  
\*  
FROM (SELECT TOP 1 id FROM contents WHERE (published = 1 AND  
contents.published\_at \< ‘2006-07-25 11:59:31’ AND blog\_id = 1) AND (  
(contents.[type] = ‘Article’ ) ) ORDER BY contents.published\_at DESC )  
AS  
tmp1 ORDER BY contents.[published\_at] ASC) AS tmp2 ORDER BY  
contents.[published\_at] DESC

This is due to messed up column quoting. I’ve tracked it down to the  
‘add\_limit\_offset!’ method in sqlserver\_adapter.rb, but I can’t decipher  
enough of what is going on to suggest the fix just yet.

Full stack trace also included below.

Regards,  
Stu

RAILS\_ROOT: ./script/…/config/…  
Application Trace | Framework Trace | Full Trace

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/connection\_ada  
pters/abstract\_adapter.rb:120:in  
`log’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/connection\_ada  
pters/sqlserver\_adapter.rb:485:in  
`select’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/connection\_ada  
pters/sqlserver\_adapter.rb:233:in  
`select\_all’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:1166:in  
`select\_limited\_ids\_list’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:1158:in  
`add\_limited\_ids\_condition!’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:1148:in  
`construct\_finder\_sql\_with\_included\_associations’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:1111:in  
`select\_all\_rows’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:975:in  
`find\_with\_associations’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/associations.r  
b:973:in  
`find\_with\_associations’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/base.rb:923:in  
`find\_every’

#{RAILS\_ROOT}/vendor/rails/activerecord/lib/active\_record/base.rb:381:in  
`find' #{RAILS_ROOT}/app/controllers/articles_controller.rb:27:in `index’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/base.rb:910:  
in  
`perform\_action\_without\_filters’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/filters.rb:3  
68:in  
`perform\_action\_without\_benchmark’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/benchmarking  
.rb:69:in  
`perform_action_without_rescue' c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/benchmarking  
.rb:69:in  
`perform\_action\_without\_rescue’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/rescue.rb:82  
:in  
`perform\_action’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/base.rb:381:  
in  
`process\_without\_filters’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/filters.rb:3  
77:in  
`process\_without\_session\_management\_support’

#{RAILS\_ROOT}/vendor/rails/actionpack/lib/action\_controller/session\_mana  
gement.rb:117:in  
`process' #{RAILS_ROOT}/vendor/rails/railties/lib/dispatcher.rb:38:in `dispatch’  
#{RAILS\_ROOT}/vendor/rails/railties/lib/webrick\_server.rb:115:in  
`handle_dispatch' #{RAILS_ROOT}/vendor/rails/railties/lib/webrick_server.rb:81:in `service’  
c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service' c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run’  
c:/ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread' c:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start\_thread’  
c:/ruby/lib/ruby/1.8/webrick/server.rb:95:in `start' c:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start’  
c:/ruby/lib/ruby/1.8/webrick/server.rb:23:in `start' c:/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start’  
#{RAILS\_ROOT}/vendor/rails/railties/lib/webrick\_server.rb:67:in  
`dispatch' #{RAILS_ROOT}/vendor/rails/railties/lib/commands/servers/webrick.rb:59 c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require’

#{RAILS\_ROOT}/vendor/rails/activesupport/lib/active\_support/dependencies  
.rb:147:in  
`require' #{RAILS_ROOT}/vendor/rails/railties/lib/commands/server.rb:30 c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require’  
script/server:3  
Request

Parameters: None

## Show session dump

Response  
Headers: {“cookie”=\>[], “Cache-Control”=\>“no-cache”}

–  
Ticket URL: [http://www.typosphere.org/trac/ticket/1040](http://www.typosphere.org/trac/ticket/1040)  
typo [http://typosphere.org/trac/](http://typosphere.org/trac/)  
web2.0 weblogging engine

> This Email may contain confidential and privileged information and is intended for the use of the addressee(s) only. If you are not the intended recipient please notify the sender and delete the Email from your system. It should not be transmitted to any other person without the consent of the sender. Additional important notifications regarding Email transmissions from and to members of Baring Asset Management can be accessed at [http://www.barings.com/email/index.hcst](http://www.barings.com/email/index.hcst)

&nbsp;

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 26, 2006, 6:03pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/6 "2006-07-26T18:03:48Z")

</div>

I think I’ve managed to fix the column quoting issue by making three  
small changes to the add\_limit\_offset! method in the  
sqlserver\_adapter.rb file:

```
  def add_limit_offset!(sql, options)

    if options[:limit] and options[:offset]
      total_rows = @connection.select_all("SELECT count(*) as

```

TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, “SELECT#{$1} TOP  
1000000000”)}) tally")[0][:TotalRows].to\_i  
if (options[:limit] + options[:offset]) \>= total\_rows  
options[:limit] = (total\_rows - options[:offset] \>= 0) ?  
(total\_rows - options[:offset]) : 0  
end  
sql.sub!(/^\s\*SELECT(\s+DISTINCT)?/i, "SELECT \* FROM (SELECT  
TOP #{options[:limit]} \* FROM (SELECT#{$1} TOP #{options[:limit] +  
options[:offset]} “)  
sql \<\< “) AS tmp1”  
if options[:order]  
options[:order] = options[:order].split(’,’).map do |field|  
parts = field.split(” ")  
tc = parts[0]  
if sql =~ /.[/ and tc =~ /./ # if column quoting used  
in query

```
            ####-- ORIGINAL : tc.gsub!(/\./, '\\.\\[')
            ####-- ORIGINAL : tc << '\\]'

            tc.gsub!(/\./, '.[') #NEW: -- do not escape

```

column quotes  
tc \<\< ‘]’ #NEW: – do not  
escape column quotes

```
          end
          if sql =~ /#{tc} AS (t\d_r\d\d?)/
            parts[0] = $1

          ####-- ORIGINAL : elsif parts[0] =~ /\w+\.(\w+)/
          elsif parts[0] =~ /\w+\.([\[\]\w]+)/

```

#NEW: ensure that column quotes are picked up when matching to strip the  
tablename

```
            parts[0] = $1
          end
          parts.join(' ')
        end.join(', ')
        sql << " ORDER BY

```

#{change\_order\_direction(options[:order])}) AS tmp2 ORDER BY  
#{options[:order]}"  
else  
sql \<\< " ) AS tmp2"  
end  
elsif sql !~ /^\s_SELECT (@@|COUNT()/i  
sql.sub!(/^\s_SELECT(\s+DISTINCT)?/i) do  
“SELECT#{$1} TOP #{options[:limit]}”  
end unless options[:limit].nil?  
end  
end

This has fixed the column quoting issue, but there are still some  
problems with the SQL that ActiveRecord is generating. The original  
issue of selecting by offset now works…

```
Article.find( :all,
  	:offset => 0,
      :limit => 1,
  	:order => "contents.published_at DESC",
      :conditions => ['published = ? AND contents.published_at <

```

? AND blog\_id = ?’,true, Time.now, 1])

```
[#<Article:0x32acb34 @attributes={... (snip)

```

And selecting with the :include tag works…

```
Article.find( :all,
      :order => "contents.published_at DESC",
  	:include => [:categories, :tags, :user, :blog],
      :conditions => ['published = ? AND contents.published_at <

```

? AND blog\_id = ?’,true, Time.now, 1])

```
[#<Article:0x3848110 @user=#<User:0x38476fc @attributes={...

```

(snip)

But a find that combines both the :offset and :include will fail due to  
bad SQL…

Article.find( :all,  
:offset =\> 0,  
:limit =\> 1,  
:order =\> “contents.published\_at DESC”,  
:include =\> [:categories, :tags, :user, :blog],  
:conditions =\> [‘published = ? AND contents.published\_at \< ? AND  
blog\_id = ?’,true, Time.now, 1])

ActiveRecord::StatementInvalid: DBI::DatabaseError: S0022 (207)  
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name  
‘published\_at’.: SELECT \* FROM (SELECT TOP 1 \* FROM (SELECT TOP 1 id  
FROM contents WHERE (published = 1 AND contents.published\_at \<  
‘2006-07-26 12:24:01’ AND blog\_id = 1) AND ( (contents.[type] =  
‘Article’ ) ) ORDER BY contents.published\_at DESC ) AS tmp1 ORDER BY  
[published\_at] ASC) AS tmp2 ORDER BY [published\_at] DESC  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/connection\_adapters/abstract\_adapter.rb:120:in `log' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_rec ord/connection_adapters/sqlserver_adapter.rb:521:in`select’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/connection\_adapters/sqlserver\_adapter.rb:257:in `select_all' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_rec ord/associations.rb:1166:in`select\_limited\_ids\_list’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/associations.rb:1158:in `add_limited_ids_condition!' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_rec ord/associations.rb:1148:in`construct\_finder\_sql\_with\_included\_associations’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/associations.rb:1111:in `select_all_rows' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_rec ord/associations.rb:975:in`find\_with\_associations’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/associations.rb:973:in `find_with_associations' from ./script/../config/../config/../vendor/rails/activerecord/lib/active_rec ord/base.rb:923:in`find\_every’  
from  
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active\_rec  
ord/base.rb:381:in `find’  
from (irb):104  
from :0

This one is caused by just selecting the [id] in the inner select, and  
not exposing the the [published\_at] column to be sorted on in the outer  
selects. Sadly I haven’t been able to get anywhere with debugging this  
one. Also, please note that this is being run against the Typo 4  
database, and the test are run via the script/console util. The issues  
do also occur through the web frontend.

Regards,  
Stu

---

<div class="post-metadata">

**Author:** ![stephen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@stephen](https://www.ruby-forum.com/u/stephen)\
**Post date:** [July 26, 2006, 8:33pm UTC](https://www.ruby-forum.com/t/patch-support-for-db-clusters-replication-in-activerecord/99643/7 "2006-07-26T20:33:42Z")

</div>

Thanks for the extra info. I hate that limit/offset thing, cause of  
all my sorrow.

Tom