Master SQL syntax is used on all shards

[pelizza]

In our current application we have a master wich is a PostgreSQL database and two shards wich are on Ms SQLServer. The problem we are facing is that the PostgreSQL syntax is being used on the queries performed on the shards.

We confirmed this behavior by setting the master as one a SQL Server too.

Here is the query performed on a shard (SQLServer database) generated when a PostgreSQL adapter is master:
SELECT [FASE2].* FROM [FASE2] JOIN fase2_enviar ON fase2.id = fase2_enviar.id LIMIT 50

And here is the 'correct' query when all adapters are SQLServer:
SELECT TOP(50) [FASE2].* FROM [FASE2] JOIN fase2_enviar ON fase2.id = fase2_enviar.id

[thiagopradi]

Hi @pelizza,

Can you provide a failing test for this case? the Octopus spec suite have configuration for running different shards using different databases.

btw, What is the query that is going wrong?

Thanks,

Thiago.

[thms]

I have a similar / exactly the same issue: My master DB is MySQL, and I am trying to use shards to connect to a number of MS SQL servers (legacy environments) via tiny-tds.
I see the same behaviour. Queries that use LIMIT in MySQL and should use TOP N for Microsoft use the wrong syntax.

[thiagopradi]

Yep, MSSql adapter is pretty complicated. This is a issue between the MSSql adapter and Octopus. I will keep this issue open for future investigation, but keep in mynd that in extreme situations you could rewrite using find_by_sql.

Thanks,

Thiago

[thms]

Hi Thiago,

I found a number of issues and some work arounds. Will post both
during the weekend.

Thomas

On Wed, Dec 14, 2011 at 10:18 AM, Thiago Pradi
reply@reply.github.com
wrote:

Yep, MSSql adapter is pretty complicated. This is a issue between the MSSql adapter and Octopus. I will keep this issue open for future investigation, but keep in mynd that in extreme situations you could rewrite using find_by_sql.

Thanks,

Thiago

---

Reply to this email directly or view it on GitHub:
#48 (comment)

[thms]

Hi Thiago,
Here is the simplest example I can create that goes wrong.
Rails: 3.1, Freetds 0.91, TinyTDS: 0.5.0, ARSQL Server Adapter: 3.1.4, Octopus: today's version from github.

Two databases: one sqlite for users and stuff, on SQL Server for orders and stuff, these will be sharded across many in production.
database.yml:

core_development:
adapter: sqlite3
database: db/development.sqlite3
pool: 5
timeout: 5000

development:
adapter: sqlserver
mode: DBLIB
host: 192.168.239.140
port: 1433
username: xxx
password: xxx
database: oms_staging_development
timeout: 5000

shards.yml

octopus:
environments:
- development

development:
    master:
        adapter: sqlite3
        database: db/development.sqlite3
        pool: 5
        timeout: 5000

    core:
        adapter: sqlite3
        database: db/development.sqlite3
        pool: 5
        timeout: 5000

    stella:
        adapter: sqlserver
        mode: DBLIB
        host: 192.168.239.140
        port: 1433
        username: xxx
        password: xxx
        database: oms_staging_development
        timeout: 5000
        verify_connection: true

order.rb:
class Order < ActiveRecord::Base

Use in development to overcome some oddities...

#octopus_establish_connection Octopus.config[Rails.env]['stella']
using(:stella)

SQL Server table name and primary id column for Tiny TDS

octopus_set_table_name 'Order'
set_primary_key :order_id

alias_attribute :id, :order_id

end

run all tests in console:
test 1:

Order.using(:stella).first
EXECUTE (0.6ms) USE [oms_staging_development]
EXECUTE (0.5ms) USE [oms_staging_development]
Order Load (1.3ms) EXEC sp_executesql N'SELECT TOP (1) [Order].* FROM [Order]'
ActiveRecord::StatementInvalid: Could not find table 'Order'
from /Users/Thomas/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.0/lib/active_record/connection_adapters/sqlite_adapter.rb:387:in `table_structure'
... it is using the sqlite adapter instead of the sql server adapter.
...

test2:
Octopus.using(:stella) {Order.first}
Order Load (2.2ms) EXEC sp_executesql N'SELECT TOP (1) [Order].* FROM [Order]'
ActiveRecord::StatementInvalid: Could not find table 'Order'
from /Users/Thomas/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.0/lib/active_record/connection_adapters/sqlite_adapter.rb:387:in `table_structure'
...
Same thing, tries to use the wrong adapter.

It works if I change shards.yml so that the SQL server adapter is the master:
octopus:
environments:
- development

development:
    not_master:
        adapter: sqlite3
        database: db/development.sqlite3
        pool: 5
        timeout: 5000

    master:
        adapter: sqlserver
        mode: DBLIB
        host: 192.168.239.140
        port: 1433
        username: rails
        password: ruby
        database: oms_staging_development
        timeout: 5000
        verify_connection: true

    core:
        adapter: sqlite3
        database: db/development.sqlite3
        pool: 5
        timeout: 5000

    stella:
        adapter: sqlserver
        mode: DBLIB
        host: 192.168.239.140
        port: 1433
        username: rails
        password: ruby
        database: oms_staging_development
        timeout: 5000
        verify_connection: true

Of course that then screws up any and all accesses to the sqlite shards.

I found another odditity:
When I run the exact same setup on thin, instead of the console, I can access the shards correctly using the Octopus.using(shard) {block} methods.

In other configurations, it tries to connect to the right shard but with the wrong syntax, e.g. trying to use Limit and Offest on SQL server instead of TOP N, or trying to escape strings with N'some-text' on MySQL ans sqlite (which should only happen on SQL Server....

Thomas

[thms]

And one more hint. If I use the first version of the shards.yml file (where SQL server is not the master) and I run this in the console:
Order.octopus_establish_connection Octopus.config[Rails.env]['stella']
Order.connection_proxy.current_shard = :stella
Order.first
It works and returns an actual order.
Note that I only need to do this in the console, not when I am running the application on thin.

[grigio]

+1
I've added it to my user module:

class User < ActiveRecord::Base
octopus_establish_connection "admin_#{Rails.env}"
connection_proxy.current_shard = :admin # :master otherwise
end

And User.first works fine in deelopment, but I get a similar error in production maybe because rails doesn't reload the classes in this environment.
Is there a workaround to have at the same time Models which refers to different DBs?

[thiagopradi]

Hi guys,

Can you guys confirm if this commit (821c330) solves your issues?

Thanks.

[thiagopradi]

I'm going to close this issue, since I think it's fixed on master.

Thanks