使用TypeORM迁移CockroachDB时只读事务错误求助

使用TypeORM对CockroachDB执行数据库迁移时，遇到了**「无法在只读事务中执行CREATE SEQUENCE」**的错误。尝试过修改DataSource配置、在迁移代码中显式设置事务只读相关变量为off等方法，但均未解决问题。以下是相关配置文件、自动生成的迁移文件及完整错误日志，恳请提供解决指导：

dataSource.ts 配置文件 #

import { DataSource } from 'typeorm';
import { Step } from './src/recipe/entity/step.entity';
import { Chemical } from './src/recipe/entity/chemical.entity';
import { Recipe } from './src/recipe/entity/recipe.entity';

export const dataSource = new DataSource({
    type: "cockroachdb",
    timeTravelQueries: true,
    url: "xxxx-xxxxxxx-xxxxx",
    ssl: true,
    synchronize: false,
    logging: true,
    entities: [Step,Chemical,Recipe],
    migrations: [__dirname + '/src/**/migrations/*{.ts,.js}'],
    extra: {
      enableExperiments: ['sequence-ddl'],
  
}});


async () => {await dataSource.initialize()
  .then(() => {
    console.log('Data Source has been initialized!');
    // async (): Promise<void> =>{
    //   await dataSource.query(`SET default_transaction_use_follower_reads = 'off';`);
    //   // Your migration code here
    // }

    console.log('Follower reads setting has been disabled.');
    
    })
  .catch((err) => {
    console.error('Error during Data Source initialization', err);
  });}

自动生成的迁移文件 #

import { MigrationInterface, QueryRunner, Transaction } from "typeorm";
import { writeHeapSnapshot } from "v8";

export class Migration011720100667518 implements MigrationInterface {
    name = 'Migration011720100667518'
    public async set_trans_off(queryRunner: QueryRunner): Promise<void>{
        
    }
    
    public async up(queryRunner: QueryRunner): Promise<void> {
        // // await queryRunner.query().
        // await queryRunner.startTransaction('READ WRITE');
        await queryRunner.query(`SET default_transaction_read_only "off"`);
        await queryRunner.query(`SET default_transaction_use_follower_reads = 'off';`);
        await queryRunner.query(`ALTER TABLE "step" DROP CONSTRAINT "FK_e43ba0c1168808a120724d0bd85"`);
        await queryRunner.query(`DROP INDEX "step"@"IDX_e43ba0c1168808a120724d0bd8" CASCADE`);
        await queryRunner.query(`ALTER TABLE "step" RENAME COLUMN "chemicalId" TO "created_at"`);
        await queryRunner.query(`CREATE TABLE "step_chemicals_chemical" ("stepId" int8 NOT NULL, "chemicalId" int8 NOT NULL, CONSTRAINT "PK_2509c715e13ddba6b71791087a8" PRIMARY KEY ("stepId", "chemicalId"))`);
        await queryRunner.query(`CREATE INDEX "IDX_fc0f013cbac13b0a0241573785" ON "step_chemicals_chemical" ("stepId") `);
        await queryRunner.query(`CREATE INDEX "IDX_604ee43998e40a8af82e001fcd" ON "step_chemicals_chemical" ("chemicalId") `);
        await queryRunner.query(`ALTER TABLE "recipe" ADD "isdeleted" bool NOT NULL`);
        await queryRunner.query(`ALTER TABLE "recipe" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "chemical" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "step" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "step" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" ADD CONSTRAINT "FK_fc0f013cbac13b0a0241573785a" FOREIGN KEY ("stepId") REFERENCES "step"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" ADD CONSTRAINT "FK_604ee43998e40a8af82e001fcd4" FOREIGN KEY ("chemicalId") REFERENCES "chemical"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" DROP CONSTRAINT "FK_604ee43998e40a8af82e001fcd4"`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" DROP CONSTRAINT "FK_fc0f013cbac13b0a0241573785a"`);
        await queryRunner.query(`ALTER TABLE "step" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "step" ADD "created_at" int8`);
        await queryRunner.query(`ALTER TABLE "chemical" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "recipe" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "recipe" DROP COLUMN "isdeleted"`);
        await queryRunner.query(`DROP INDEX "step_chemicals_chemical"@"IDX_604ee43998e40a8af82e001fcd" CASCADE`);
        await queryRunner.query(`DROP INDEX "step_chemicals_chemical"@"IDX_fc0f013cbac13b0a0241573785" CASCADE`);
        await queryRunner.query(`DROP TABLE "step_chemicals_chemical"`);
        await queryRunner.query(`ALTER TABLE "step" RENAME COLUMN "created_at" TO "chemicalId"`);
        await queryRunner.query(`CREATE INDEX "IDX_e43ba0c1168808a120724d0bd8" ON "step" ("chemicalId") `);
        await queryRunner.query(`ALTER TABLE "step" ADD CONSTRAINT "FK_e43ba0c1168808a120724d0bd85" FOREIGN KEY ("chemicalId") REFERENCES "chemical"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

}

错误日志 #

query: SELECT * FROM current_schema()
query: SET default_transaction_use_follower_reads = 'on';
query: SET enable_experimental_alter_column_type_general = true
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'migrations'
query: CREATE SEQUENCE "migrations_id_seq"
query failed: CREATE SEQUENCE "migrations_id_seq"
error: 错误：无法在只读事务中执行CREATE SEQUENCE
迁移执行期间出错：
QueryFailedError: 无法在只读事务中执行CREATE SEQUENCE
    在 CockroachQueryRunner.query (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\typeorm\src\driver\cockroachdb\CockroachQueryRunner.ts:396:23)
    在 processTicksAndRejections (node:internal/process/task_queues:95:5)
    在异步 CockroachQueryRunner.executeQueries (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\src\query-runner\BaseQueryRunner.ts:660:13)
    在异步 CockroachQueryRunner.createTable (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\typeorm\src\driver\cockroachdb\CockroachQueryRunner.ts:694:9)
    在异步 MigrationExecutor.createMigrationsTableIfNotExist (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\src\migration\MigrationExecutor.ts:505:13)
    在异步 MigrationExecutor.executePendingMigrations (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\src\migration\MigrationExecutor.ts:195:9)
    在异步 DataSource.runMigrations (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\src\data-source\DataSource.ts:403:13)
    在异步 Object.handler (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\src\commands\MigrationRunCommand.ts:76:13) {
  query: 'CREATE SEQUENCE "migrations_id_seq"',
  parameters: undefined,
  driverError: 错误：无法在只读事务中执行CREATE SEQUENCE
      在 Parser.parseErrorMessage (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\pg-protocol\src\parser.ts:369:69)
      在 Parser.handlePacket (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\pg-protocol\src\parser.ts:188:21)
      在 Parser.parse (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\pg-protocol\src\parser.ts:103:30)
      在 TLSSocket.<匿名> (C:\Users\SAAD\Desktop\AKT_FE\akhtarbackend\akhtar_backend\node_modules\pg-protocol\src\index.ts:7:48)
      在 TLSSocket.emit (node:events:519:28)
      在 TLSSocket.emit (node:domain:488:12)
      在 addChunk (node:internal/streams/readable:559:12)
      在 readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
      在 TLSSocket.Readable.push (node:internal/streams/readable:390:5)
      在 TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23) {
    length: 122,
    severity: 'ERROR',
    code: '25006',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'relational.go',
    line: '181',
    routine: 'buildRelational'
  },
  length: 122,
  severity: 'ERROR',
  code: '25006',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'relational.go',
  line: '181',
  routine: 'buildRelational'
}

内容的提问来源于stack exchange，提问作者pekka 123