Hi
I have this RoR (2.3.16) app that has a simple agenda tool. Each customer
has their own individual database, and I have one central database with
login, passwd and the customer database name.
Something like this:
- main_database:
- users_table:
- login_field
- passwd_field
- database_name_field
- customer_db_1:
- tables ....
- customer_db_2:
- tables ...
So, after user authentication, I set the correspondent database connection
according each customer.
The problem is:
Customer 1 sometimes see records from customer 2, even being on different
databases.
I''m totally lost here... I just suspect that this could be a database
connection cache issue, since the SQL query is the same for every customer
(ie.: select * from agendas;) but the connection string is different for
each one.
Most of the time everything runs ok.. but some customers are getting this
strange behaviour.
Any idea of what could be happening?
Current env:
Passenger: 4.0.21
Apache/2.2.22 (Ubuntu)
ruby 1.8.7 (2012-02-08 patchlevel 358)
Thank you very much in advance for any help!!
Best regards
Paulo
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/ff60e3ef-df0f-4150-b5fc-33c40deea136%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
On 19 November 2013 19:55, Paulo Henrique Leite de Castro <phlcastro-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi > > I have this RoR (2.3.16) app that has a simple agenda tool. Each customer > has their own individual database, and I have one central database with > login, passwd and the customer database name. > Something like this: > > - main_database: > - users_table: > - login_field > - passwd_field > - database_name_field > > - customer_db_1: > - tables .... > > - customer_db_2: > - tables ... > > So, after user authentication, I set the correspondent database connection > according each customer. > > The problem is: > > Customer 1 sometimes see records from customer 2, even being on different > databases. > > I''m totally lost here... I just suspect that this could be a database > connection cache issue, since the SQL query is the same for every customer > (ie.: select * from agendas;) but the connection string is different for > each one. > > Most of the time everything runs ok.. but some customers are getting this > strange behaviour. > > Any idea of what could be happening?Put some debug code in to log stuff in order that you can work out what it going wrong. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAL%3D0gLu4Z3QrK_HJdxzQc%2BUVktkmLGjwZwmf3V%3DKQ5z9r8zeGg%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
Hi Colin thanks for your reply!! I''ll try it and see if I can find more info to help me... I was also wondering where can I find how Rails (specially 2.3.x) deals with sql result caching. This could help to diagnose a possible root cause. Best regards Em quarta-feira, 20 de novembro de 2013 05h34min08s UTC-2, Colin Law escreveu:> > On 19 November 2013 19:55, Paulo Henrique Leite de Castro > <phlc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org <javascript:>> wrote: > > Hi > > > > I have this RoR (2.3.16) app that has a simple agenda tool. Each > customer > > has their own individual database, and I have one central database with > > login, passwd and the customer database name. > > Something like this: > > > > - main_database: > > - users_table: > > - login_field > > - passwd_field > > - database_name_field > > > > - customer_db_1: > > - tables .... > > > > - customer_db_2: > > - tables ... > > > > So, after user authentication, I set the correspondent database > connection > > according each customer. > > > > The problem is: > > > > Customer 1 sometimes see records from customer 2, even being on > different > > databases. > > > > I''m totally lost here... I just suspect that this could be a database > > connection cache issue, since the SQL query is the same for every > customer > > (ie.: select * from agendas;) but the connection string is different for > > each one. > > > > Most of the time everything runs ok.. but some customers are getting > this > > strange behaviour. > > > > Any idea of what could be happening? > > Put some debug code in to log stuff in order that you can work out > what it going wrong. > > Colin >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/19319d85-e1e8-4bb6-ac8d-a1fd5d850c0b%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
On Wednesday, November 20, 2013 4:19:40 PM UTC, Paulo Henrique Leite de Castro wrote:> > Hi Colin > > thanks for your reply!! I''ll try it and see if I can find more info to > help me... > > I was also wondering where can I find how Rails (specially 2.3.x) deals > with sql result caching. This could help to diagnose a possible root cause. > >There is an sql cache in Rails, however it is scoped to the current request - you get a fresh cache for each request. When you say that you are setting the DB connection after authentication, what exactly are you doing (and by authentication do you mean when the user signs in or on every request once you have got a user_id from a session/cookie?) ? Fred> Best regards > > Em quarta-feira, 20 de novembro de 2013 05h34min08s UTC-2, Colin Law > escreveu: >> >> On 19 November 2013 19:55, Paulo Henrique Leite de Castro >> <phlc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> > Hi >> > >> > I have this RoR (2.3.16) app that has a simple agenda tool. Each >> customer >> > has their own individual database, and I have one central database with >> > login, passwd and the customer database name. >> > Something like this: >> > >> > - main_database: >> > - users_table: >> > - login_field >> > - passwd_field >> > - database_name_field >> > >> > - customer_db_1: >> > - tables .... >> > >> > - customer_db_2: >> > - tables ... >> > >> > So, after user authentication, I set the correspondent database >> connection >> > according each customer. >> > >> > The problem is: >> > >> > Customer 1 sometimes see records from customer 2, even being on >> different >> > databases. >> > >> > I''m totally lost here... I just suspect that this could be a database >> > connection cache issue, since the SQL query is the same for every >> customer >> > (ie.: select * from agendas;) but the connection string is different >> for >> > each one. >> > >> > Most of the time everything runs ok.. but some customers are getting >> this >> > strange behaviour. >> > >> > Any idea of what could be happening? >> >> Put some debug code in to log stuff in order that you can work out >> what it going wrong. >> >> Colin >> >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/a7d57fa1-b28d-4bee-902c-852f71fed3d7%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Hi Fred, thanks for your reply!
I''m using authlogic gem to authenticate my users.
Inside my Application Controller I created the following code:
prepend_before_filter :database_connection
and this method has the following code:
ActiveRecord::Base.establish_connection({:adapter => "mysql",
:database =>
"#{current_user.dbname}", :username => "x", :password
=> "x", :host => "x",
:encoding => "utf8"})
So, for each request I get the current authenticated user database name and
set the connection to customer database.
Imagine I have 2 different users requesting the same view:
mydomain.com/agenda/view
Since each user had authenticated using their own username/passwd, each
request will establish a different connection to the database.
But if Rails doesn''t cache database queries for different time
requests,
I''m lost about why this issue is happening.
Do you have any clue?
Best regards
Em quarta-feira, 20 de novembro de 2013 14h36min11s UTC-2, Frederick Cheung
escreveu:>
>
>
> On Wednesday, November 20, 2013 4:19:40 PM UTC, Paulo Henrique Leite de
> Castro wrote:
>>
>> Hi Colin
>>
>> thanks for your reply!! I''ll try it and see if I can find more
info to
>> help me...
>>
>> I was also wondering where can I find how Rails (specially 2.3.x) deals
>> with sql result caching. This could help to diagnose a possible root
cause.
>>
>>
> There is an sql cache in Rails, however it is scoped to the current
> request - you get a fresh cache for each request. When you say that you are
> setting the DB connection after authentication, what exactly are you doing
> (and by authentication do you mean when the user signs in or on every
> request once you have got a user_id from a session/cookie?) ?
>
>
> Fred
>
>
>> Best regards
>>
>> Em quarta-feira, 20 de novembro de 2013 05h34min08s UTC-2, Colin Law
>> escreveu:
>>>
>>> On 19 November 2013 19:55, Paulo Henrique Leite de Castro
>>> <phlc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>>> > Hi
>>> >
>>> > I have this RoR (2.3.16) app that has a simple agenda tool.
Each
>>> customer
>>> > has their own individual database, and I have one central
database
>>> with
>>> > login, passwd and the customer database name.
>>> > Something like this:
>>> >
>>> > - main_database:
>>> > - users_table:
>>> > - login_field
>>> > - passwd_field
>>> > - database_name_field
>>> >
>>> > - customer_db_1:
>>> > - tables ....
>>> >
>>> > - customer_db_2:
>>> > - tables ...
>>> >
>>> > So, after user authentication, I set the correspondent
database
>>> connection
>>> > according each customer.
>>> >
>>> > The problem is:
>>> >
>>> > Customer 1 sometimes see records from customer 2, even being
on
>>> different
>>> > databases.
>>> >
>>> > I''m totally lost here... I just suspect that this
could be a database
>>> > connection cache issue, since the SQL query is the same for
every
>>> customer
>>> > (ie.: select * from agendas;) but the connection string is
different
>>> for
>>> > each one.
>>> >
>>> > Most of the time everything runs ok.. but some customers are
getting
>>> this
>>> > strange behaviour.
>>> >
>>> > Any idea of what could be happening?
>>>
>>> Put some debug code in to log stuff in order that you can work out
>>> what it going wrong.
>>>
>>> Colin
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/d0dd3f11-fe5b-4bb1-9fe7-854bd67fb4b0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
On 20 November 2013 16:52, Paulo Henrique Leite de Castro <phlcastro-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi Fred, thanks for your reply! > > I''m using authlogic gem to authenticate my users. > > Inside my Application Controller I created the following code: > > prepend_before_filter :database_connection > > and this method has the following code: > > ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => > "#{current_user.dbname}", :username => "x", :password => "x", :host => "x", > :encoding => "utf8"})Are you checking for an error return here in case it fails for some reason, leaving the connection as it was for the last request? Also I presume you are not silently absorbing any exception raised there. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAL%3D0gLsF6m4v-T2XHj72Cm6AV7_ey_woiNDg0m%2B7t6ReMwRs%3Dw%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
Hi Colin, I wasn''t able to find any exception referring to this specific line code. But, in case of an exception, Rails would return a 500 error page and not the correct page with someone else records. Am I right? Best regards Em quarta-feira, 20 de novembro de 2013 14h58min40s UTC-2, Colin Law escreveu:> > On 20 November 2013 16:52, Paulo Henrique Leite de Castro > <phlc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org <javascript:>> wrote: > > Hi Fred, thanks for your reply! > > > > I''m using authlogic gem to authenticate my users. > > > > Inside my Application Controller I created the following code: > > > > prepend_before_filter :database_connection > > > > and this method has the following code: > > > > ActiveRecord::Base.establish_connection({:adapter => "mysql", :database > => > > "#{current_user.dbname}", :username => "x", :password => "x", :host => > "x", > > :encoding => "utf8"}) > > Are you checking for an error return here in case it fails for some > reason, leaving the connection as it was for the last request? Also I > presume you are not silently absorbing any exception raised there. > > Colin >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/938a8490-5894-4ef7-94e5-ca1fbbc866bc%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Hi Colin
first of all, thank you very much for your help!! You found the root cause
of my problem.
After user session expired, the code:
ActiveRecord::Base.establish_connection({:adapter => "mysql",
:database =>
"#{current_user.dbname}", :username => "x", :password
=> "x", :host => "x",
:encoding => "utf8"})
didn''t work, but also didn''t throw any exception. So the
requested view got
the last opened database connection to retrieve the records. Of course, the
records retrieved was from another user (the last
successful establish_connection attempt).
Thank you all for the support!!
Best regards
Em quarta-feira, 20 de novembro de 2013 15h09min18s UTC-2, Paulo Henrique
Leite de Castro escreveu:>
> Hi Colin,
>
> I wasn''t able to find any exception referring to this specific
line code.
> But, in case of an exception, Rails would return a 500 error page and not
> the correct page with someone else records. Am I right?
>
> Best regards
>
> Em quarta-feira, 20 de novembro de 2013 14h58min40s UTC-2, Colin Law
> escreveu:
>>
>> On 20 November 2013 16:52, Paulo Henrique Leite de Castro
>> <phlc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> > Hi Fred, thanks for your reply!
>> >
>> > I''m using authlogic gem to authenticate my users.
>> >
>> > Inside my Application Controller I created the following code:
>> >
>> > prepend_before_filter :database_connection
>> >
>> > and this method has the following code:
>> >
>> > ActiveRecord::Base.establish_connection({:adapter =>
"mysql", :database
>> =>
>> > "#{current_user.dbname}", :username => "x",
:password => "x", :host =>
>> "x",
>> > :encoding => "utf8"})
>>
>> Are you checking for an error return here in case it fails for some
>> reason, leaving the connection as it was for the last request? Also I
>> presume you are not silently absorbing any exception raised there.
>>
>> Colin
>>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/edbf9d9d-543d-430d-9536-b6ded6ea58d9%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
On Wed, Nov 20, 2013 at 8:52 AM, Paulo Henrique Leite de Castro <phlcastro-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> prepend_before_filter :database_connection > > and this method has the following code: > > ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => > "#{current_user.dbname}", :username => "x", :password => "x", :host => "x", > :encoding => "utf8"}) > > So, for each request I get the current authenticated user database name and > set the connection to customer database.I would wonder how this plays with connection pooling. In any case, since you''re explicitly opening the connection, I''d add an after_filter to explicitly *release* the connection after using it. And it might be worth testing whether setting the connection pool size to 1 changes the behavior. Not that that''s necessarily a great long-term "solution", but for troubleshooting purposes. :-) HTH, -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org http://about.me/hassanschroeder twitter: @hassan -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACmC4yBtDQuA7YL2hYA4f09b0sjkZFuO%3DX9yAkfoZZTmpAne0Q%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
On 20 November 2013 17:40, Paulo Castro <phlcastro-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi Colin > > first of all, thank you very much for your help!! You found the root cause > of my problem. > > After user session expired, the code: > > ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => > "#{current_user.dbname}", :username => "x", :password => "x", :host => "x", > :encoding => "utf8"}) > > didn''t work, but also didn''t throw any exception.I expect it returned an error (hence my suggestion to check for error return). If you look at the docs for establish_connection you will see that it may throw an exception, but under other circumstances will return an error. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAL%3D0gLsydFpFS%2BGLWveyf9vroY%2BoHf3BYS1%2BE7iMaSn2mBay%2Bg%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
Paulo, You might want to check out Ryan Bigg’s "Multitenancy With Rails" https://leanpub.com/multi-tenancy-rails -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/00BDD647-481D-49AC-905B-B60DF7F3783A%40gmail.com. For more options, visit https://groups.google.com/groups/opt_out.