In my Phoenix app I have to export a massive CSV of data. I am sending the data to the user in chunks because there is so much of it.
This is what my controller looks like.
def download_file(conn, params) do
new_conn =
conn
|> put_resp_content_type("application/csv")
|> put_resp_header("content-disposition", "attachment; filename=price_export.csv")
|> send_chunked(200)
prices = PricingExport.export_prices()
prices
# |> Stream.concat()
|> CSV.encode(
headers: ["Name", "Part Number", "Price", "Contains Errors", "Contains Warnings"]
)
|> Enum.reduce_while(new_conn, fn chunk, connection ->
case Plug.Conn.chunk(connection, chunk) do
{:ok, connection} ->
IO.inspect("===adding to connection===")
{:cont, connection}
{:error, :closed} ->
{:halt, connection}
end
end)
end
The data export starts and goes for awhile then around 50kb It fails and I get this error
[error] Postgrex.Protocol (#PID<0.664.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.5060.0> exited
Any idea why this might be happening?
Any Help would be greatly appreciated!
Can you show how you implement export_prices? Presumably that’s where you’re opening a database transaction
2 Likes
Hi Ben, Thanks for the response.
This is what export_prices looks like
def export_prices do
ProductConfig.list_full_sku_indexes()
|> Stream.map(fn product ->
return_pricing_export_object(product)
end)
end
list_full_sku_indexes looks like this
def list_full_sku_indexes do
Repo.all(FullSkuIndex)
end
The list full sku index query completes and the function starts to work but after awhile it times out.
I found this post How do I avoid DB disconnect on chunked streaming of large amount of data?
After finding this, I updated my dev config to
http: [port: 8000, protocol_options: [idle_timeout: 5_000_000]],
And now the function works locally. The problem is it still does not work in production.
This is what my prod config looks like, Any ideas why this isnt fixing it?
url: [host: {:system, "HOST"}, port: 80, protocol_options: [idle_timeout: 5_000_000]],
Again thanks for the help!
this is the error we are getting on production
I 2019-08-02T11:44:28.845809289Z 11:44:28.837 [error] GenServer #PID<0.138.0> terminating
I 2019-08-02T11:44:28.845843521Z ** (RuntimeError) connect raised KeyError exception: key :database not found. The exception details are hidden, as they may contain sensitive data such as database credentials. You may set :show_sensitive_data_on_connection_error to true when starting your connection if you wish to see all of the details
I 2019-08-02T11:44:28.845872185Z (elixir) lib/keyword.ex:377: Keyword.fetch!/2
I 2019-08-02T11:44:28.845876237Z (postgrex) lib/postgrex/protocol.ex:90: Postgrex.Protocol.connect/1
I 2019-08-02T11:44:28.845879674Z (db_connection) lib/db_connection/connection.ex:66: DBConnection.Connection.connect/2
I 2019-08-02T11:44:28.845883016Z (connection) lib/connection.ex:622: Connection.enter_connect/5
I 2019-08-02T11:44:28.845886209Z (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
I 2019-08-02T11:44:28.845889275Z Last message: nil
I 2019-08-02T11:44:28.846743392Z 11:44:28.846 [error] GenServer #PID<0.139.0> terminating
I 2019-08-02T11:44:28.846758331Z ** (RuntimeError) connect raised KeyError exception: key :database not found. The exception details are hidden, as they may contain sensitive data such as database credentials. You may set :show_sensitive_data_on_connection_error to true when starting your connection if you wish to see all of the details
I 2019-08-02T11:44:28.846762490Z (elixir) lib/keyword.ex:377: Keyword.fetch!/2
I 2019-08-02T11:44:28.846795533Z (postgrex) lib/postgrex/protocol.ex:90: Postgrex.Protocol.connect/1
I 2019-08-02T11:44:28.846800205Z (db_connection) lib/db_connection/connection.ex:66: DBConnection.Connection.connect/2
I 2019-08-02T11:44:28.846803604Z (connection) lib/connection.ex:622: Connection.enter_connect/5
I 2019-08-02T11:44:28.846806895Z (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
I 2019-08-02T11:44:28.846810060Z Last message: nil
I 2019-08-02T11:44:28.847359804Z 11:44:28.847 [error] GenServer #PID<0.140.0> terminating
I 2019-08-02T11:44:28.847371713Z ** (RuntimeError) connect raised KeyError exception: key :database not found. The exception details are hidden, as they may contain sensitive data such as database credentials. You may set :show_sensitive_data_on_connection_error to true when starting your connection if you wish to see all of the details
I 2019-08-02T11:44:28.847375687Z (elixir) lib/keyword.ex:377: Keyword.fetch!/2
I 2019-08-02T11:44:28.847378978Z (postgrex) lib/postgrex/protocol.ex:90: Postgrex.Protocol.connect/1
I 2019-08-02T11:44:28.847382355Z (db_connection) lib/db_connection/connection.ex:66: DBConnection.Connection.connect/2
I 2019-08-02T11:44:28.847385646Z (connection) lib/connection.ex:622: Connection.enter_connect/5
I 2019-08-02T11:44:28.847388883Z (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
I 2019-08-02T11:44:28.847391905Z Last message: nil
Right before the error comes it looks like my relsease task startrunning for some reason.
My app logs this out
I 2019-08-02T14:03:02.947470656Z Starting dependencies..
I 2019-08-02T14:03:03.137240798Z Starting repos..
I 2019-08-02T14:03:03.293244461Z Running migrations for beswick
The timeout that you added is for the HTTP connection, not for the DB connection. Therefore, if your database query takes more than the timeout (15 sec by default if I am not mistaken), the query will timeout. Could that be the problem?
In that case, you could increase the query timeout like @jola wrote here: How do I avoid DB disconnect on chunked streaming of large amount of data?
That said, having a very long running DB query might not be ideal, so getting the records from the DB in chunks might be a better option.
2 Likes
Yeah the query is long but it does complete. The error happens after I start sending my data to the connection. I did try setting my db timeoout like so.
def list_full_sku_indexes do
FullSkuIndex
|> Repo.all(timeout: :infinity)
end
And I am still getting the same error.
Uhm, then unfortunately I have no answer, just questions. I hope one of them points you in the right direction.
What is return_pricing_export_object doing?
Could it be that the node runs out of memory and restarts?
Why is the release task running? Does it trigger only when this error occurs, or also normally? If for some reason it triggers periodically, it might be that the issue is unrelated to the CSV download: it might be happening during the CSV download just because it’s a long-running request that is more likely to be interrupted by a release.
1 Like
This Is what return_pricing_export returns
def return_pricing_export_object(indexed_product) do
product = ProductConfig.get_product_version!(indexed_product.product_version_id)
prod = %{
"Name" => product.code,
"Part Number" => indexed_product.sku,
"Price" => indexed_product.price,
"Contains Errors" => check_error_value(indexed_product.contains_errors),
"Contains Warnings" => check_error_value(indexed_product.contains_warnings)
}
end
I am still not sure why the release tasks are running, I dont have any jobs that run periodically that have anything to do with the release task. every-time I hit export those release tasks start no matter what.
