"Server has closed the connection" while creating reflection for PostgreSQL table

We have rather large table on PostgreSQL which frequently accessed via our Dremio implementation. To make things faster we decided to create full raw reflection for it. When the first reflection initiating it failed with error and suggestion below

(java.sql.SQLNonTransientConnectionException) (conn=786851120) Server has closed the connection. If result set contain huge amount of data, Server expects client to read off the result set relatively fast. In this case, please consider increasing net_wait_timeout session variable / processing your result set faster (check Streaming result sets documentation for more information)

It seems that handling Streaming result sets documentation isn’t available on Dremio documentation. And there is nowhere else to set net_wait_timeout when editing Dremio source for PostgreSQL.

Hi @chafidz

Can you try to reduce record fetch size by editing the postgres source via the Dremio UI, use advanced property and set the value. 0 means everything while a smaller number can be a good start, see Postgres documentation on the below

Setting Fetch Size

attaching screenshot of Postgres source

Kindly let us know, if this helped

Thanks
@balaji.ramaswamy

1 Like

This helps, thanks. It’s refreshing.

Hey @balaji.ramaswamy,

We are also experiencing same problem while fetching data from Mysql data source.

I have already configured record fetch size to 200 records for mysql data source, but still i’m getting this error.

Here is the exact error that we got:
SYSTEM ERROR: EOFException: unexpected end of stream, read 3 bytes from 4 (socket was closed by server)

SqlOperatorImpl JDBC_SUB_SCAN

(java.sql.SQLNonTransientConnectionException) (conn=640084) Server has closed the connection. If result set contain huge amount of data, Server expects client to read off the result set relatively fast. In this case, please consider increasing net_wait_timeout session variable / processing your result set faster (check Streaming result sets documentation for more information)

Could you please help us on this.

Thanks,
Jalandhar

@jalandhar

I am wondering if you are able to narrow down the issue? What happens if you create the reflection on a subset of the table. Try to use an INDEX in the WHERE clause so there is an index used in Postgres using the pushdown query sent

Thanks
@balaji.ramaswamy

@jalandhar

This issue resolved? Iam also facing the same issue(MySQL datasource Net write timeout behaviour) when reflection full update on MySQL 8.x db, when changing the net_write_timeout from mysql server side global varible it’s not coming, impact always happens when changing the varible at server side not at dremio advanced options.So if you have any troubleshoot procedure please let me know.

Thanks,
Ajay Babu Maguluri.