Forum: Ruby on Rails Caching unexpected behaviour

Ab0a3817f79012527ea03113d818874b?d=identicon&s=25 Paulo Castro (phlcastro)
on 2013-11-20 08:13
(Received via mailing list)
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
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2013-11-20 08:36
(Received via mailing list)
On 19 November 2013 19:55, Paulo Henrique Leite de Castro
<phlcastro@gmail.com> wrote:
>     - passwd_field
>
> 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
Ab0a3817f79012527ea03113d818874b?d=identicon&s=25 Paulo Castro (phlcastro)
on 2013-11-20 17:21
(Received via mailing list)
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:
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2013-11-20 17:37
(Received via mailing list)
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
Ab0a3817f79012527ea03113d818874b?d=identicon&s=25 Paulo Castro (phlcastro)
on 2013-11-20 17:53
(Received via mailing list)
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:
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2013-11-20 18:00
(Received via mailing list)
On 20 November 2013 16:52, Paulo Henrique Leite de Castro
<phlcastro@gmail.com> wrote:
> 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
Ab0a3817f79012527ea03113d818874b?d=identicon&s=25 Paulo Castro (phlcastro)
on 2013-11-20 18:10
(Received via mailing list)
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:
Ab0a3817f79012527ea03113d818874b?d=identicon&s=25 Paulo Castro (phlcastro)
on 2013-11-20 18:41
(Received via mailing list)
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:
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2013-11-20 19:14
(Received via mailing list)
On Wed, Nov 20, 2013 at 8:52 AM, Paulo Henrique Leite de Castro
<phlcastro@gmail.com> 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@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2013-11-20 22:45
(Received via mailing list)
On 20 November 2013 17:40, Paulo Castro <phlcastro@gmail.com> wrote:
>
> 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
Aa082c8b00a50928e5860dcd70bf2368?d=identicon&s=25 tamouse m. (tamouse_m)
on 2013-11-21 10:35
(Received via mailing list)
Paulo,

You might want to check out Ryan Biggs "Multitenancy With Rails"

https://leanpub.com/multi-tenancy-rails
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.