ConnectionError: Connection lost - write ECONNRESET when inserting long string

[ricklang]

I have a table on an Azure SQL Database.

CREATE TABLE [dbo].[Owner](
	[OwnerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](50) NOT NULL,
	[Signature] [varchar](max) NULL
)

I tried to insert very long string to the Signature column. If the packetSize is not set to 16384 or higher, I got the following exception:

ConnectionError: Connection lost - write ECONNRESET
    at ConnectionError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\errors.js:13:12)
    at Connection.socketError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1187:26)
    at Socket.<anonymous> (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1032:14)
    at Socket.emit (events.js:205:15)
    at errorOrDestroy (internal/streams/destroy.js:107:12)
    at onwriteError (_stream_writable.js:438:5)
    at onwrite (_stream_writable.js:459:5)
    at internal/streams/destroy.js:49:7
    at Socket._destroy (net.js:593:3)
    at Socket.destroy (internal/streams/destroy.js:37:8)
Emitted 'error' event at:
    at Connection.socketError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1187:12)
    at Socket.<anonymous> (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1032:14)
    [... lines matching original stack trace ...]
    at Socket.destroy (internal/streams/destroy.js:37:8)
    at WriteWrap.onWriteComplete [as oncomplete] (internal/stream_base_commons.js:84:12) {
  message: 'Connection lost - write ECONNRESET',
  code: 'ESOCKET'
}

Below is my simple test program. I use the latest tedious. Also the read operation seems to work fine for long string no matter what the packet size is. Any idea what I did wrong?

var Connection = require('tedious').Connection;

var config = {
  server: "myserver.database.windows.net",
  options: {
    encrypt: true,
    database: "<mydb>",
    packetSize: 4096,
  },
  authentication: {
    type: "default",
    options: {  
      userName: "<myuser>",
      password: "<mypwd>",
    }
  }
};

var connection = new Connection(config);

connection.on('connect', function(err) {
    // executeStatement();
    executeInsert();
  }
);


var Request = require('tedious').Request;

function executeStatement() {
  /* Read a long string, work fine */
  request = new Request("select OwnerId, Signature From dbo.Owner Where OwnerId = 36", function(err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + ' rows');
    }
  });

  request.on('row', function(columns) {
    columns.forEach(function(column) {
      console.log(column.value);
    });
  });

  connection.execSql(request);
}


function executeInsert() {
  /************************************************
   * Insert a long string, not working when
   *     packetSize = 4096
   * If
   *     packetSize = 16384
   * or higher, insertion works fine.
   *****************************************************/
  let s = '0123456789'.repeat(100000);
  request = new Request("Insert into dbo.Owner VALUES ('Rick', '" + s + "')", function(err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + ' rows');
    }
  });

  connection.execSql(request);
}

[susares]

We run into a similar issue. when trying to insert a long string. After a bit of testing it turns out, that for us it is related to the used nodejs version in our case dockerized.
node:10-slim -> works fine
node:12.3.1 -> works fine
starting node:12.4 results in either socket hangup or socket closed error.

[MichaelSun90]

Hi @ricklang, I was looking int this. Is "encrypt: true" a required setting on your side? If it is not, then set it to false or just do not set it in the option which using the default value -false can also resolve these issues. I was trying to figure out why this is happening, but I have not found a root cause for this. I checked the packets for both encrypt and non-encrypt connection, the packets are exactly the same, but for encrypting one, when the last packet with EOM is received, the connection lost error is received. I will give a bit more investigation, see if I can dig up anything.
On the other hand, we could also ask @arthurschreiber about this, see if he has any insight.

[ricklang]

With

encrypt: false,

Error message is (no matter what the packet size is):

