SQL Server using TypeORM: Error Timeout: Request failed to complete when inserting records by batch

Question

The issue I am having is that when I run the migration command I always get an ETIMEOUT error, but if I comment out the await populateTable() the code will run without any error.

I already tried to increase the requestTimeout from 15sec to 300sec but that didn't help.

Here's my code:

Create the following:

// ormconfig.json
[
  {
    "name": "app",
    "type": "mssql",
    "host": "127.0.0.1",
    "port": 1433,
    "username": "root",
    "password": "root",
    "database": "app",
    "logging": true,
    "options": {
      "useUTC": true
    },
    "entities": ["src/**/*.entity.ts"],
    "migrations": ["migration/**/*.ts"],
    "cli": {
      "migrationsDir": "migration"
    }
  }
]

// root-dir/src/grouping/grouping.entity.ts
import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from 'typeorm';

@Entity({ name: 'grouping' })
export class Grouping {

  @PrimaryGeneratedColumn({ name: 'id', unsigned: true })
  id: number;

  @Column({ name: 'name', unique: true })
  name: string;
}

// root-dir/migration/tables/grouping.ts
import { getRepository, QueryRunner, Table, TableIndex } from 'typeorm';
import { Grouping } from '../../src/grouping/grouping.entity';

export async function up(queryRunner: QueryRunner): Promise<any> {
  await createTable(queryRunner);
  await createIndexes(queryRunner);
  await populateTable();
}

export async function down(queryRunner: QueryRunner): Promise<any> {
  await queryRunner.dropIndex('grouping', 'IDX_GROUP');
  await queryRunner.dropTable('grouping');
}

async function createTable(queryRunner: QueryRunner) {
  return queryRunner.createTable(
    new Table({
      name: 'grouping',
      columns: [
        {
          name: 'id',
          type: 'integer',
          isPrimary: true,
          isGenerated: true,
          generationStrategy: 'increment',
          unsigned: true,
        },
        {
          name: 'name',
          type: 'varchar',
          isUnique: true,
        },
      ],
    }),
    true,
  );
}

async function createIndexes(queryRunner: QueryRunner) {
  return await queryRunner.createIndex(
    'grouping',
    new TableIndex({
      name: 'IDX_GROUP',
      columnNames: ['name'],
    }),
  );
}

async function populateTable() {
  await getRepository(Grouping, 'app').save([{ name: 'classification' }, { name: 'categorization' }]);
}

// root-dir/migration/initial-migration.ts
import { MigrationInterface, QueryRunner } from 'typeorm';
import * as groupingTable from './tables/grouping';

export class InitialMigration1550229771145 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<any> {
    await groupingTable.up(queryRunner);
  }

  async down(queryRunner: QueryRunner): Promise<any> {
    await groupingTable.down(queryRunner);
  }
}

Run the migration command.

ts-node ./node_modules/typeorm/cli.js migration:run -c

This should create the Grouping table and insert 2 records but what I am getting is this error:

