Expected behavior
To export table data from current db connection to CSV
Actual behavior
Table from the default db table is exported.
Context
Rails 6.1.3.1
Forest Liana 6.3.1
We have a rails 6 app, with a mutli db (horizontal sharding) setup.
We have implemented a smart action so the end user can select which db they want to connect to.
Then an around_action callback is performed by overriding ForestLiana::BaseController.
The action has code analogous to:
ActiveRecord::Base.connected_to(role: :writing, shard: shard) do
yield
end
This works well, and ensures the controller action is executed in the context of the correct db connection.
However, when exporting the table data to csv, the default db connection is always used.
Can someone provide insight into this behaviour?
Many thanks
Hello @veritas1,
It seems that your use case is very specific and out of our usual scope.
Are you willing to share your code in order for us to try to understand more and investigate?
Thank you
Yes. What would you like?
Hi @veritas1,
The smart action allowing this DB switch, alongside the ForestLiana::BaseController override you seems to have made might help us dig into this.
We can take a look to try and find something that might have gone wrong with your data export. But by overriding forest base controller and having a smart action dynamically changing your db connection, you’re changing forest’s original behavior and this kind of issues might be out of the scope of what we can help you with.
Best regards
The smart action just writes the user selected db connection to a file.
Here is the base controller:
ForestLiana::BaseController.class_eval do
around_action :set_database_connection
def set_database_connection
yield and return if defined?(forest_user).nil?
user_id = forest_user['id']
yield and return if user_id.nil?
file_path = File.join(Rails.root, DB_CONNECTIONS_FILE)
data = File.read file_path
hash = eval data
user_db_connection = hash[user_id]
yield and return if user_db_connection.nil?
Rails.logger.info "Admin panel connecting to database: #{user_db_connection}"
ActiveRecord::Base.connected_to(role: :writing, shard: user_db_connection.to_sym) do
yield
end
end
end
Thank you for sharing this with us, if I understand well, this configures a custom connection for each user when they are querying on your admin panel. I have two questions then:
- Does that work for the all app except the export (listing values, dashboards, smart actions, etc)?
- When exporting, is the queried DB the one you defined by default through your env variables?
Thank you
Yes. Everything tested so far like listing values, dashboards, smart actions all work as expected e.g. the correct database connection is used.
No. It is not using the database specified in the DATABASE_URL env variable. It is using the first database defined in the config e.g shard1 as per the below.
production: &production
shard1:
<<: *default
database: defaultdb
url: <%= ENV["SHARD_1_DATABASE_URL"] %>
prepared_statements: false
advisory_locks: false
shard2:
<<: *default
database: defaultdb
url: <%= ENV["SHARD_2_DATABASE_URL"] %>
prepared_statements: false
advisory_locks: false
shard3:
<<: *default
database: defaultdb
url: <%= ENV["SHARD_3_DATABASE_URL"] %>
prepared_statements: false
advisory_locks: false
Hello @veritas1,
I’ve dig into our code. The good news: It should work.
To be honest, I don’t see any reason your code is not working on this specific case.
So, can you debug your code and look at the forest_user and user_id. In order to know if in the case of Exporting to CSV they are well defined.
Best regards,
Morgan
Hi @morganperre
Sorry for the slow reply.
Yes, the forest_user object is set properly and looks normal.
Something I noticed though is on the csv request, the recorded active record time is 0.0ms
[Tracing] Starting <rails.request> transaction </forest/Organisation.csv>
Started GET "/forest/Organisation.csv?fields%5BOrganisation%5D=active%2Ccreated_at%2Cid%2Cname%2Cslug%2Cupdated_at&search=&filters=&searchExtended=0&timezone=Europe%2FLondon&filename=organisations&header=active%2Ccreated%20at%2Cid%2Cname%2Cslug%2Cupdated%20at" for 127.0.0.1 at 2021-05-10 13:53:03 +0100
Processing by ForestLiana::UserSpace::OrganisationController#index as CSV
Parameters: {"fields"=>{"Organisation"=>"active,created_at,id,name,slug,updated_at"}, "search"=>"", "filters"=>"", "searchExtended"=>"0", "timezone"=>"Europe/London", "filename"=>"organisations", "header"=>"active,created at,id,name,slug,updated at", "collection"=>"Organisation"}
Completed 200 OK in 10ms (ActiveRecord: 0.0ms | Allocations: 19542)
My thoughts are that either cached values are used, or the work is carried out on a background thread.
It could be that a new thread is created? and the db connection is not maintained.
1 Like
Hey @veritas1,
This still seems weird, since the code for the CSV export & the records fetch to display the table view are almost the same.
Looking at the connected_to documentation (here)
At the end of the block the connection will be returned to the original role / shard.
I guess that is the behavior you are experiencing, right?
Looking at the forest_liana code, I can’t figure out any reason this would not work. I would highly suggest to try debugging this with the forest_liana code, especially here and here since I’m pretty sure that if you are experiencing this issue only for CSV export, you’ll see it here.
Let me know if that helps 
Thanks.
I see what’s happening. The difference with the CSV response, is that the CSV response is streamed.
This code:
ActiveRecord::Base.connected_to(role: :writing, shard: user_db_connection.to_sym) do
yield
end
ensures the correct db connection for the scope of the controller action.
But on a CSV request, the controller action returns immediately (hence 0.0ms ActiveRecord time, as the db has not been touched yet).
The response body is made available by the Enumerator, but the controller action has already completed when the passed code block is executed, so the db connection has reset to the default one.