RequestError: Requests can only be made in the LoggedIn state, not the SentPrelogin state
    at RequestError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\errors.js:32:12)
    at Connection.makeRequest (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1680:24)
    at Connection.execSql (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1459:10)
    at executeInsert (C:\work\GitRepos\tedious-test\index.js:67:14)
    at Connection.<anonymous> (C:\work\GitRepos\tedious-test\index.js:24:5)
    at Connection.emit (events.js:200:13)
    at Connection.message (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1871:18)
    at Connection.dispatchEvent (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1172:36)
    at MessageIO.<anonymous> (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1045:14)
    at MessageIO.emit (events.js:200:13) {
  message: 'Requests can only be made in the ' +
    'LoggedIn state, not the SentPrelogin ' +
    'state',
  code: 'EINVALIDSTATE'

If remove encrypt setting, but leave the packet size at 4096, error message is:

ConnectionError: Connection lost - write ECONNRESET
    at ConnectionError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\errors.js:13:12)
    at Connection.socketError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1187:26)
    at Socket.<anonymous> (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1032:14)
    at Socket.emit (events.js:205:15)
    at errorOrDestroy (internal/streams/destroy.js:107:12)
    at onwriteError (_stream_writable.js:438:5)
    at onwrite (_stream_writable.js:459:5)
    at internal/streams/destroy.js:49:7
    at Socket._destroy (net.js:593:3)
    at Socket.destroy (internal/streams/destroy.js:37:8)