Query: CREATE TABLE "grouping" ("id" integer NOT NULL IDENTITY(1,1), "name" varchar(255) NOT NULL, CONSTRAINT "UQ_07314fe287a837177015c041131" UNIQUE ("name"), CONSTRAINT "PK_135d73da7246e0250716afdc0ab" PRIMARY KEY ("id")) query: SELECT SCHEMA_NAME() AS "schema_name" query: SELECT DB_NAME() AS "db_name" query: SELECT * FROM "app"."INFORMATION_SCHEMA"."TABLES" WHERE ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'grouping') query: SELECT * FROM "app"."INFORMATION_SCHEMA"."COLUMNS" WHERE ("TABLE_SCHEMA" = 'dbo' AND "TABLE_NAME" = 'grouping') query: SELECT "columnUsages".*, "tableConstraints"."CONSTRAINT_TYPE", "chk"."definition" FROM "app"."INFORMATION_SCHEMA"."CONSTRAINT_COLUMN_USAGE" "columnUsages" INNER JOIN "app"."INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" "tableConstraints" ON "tableConstraints"."CONSTRAINT_NAME" = "columnUsages"."CONSTRAINT_NAME" LEFT JOIN "app"."sys"."check_constraints" "chk" ON "chk"."name" = "columnUsages"."CONSTRAINT_NAME" WHERE (("columnUsages"."TABLE_SCHEMA" = 'dbo' AND "columnUsages"."TABLE_NAME" = 'grouping' AND "tableConstraints"."TABLE_SCHEMA" = 'dbo' AND "tableConstraints"."TABLE_NAME" = 'grouping')) AND "tableConstraints"."CONSTRAINT_TYPE" IN ('PRIMARY KEY', 'UNIQUE', 'CHECK') query: SELECT "fk"."name" AS "FK_NAME", 'app' AS "TABLE_CATALOG", "s1"."name" AS "TABLE_SCHEMA", "t1"."name" AS "TABLE_NAME", "col1"."name" AS "COLUMN_NAME", "s2"."name" AS "REF_SCHEMA", "t2"."name" AS "REF_TABLE", "col2"."name" AS "REF_COLUMN", "fk"."delete_referential_action_desc" AS "ON_DELETE", "fk"."update_referential_action_desc" AS "ON_UPDATE" FROM "app"."sys"."foreign_keys" "fk" INNER JOIN "app"."sys"."foreign_key_columns" "fkc" ON "fkc"."constraint_object_id" = "fk"."object_id" INNER JOIN "app"."sys"."tables" "t1" ON "t1"."object_id" = "fk"."parent_object_id" INNER JOIN "app"."sys"."schemas" "s1" ON "s1"."schema_id" = "t1"."schema_id" INNER JOIN "app"."sys"."tables" "t2" ON "t2"."object_id" = "fk"."referenced_object_id" INNER JOIN "app"."sys"."schemas" "s2" ON "s2"."schema_id" = "t2"."schema_id" INNER JOIN "app"."sys"."columns" "col1" ON "col1"."column_id" = "fkc"."parent_column_id" AND "col1"."object_id" = "fk"."parent_object_id" INNER JOIN "app"."sys"."columns" "col2" ON "col2"."column_id" = "fkc"."referenced_column_id" AND "col2"."object_id" = "fk"."referenced_object_id" query: SELECT "TABLE_CATALOG", "TABLE_SCHEMA", "COLUMN_NAME", "TABLE_NAME" FROM "app"."INFORMATION_SCHEMA"."COLUMNS" WHERE COLUMNPROPERTY(object_id("TABLE_CATALOG" + '.' + "TABLE_SCHEMA" + '.' + "TABLE_NAME"), "COLUMN_NAME", 'IsIdentity') = 1 AND "TABLE_SCHEMA" IN ('dbo') query: SELECT "NAME", "COLLATION_NAME" FROM "sys"."databases" query: SELECT 'app' AS "TABLE_CATALOG", "s"."name" AS "TABLE_SCHEMA", "t"."name" AS "TABLE_NAME", "ind"."name" AS "INDEX_NAME", "col"."name" AS "COLUMN_NAME", "ind"."is_unique" AS "IS_UNIQUE", "ind"."filter_definition" as "CONDITION" FROM "app"."sys"."indexes" "ind" INNER JOIN "app"."sys"."index_columns" "ic" ON "ic"."object_id" = "ind"."object_id" AND "ic"."index_id" = "ind"."index_id" INNER JOIN "app"."sys"."columns" "col" ON "col"."object_id" = "ic"."object_id" AND "col"."column_id" = "ic"."column_id" INNER JOIN "app"."sys"."tables" "t" ON "t"."object_id" = "ind"."object_id" INNER JOIN "app"."sys"."schemas" "s" ON "s"."schema_id" = "t"."schema_id" WHERE "ind"."is_primary_key" = 0 AND "ind"."is_unique_constraint" = 0 AND "t"."is_ms_shipped" = 0 query: CREATE INDEX "IDX_GROUP" ON "grouping" ("name") query: BEGIN TRANSACTION query: INSERT INTO "grouping"("name") OUTPUT INSERTED."id" VALUES (@0), (@1) -- PARAMETERS: [{"value":"classification","type":"nvarchar","params":[]},{"value":"categorization","type":"nvarchar","params":[]}] query failed: INSERT INTO "grouping"("name") OUTPUT INSERTED."id" VALUES (@0), (@1) -- PARAMETERS: [{"value":"classification","type":"nvarchar","params":[]},{"value":"categorization","type":"nvarchar","params":[]}] error: { RequestError: Timeout: Request failed to complete in 15000ms at Request.tds.Request.err [as userCallback] (C:\Users\me\Workspace\app\api\node_modules\mssql\lib\tedious.js:629:19) at Request.callback (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\request.js:37:27) at Connection.message (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:2136:24) at Connection.dispatchEvent (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:1084:36) at MessageIO.messageIo.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:984:14) at MessageIO.emit (events.js:189:13) at MessageIO.EventEmitter.emit (domain.js:441:20) at Message.message.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\message-io.js:32:14) at Message.emit (events.js:194:15) at Message.EventEmitter.emit (domain.js:441:20) code: 'ETIMEOUT', number: 'ETIMEOUT', state: undefined, originalError: { RequestError: Timeout: Request failed to complete in 15000ms at RequestError (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\errors.js:32:12) at Connection.message (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:2136:33) at Connection.dispatchEvent (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:1084:36) at MessageIO.messageIo.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:984:14) at MessageIO.emit (events.js:189:13) at MessageIO.EventEmitter.emit (domain.js:441:20) at Message.message.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\message-io.js:32:14) at Message.emit (events.js:194:15) at Message.EventEmitter.emit (domain.js:441:20) at endReadableNT (C:\Users\me\Workspace\app\api\node_modules\tedious\node_modules\readable-stream\lib_stream_readable.js:1077:12) message: 'Timeout: Request failed to complete in 15000ms', code: 'ETIMEOUT' }, name: 'RequestError', precedingErrors: [] } query: ROLLBACK query: ROLLBACK Error during migration run: { QueryFailedError: Error: Timeout: Request failed to complete in 15000ms at new QueryFailedError (C:\Users\me\Workspace\app\api\src\error\QueryFailedError.ts:9:9) at C:\Users\me\Workspace\app\api\src\driver\sqlserver\SqlServerQueryRunner.ts:221:37 at _query (C:\Users\me\Workspace\app\api\node_modules\mssql\lib\base.js:1346:25) at Request.tds.Request.err [as userCallback] (C:\Users\me\Workspace\app\api\node_modules\mssql\lib\tedious.js:671:15) at Request.callback (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\request.js:37:27) at Connection.message (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:2136:24) at Connection.dispatchEvent (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:1084:36) at MessageIO.messageIo.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:984:14) at MessageIO.emit (events.js:189:13) at MessageIO.EventEmitter.emit (domain.js:441:20) message: 'Error: Timeout: Request failed to complete in 15000ms', code: 'ETIMEOUT', number: 'ETIMEOUT', state: undefined, originalError: { RequestError: Timeout: Request failed to complete in 15000ms at RequestError (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\errors.js:32:12) at Connection.message (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:2136:33) at Connection.dispatchEvent (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:1084:36) at MessageIO.messageIo.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\connection.js:984:14) at MessageIO.emit (events.js:189:13) at MessageIO.EventEmitter.emit (domain.js:441:20) at Message.message.on (C:\Users\me\Workspace\app\api\node_modules\tedious\lib\message-io.js:32:14) at Message.emit (events.js:194:15) at Message.EventEmitter.emit (domain.js:441:20) at endReadableNT (C:\Users\me\Workspace\app\api\node_modules\tedious\node_modules\readable-stream\lib_stream_readable.js:1077:12) message: 'Timeout: Request failed to complete in 15000ms', code: 'ETIMEOUT' }, name: 'QueryFailedError', precedingErrors: [], query: 'INSERT INTO "grouping"("name") OUTPUT INSERTED."id" VALUES (@0), (@1)', parameters: [ MssqlParameter { value: 'classification', type: 'nvarchar', params: [] }, MssqlParameter { value: 'categorization', type: 'nvarchar', params: [] } ] } npm ERR! code ELIFECYCLE npm ERR! errno 1 npm ERR! app-api@0.1.0 migration:run: ts-node ./node_modules/typeorm/cli.js migration:run -c "app_engine" npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the app-api@0.1.0 migration:run script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR! C:\Users\me\AppData\Roaming\npm-cache_logs\2019-06-04T18_56_36_972Z-debug.log

Any help is greatly appreciated!

Update:

I found this https://github.com/typeorm/typeorm/issues/3100#issuecomment-446309812 while browsing through the previous issues and it is now working.

Answer 1

For anyone who is searching how to increase the timeout, you can do it by adding a property called requestTimeout in your config for MSSQL

// ormconfig.json
[
  {
    "name": "app",
    "type": "mssql",
    "host": "127.0.0.1",
    "requestTimeout": 30000,....
]

You can refer TypeORM SqlServerConnectionOptions - https://github.com/typeorm/typeorm/blob/master/src/driver/sqlserver/SqlServerConnectionOptions.ts for additional properties.

Answer 2

try to use a legacy version of the npm i mssql @ 5.1.1 driver --save