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

1

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.

2

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

Postgres-source-advanced
Postgres-source-advanced.png1778×1660 185 KB

Kindly let us know, if this helped

Thanks
@balaji.ramaswamy

1 Like
3

This helps, thanks. It’s refreshing.

4

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

5

@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

6

@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.

7

I have the same issue, but my data source is MySQL
DATA_READ ERROR: Source 'xxx' returned error '(conn=720220) 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)'

The record fetch size setting I set to 100, but it doesn’t help
image

Do you have any suggestion, @balaji.ramaswamy?

8

@quangth2 Does this only happen on large datasets?

9

hello,
I established a union request and I received this message which is about time out
I increased the time august and tech size and still the same problem.
please let me know if there is a way to view the results of a large query.
please find incident details attached.

increq
increq1291×446 41.1 KB

Ali.
Cordially.

10

@alilos Can you try to make the record fetch size to 100, Edit source-Advanced, see screen shot below

Set it to 100 and retry the query, does it help?

Screen Shot 2022-12-15 at 6.04.32 PM
Screen Shot 2022-12-15 at 6.04.32 PM2552×1398 216 KB

11

Hi,

I thank you for replying, I edit the record fetch size in level of server or client?

Sincerely.I thank you for replying, I edit the record fetch size in level of server or client?

Sincerely.

12

Hi,

is there a substitute of query recursive in Dremio,

if it’s not the case how to make a query with recursive in Dremio

Thanks.

13

Salam,

Just a reminder.

THANKS.

ALI

14

@alilos edit record fetch size in the Dremio Mysql source advanced tab

Are you able to provide an example of the recursive query?

15

Please find below an example of the recursive date:

with recursive

dataset as

(select 1 as id,(concat(DATE_FORMAT(now(), “%Y-%m”),‘-01’)) as dt union select 1+id,STR_TO_DATE(CONCAT(year(now()),‘-’,month(now()),‘-’,CASE WHEN 1+id<10 THEN concat(‘0’,1+id) else 1+id end), “%Y-%m-%d”) as dt

from dataset where id<day(LAST_day(now()) ))

16

@alilos So you need an equivalent query in Dremio?

17

Yes i want an equivalent query in dremio.

18

Salam,

I’m still waiting for your habitual reply.

Thanks a lot.

ALI.

19

Salam,

i edited in the advanced options but the problem still persist

advanced drm.png
advanced drm.png1190×468 23.4 KB

20

@alilos Basically you have to replace SQL functions in your SQL with SQL functions that Dremio supports, listed functions are below

https://docs.dremio.com/software/sql-reference/sql-functions/

Example

select concat(to_char(now(),'YYYY-MM'),'-01'), to_date(concat(to_char(now(),'YYYY'),'-',to_char(now(),'MM')),'YYYY-MM')