Emitted 'error' event at:
    at Connection.socketError (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1187:12)
    at Socket.<anonymous> (C:\work\GitRepos\tedious-test\node_modules\tedious\lib\connection.js:1032:14)
    [... lines matching original stack trace ...]
    at Socket.destroy (internal/streams/destroy.js:37:8)
    at WriteWrap.onWriteComplete [as oncomplete] (internal/stream_base_commons.js:84:12) {
  message: 'Connection lost - write ECONNRESET',
  code: 'ESOCKET'

But if increases packet size to 16384 with no encrypt set, everything works fine.

By the way, I tested for node versions: 12.4.0, 12.6.0. Same results.

[wy193777]

Got Connection lost - read ECONNRESET when run a long query. With encrypt: true and packetSize: 32768, everything works fine.

node version: v12.6.0

[susares]

@wy193777 could you check if everything works without additional options and using node version at max 12.3.1 ?

[wy193777]

@susares node v12.3.1 works without packetSize option works for me.

[Josh-Weston]

node version: v12.2.0 works without packetSize option
node version: v.12.6.0 required me to use packetSize: 8192 to avoid the Connection lost - read ECONNRESET error

[arthurschreiber]

This seems to be due to nodejs/node#27861. Somehow, SQLServer will end the connection if large TLS segments are written. I do have a fix and will open a PR shortly. 👍

For those who can't wait, here's the diff that will fix this:

diff --git a/src/message-io.js b/src/message-io.js
index 90875f8..79f5da8 100644
--- a/src/message-io.js
+++ b/src/message-io.js
@@ -72,6 +72,8 @@ module.exports = class MessageIO extends EventEmitter {
       encrypted: duplexpair.socket2
     };
 
+    securePair.cleartext.setMaxSendFragment(this.outgoingMessageStream.packetSize);
+
     // If an error happens in the TLS layer, there is nothing we can do about it.
     // Forward the error to the socket so the connection gets properly cleaned up.
     securePair.cleartext.on('error', (err) => {

[arthurschreiber]

🎉 This issue has been resolved in version 6.2.1 🎉

The release is available on:

• npm package (@next dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[padiny]

Facing this issue when performing bulk insert op. When I truncate the query its working fine.

node: 16.8.0
mssql: 6.3.2

[omardoma]

For whoever is still struggling with any of the following issues Connection lost - read ECONNRESET or Connection lost - write ECONNRESET while dealing with large reads or writes and using Node.js v12.4+, setting these options in the configuration fixed it for me:

encrypt:  true,
packetSize: 16368

[kanthi0802]

The issue still exists. Tried all the workarounds mentioned in the thread. Is there any other way to fix this issue. Seeing this issue on read operation.

[imtiyazthange]

I am still facing this error, do we have any other fix or workaround.

[MichaelSun90]

Hi @imtiyazthange, I assume you have already tried the fix and configuration settings mentioned in this thread. I am wondering, which version of tedious and node are you currently on? If possible, can you post your connection configuration here? In this way, we can try to reproduce the behavior with the same setup. Thanks!

[imtiyazthange]

I am using node-mssql version (7.1.4) not tedious, but node-mssql reference tedious internally (version 4.0.0)
And my application is running under Node.js 14 LTS.

{ user: process.env.DB_USER, password: process.env.DB_PASS, database: process.env.DB_NAME, server: process.env.DB_SERVER!, requestTimeout: Number(process.env.DB_REQUEST_TIMEOUT || 30000), stream: false, pool: { max: 10, min: 0, idleTimeoutMillis: 30000 }, options: { packetSize: 16368, encrypt: false, // for azure trustServerCertificate: false, // change to true for local dev / self-signed certs rowCollectionOnRequestCompletion: true } }

[mShan0]

@imtiyazthange Can you try upgrading Tedious to a version that is at least 6.2.1?

[weatherangel]

@imtiyazthange Can you try upgrading Tedious to a version that is at least 6.2.1?

Requiring tedious 6.2.1 in my project rather than depending on mssql's minimum version fixed the issue for me. Even though I had updated mssql, the version of tedious was still an issue.

{ ... "engines": { "node": ">=16.13.0" }, "scripts": { "start": "node server.js" }, "dependencies": { "body-parser": "^1.19.0", "express": "^4.17.2", "fs": "0.0.2", "mssql": "^7.3.0", "nodemailer": "^6.3.1", "request": "^2.88.2", "sequelize": "^6.13.0", "sequelize-cli": "^6.3.0", "tedious": "^6.2.1" } }

Thank you!

[mShan0]

Awesome! Glad it worked out.

[RSS1102]

了不起的！很高兴它成功了。

can you help me for this? i also get this fail.

[mShan0]

Hi @RSS1102, what Tedious version are you using?

[fednelpat]

Hi, I'm encountering the same issue using
"mssql": "^8.1.0", "tedious": "^14.4.0",. None of the solutions provided above solved the issue.

[mShan0]

Hi @deltarays, if you're still experiencing this problem, could you open a new issue? We can assist you from there.

I couldn't reproduce this issue using either of the latest versions of Tedious or mssql on Node 12, 14, or 16.

[fednelpat]

I will, thanks! Should I make the issue in tediousjs/tedious or in tediousjs/node-mssql?

[mShan0]

You can open it under tediousjs/tedious

[fednelpat]

Okay, I was able to find a solution to the issue:
Changing the port from 1434 to 1433.
The worst thing was that only queries that were sent in quick succession or that required big chunks of data encountered issues, everything else was fine. Thanks anyways

[kin-for-test]

Okay, I was able to find a solution to the issue: Changing the port from 1434 to 1433. The worst thing was that only queries that were sent in quick succession or that required big chunks of data encountered issues, everything else was fine. Thanks anyways

i get the ConnectionError ConnectionError: Connection lost - read ECONNRESET with using the 1434 port , and the sql server browser is listening to the 1434 port too , is it the issue to make the connection error?

[MichaelSun90]

Hi @kin-for-test , for this error, can you share with us your connation config set up as well as the error log. These information may help us understand the issue better. If is not too much trouble, you can also open up an new issue, so we can better track the conversions.

[kin-for-test]

Hi @kin-for-test , for this error, can you share with us your connation config set up as well as the error log. These information may help us understand the issue better. If is not too much trouble, you can also open up an new issue, so we can better track the conversions.

HI @MichaelSun90 ,i using node-mssql to connect db , but it seem like the tedious error, i create a issues in node-mssql , link:tediousjs/node-mssql#1467 , thank !

[mattdeba]

Using typeorm I have set this config:

pool: {
max: 1,
}

It limits the number of connection to 1 rather than 10.

[Harshpanday]

Hey everyone,
I am facing the same issue, I have tried all the fixes mentioned in this thread but none of them seem to work.

Software Versions:
Node : v18.16.0
mssql : v9.1.1
Sequelize : v6.31.1
Tedious: v16.1.0

[mattdeba]

Try this one:
type: 'mssql',
host: 'localhost',
port: 1434,
username: 'sa',
password: 'yourPassword',
database: 'yourDbName',
synchronize: true,
options: {
encrypt: false,
},
pool: {
max: 1,
},

[Harshpanday]

@mattdeba thank you for your suggestion. But I fixed my issue by using a different SQL server.

Initially I was using SQL server 2022 which was causing this error for some reason, but after shifting to Azure's SQL server my script worked completely fine.

To anyone else who is getting this error, try a different SQL server (like Azure's SQL server or SQL server 2019).

[AWeissMotz]

packetSize: 16368

Adjusting the packageSize was the key for me. It seems Azure SQL DB requires a dedicated size definition for queries with a high number of chars