cookpad / arproxy Goto Github PK

mysql2でAとBのdatabaseに接続しており、Bでクエリーを発行すると
何故かarproxy内でAに対してクエリーが発行されエラーになりました。

間違ってたらスイマセン

I am encountering an issue with Rails version 7.1.3.2, where certain expected behaviors are not functioning as anticipated. Specifically, the use of ActiveRecord::Base.connection.execute seems to trigger the proxy execution correctly, as demonstrated below:

irb(main):001> ActiveRecord::Base.connection.execute('select * from users limit 1')
   [writing] (3.9ms)  select * from users limit 1

However, when attempting to use other ActiveRecord methods such as find or where, the proxy does not appear to be executed. For instance:

irb(main):003> User.find(1)
  User Load (1.4ms)  SELECT `users`.`id`, ...

Upon reviewing the changes made to Rails, it seems that the internal call implementation within execute has been modified to something like internal_execute. This alteration can be observed in the following commit: rails/rails@63c0d6b#diff-88e231c48eb5559ac3be58a024b019e86f7c74d388f5d49fac3f4e47bf851cc6

It is my concern that these changes may have inadvertently affected the ability to execute hooks within execute. I am seeking guidance or confirmation on whether this behavior is indeed a result of the recent changes, and if so, how it might be addressed or worked around.

Hi,

We rarely encountered errors like

Mysql2::Error: This connection is in use by: #<Celluloid::Thread:0x007f415c0262d8 sleep>

when running Sidekiq with Arproxy.

This seems that some Sidekiq threads simultaneously reach the following line and one thread replaces connections for others.
https://github.com/cookpad/arproxy/blob/v0.1.3/lib/arproxy/proxy_chain.rb#L34
Similar errors would be occurred for multi-threaded applications such as one using Puma.

Any support for multi-threaded applications?

I followed the Readonly Access example in the readme and found that updates still managed to be written:

[9] pry(main)> customer.first_name = "Wheeeee"
=> "Wheeeee"
[10] pry(main)> customer.save

From: /Users/xxxxxxx/Development/xxxxxxxx/config/initializers/arproxy.rb @ line 7 Readonly#execute:

    2: def execute(sql, name=nil)
    3:   if sql =~ /\s*(SELECT|SET|SHOW|DESCRIBE)\b/
    4:     super sql, name
    5:   else
    6:     binding.pry
 => 7:     Rails.logger.warn "#{name} (BLOCKED) #{sql}"
    8:   end
    9: end

[3] pry(#<Readonly>)> sql
=> "BEGIN"
[4] pry(#<Readonly>)> c
 (BLOCKED) BEGIN
  SQL (1.3ms)  UPDATE "customers" SET "first_name" = $1, "updated_at" = $2 WHERE "customers"."id" = $3  [["first_name", "Wheeeee"], ["updated_at", "2016-03-04 15:57:59.788986"], ["id", "f86d3094-a36f-499b-92ee-a78a76168750"]]

From: /Users/xxxxxxx/Development/xxxxxxxx/config/initializers/arproxy.rb @ line 7 Readonly#execute:

    2: def execute(sql, name=nil)
    3:   if sql =~ /\s*(SELECT|SET|SHOW|DESCRIBE)\b/
    4:     super sql, name
    5:   else
    6:     binding.pry
 => 7:     Rails.logger.warn "#{name} (BLOCKED) #{sql}"
    8:   end
    9: end

[4] pry(#<Readonly>)> sql
=> "COMMIT"
[5] pry(#<Readonly>)> c
 (BLOCKED) COMMIT
=> true

Any pointers how I could make this truly read-only? I'm using the PostgreSQLAdapter instead of MySQL as you have in your example, if that makes any difference. Thanks!

From Rails 6, ActiveRecord::Base.transaction does not execute BEGIN; and it is executed at the first query of the transaction instead.
rails/rails#32647
it seems that this change cause the process order gap between arproxy and actually executed transaction.

Here is an example.

ActiveRecord::Base.transaction do
  User.find(1)
end

This case seems to be processed as follows

1. initialize transaction but doesn’t execute `BEGIN`
2. call `execute(sql, name)` of `User.find(1)`, the query of which is like "SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1" 
3. before executing the select query, this `execute(sql, name)` will call `materialize_transactions`.
4. `materialize_transactions` execute `BEGIN;`
5. `materialize_transactions` -> `connection.begin_db_transaction` -> `connection.begin_db_transaction` -> execute("BEGIN", "TRANSACTION")
6. back to the `execute(sql, name)` of select query and call `@connection.query(sql)`, which execute select query. 
7. "COMMIT;"

So, from the viewpoint of arproxy(or viewpoint of execute(sql, name)) the order is as follows

1. "SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1;" 
2. "BEGIN;"
3. "COMMIT;"

even though the actual query execution order is

1. "BEGIN;"
2. "SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1;" 
3. "COMMIT;"

I issued because it looks unexpected behavior.
I'm very happy if there is any good idea to avoid it.
Thank you.

Since activerecord 7.0.0, #execute method might take keyword arguments in MySQL adapter, like execute(sql, name, async: false).

This change was added at rails/rails#40037.