Getting "Error: socket has unexpectedly been closed" while exporting data

[kartikgupta2607]

I am using mariadb version - 3.2.0 promise based approach to create an export adaptor for mariadb server using queryStream on simple connection (not using pooled option). But after exporting few records (50k out of 1M records), the execution suddenly terminates with Error: socket has unexpectedly been closed error with following details [{"code":"ER_SOCKET_UNEXPECTED_CLOSE","message":"(conn=706534, no: 45009, SQLState: 08S01) socket has unexpectedly been closed"}].

On debugging, it seems that in the __streamreadable.js Readable.prototype.read function it is somehow going into the following block

if (state.length === 0) {
    // If we have nothing in the buffer, then we want to know
    // as soon as we *do* get something into the buffer.
    if (!state.ended) state.needReadable = true; // If we tried to read() past the EOF, then emit end on the next tick.
    if (nOrig !== n && state.ended) endReadable(this);
  }

and calling the endReadable function which further calls the endReadableNT function which emits end event and the end event handler has no error passed to it, so it throws the above error. I tried to modify the server variable timeouts (net_read_timeout, net_write_timeout and max_allowed_packet) and connection level are mainly kept as default (tried with connectTimeout option). Can someone help fix the issue?

Here is a reference of the script which replicates the behaviour on debugging Mariadb Export Sample.txt

[rusher]

That's a guess, but i expect to be the solution: The server usually expects clients to read off the result set relatively quickly. The net_write_timeout server variable controls this behavior (defaults to 60s). When reaching that timeout, server will close socket. If you don't expect results to be handled in this amount of time there is a different possibility:

• With >= MariaDB 10.1.2, you can use the query "SET STATEMENT net_write_timeout=10000 FOR XXX" with XXX your "normal" query. This will indicate that specifically for this query, net_write_timeout will be set to a longer time (10000 in this example).
• if your application usually uses a lot of long queries, the connection can be set using option sessionVariables: { net_write_timeout: 10000 } (10000 being an example, that depends on how much time this request is expected to be handled)

[kartikgupta2607]

I will certainly give that a try and provide you with an update. Thank you for your prompt assistance.

[kartikgupta2607]

Hello @rusher , thank you for the suggestion, I tried setting the net_write_timeout variable using connection options and its working as expected. However, I have a question: Are there any scenarios where the server might wait for this duration, even in error cases?

[rusher]

Server bever wait, it either send results or error. The only time server wait is because of locks.

[roy-mdr]

I'm also having the same error "Error: socket has unexpectedly been closed" but every day at 6:00am (I guess 12:00 UTC because my server timezone is UTC-06:00). Since this is the only time I'm getting the error is hard to debug with just the custom logs after the app crashes every 24hrs. I have another app running MariaDB connector 2.5.4 without issues, so my temporal solution was to downgrade to 2.5.4. Working fine in that version.

[kartikgupta2607]

@roy-mdr Maybe check for any scheduled restarts or connection clean up tasks? For me the issue was resolved by increasing net_write_timeout variable as I had to export large amount of data