"Connection Closed before request completed" error when update with long string on SQL Server

[ricklang]

I have a table in SQL Server with a column of type varbinary. I use STRING.BINARY to model the column in Sequelize.

      signature: {
        type: DataTypes.STRING.BINARY,
        allowNull: true,
        field: 'Signature',
        get() {
          let s = this.getDataValue('signature');
          if (s) {
            let buf = Buffer.from(s);
            return buf.toString('base64');
          } else {
            return null;
          }
        },
        set(val) {  /* not working. Sequelize stop generating create/update statement. */
          let buf = Buffer.from(val, 'base64');
          this.setDataValue('signature', buf);
        },
      },

I use GraphQL to create service. So the signature column uses base64 string type in the API. In the get() method, I convert the binary data to base64 string and it is working fine. But how do I set the base64 string to binary type in the set() method?

[papb]

Shouldn't you be doing this.setDataValue('signature', buf.toString()); instead?

[ricklang]

After further testing, it looks like the problem is the length of string. I changed column to STRING type. I did the update from GraphQL playground and got error

    Connection closed before request completed.

when the signature string is around 3950 chars long.

mutation updateStaff {
  updateStaff(
    input: {
      where: { staffId: 3000 }
      data: {
        signature: "a very loooooooong string"
        modifiedBy: 3700
      }
    }
  ) {
    message
    data {
      staffId
      signature
    }
  }
}

I am using

    "sequelize": "^5.8.11",
    "tedious": "^6.2.0"

and here is my GraphQL resolver:

async updateStaff(parent, { input }, context) {
      try {
        let currRecord = await context.db.Staff.findByPk(input.where.staffId);
        if (!currRecord) {
          return { message: 'No record to update' };
        }
        const newRecord = await currRecord.update(input.data);
        return { data: newRecord };
      } catch (error) {
        console.log(error.message);   /* ERROR THROWS FROM HERE */
        return { message: error.message };
      }
    },

There is no problem when reading the long string back from the database.

[ricklang]

Looks like increase the tedious packet size could solve the problem.

options: {
   packetSize: 65536

But I don't know how it will affect the perfornance. Also whether I can set it just on a query by query basis.

[papb]

Was the error thrown from context.db.Staff.findByPk(input.where.staffId) or from currRecord.update(input.data)?

[ricklang]

If I set the packet size to 32768 in the connection options, then the problem will be solved. It doesn't make sense to me. Why the packets are not re-assembled at the server side when the packet size is 4096 (default). And clearly the packets are re-assembled at the client side all the time.

[ricklang]

It looks like a problem with tedious. I file a new issue there.
tedious issue 923.

[pgqueme]

I got this error when moving to a Heroku instance with Node v12+ (I moved from an Azure instance with Node 8.1+). Changing the Node engine on my app and deploying again solved the issue.

[lmshaffe]

Is there a plan to have Sequelize use the fix with tedious v6.2.1+?

[papb]

@lmshaffe Sorry, I didn't understand, what do you mean?

[xage93]

I think what he meant to say is that the issue has been resolved by the tedious team in the version 6.2.1 according to this: tedious issue 923

I myself encountered this same issue just now, had an app using Sequelize 4.38.0 and Tedious 2.6.4
As the people in tedious issue 923 described, it works fine on older Node versions, I was on Node 8.11.1. I recently updated to Node 12.13.0 and started encountering this issue.
After reading up on the comments here and the tedious issue thread, I tried updating the Tedious package to 6.2.1 leaving sequelize at 4.38.0 but then the database wouldn't even connect and the server never starts. So for a quick fix I had to resort to the packetSize option and revert back to the older tedious version.

After further testing however, I found that updating both Sequelize and Tedious to their latest versions, 6.5.1 and 6.7.0 respectively, runs everything smoothly and as expected. I can now run long queries without specifying the packetSize option.