Comments (9)
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.
from octopus.
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.
from octopus.
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
from octopus.
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
[email protected]
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)
from octopus.
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
from octopus.
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.
from octopus.
+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?
from octopus.
Hi guys,
Can you guys confirm if this commit (821c330) solves your issues?
Thanks.
from octopus.
I'm going to close this issue, since I think it's fixed on master.
Thanks
from octopus.
Related Issues (20)
- Octopus 0.10.2 / Rails 5.1.5 / Sidekiq workers eventually run out of memory and freeze
- Not able to access the wiki page HOT 4
- Config file setting of wiki is outdated? HOT 1
- could not obtain a database connection within 5.000 seconds HOT 2
- Rails 6 migration / compatibility HOT 3
- How does the ActiveRecord connection pool interact with Octopus? HOT 4
- Rails 6 release HOT 6
- Octopus error at startup with Rails 6: `alias_method': undefined method `any?' for class `ActiveRecord::Associations::CollectionAssociation' (NameError) HOT 7
- Update methods sent to read-only database with identity_cache gem
- Sidekiq job random failures due to proxy_config is nil
- octopus for ruby project HOT 1
- Delayed Job + Octopus 0.10.2 issues on Rails 5.2 (undefined method `any?' for nil:NilClass) HOT 2
- ActiveRecord 5.2 StatementCache Pins Queries to Shards
- Octopus is already running
- octopus 0.6.1 make rake unit test failling.. HOT 1
- Would sponsorship help? HOT 2
- I can't able to use gem 'ar-octopus' in rails 6.0.3 HOT 2
- Octopus doesn't work with Rails' schema cache HOT 1
- New Maintainer HOT 4
- Preventing Duplicate Migrations on the Same Shard for Rails > 5.2 and ar-octopus (0.10.2) HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from octopus.