directus CockroachDB TransactionRetryWithProtoRefreshError on Collection Creation

Describe the Bug

CockroachDB bootstrap works, but transaction error occurs when trying to create a new collection.

[INTERNAL_SERVER_ERROR] COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 145} {articles 137 1}], old versions still in use

    err: {
      "type": "DatabaseError",
      "message": "COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 145} {articles 137 1}], old versions still in use",
      "stack":
          error: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 145} {articles 137 1}], old versions still in use
              at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
              at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
              at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
              at Socket.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:368:12)
              at readableAddChunk (node:internal/streams/readable:341:9)
              at Readable.push (node:internal/streams/readable:278:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 289,
      "name": "error",
      "severity": "ERROR",
      "code": "40001",
      "hint": "See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html"
    }

To Reproduce

Setup CockroachDB:

docker network create private

mkdir -p crdb/data

docker run -it --rm \
--name=crdb --hostname=crdb \
--net=private -p 26257:26257 -p 8080:8080 \
-v ${PWD}/crdb/data:/cockroach/cockroach-data \
--entrypoint /cockroach/cockroach \
cockroachdb/cockroach:v23.2.0 \
start-single-node \
--insecure \
--listen-addr=crdb:26257 \
--http-addr=crdb:8080

Setup MinIO:

mkdir -p minio/data

docker run -it --rm \
--name=minio --hostname=minio \
--net=private -p 9000:9000 -p 9090:9090 \
--user $(id -u):$(id -g) \
-v ${PWD}/minio/data:/data \
-e MINIO_ROOT_USER=minio.user \
-e MINIO_ROOT_PASSWORD=minio.pass \
minio/minio:latest \
server /data --address ":9000" --console-address ":9090"

Add bucket to MinIO:

docker exec -it minio sh

export MC_HOST_minio=http://minio.user:minio.pass@localhost:9000

mc mb minio/directus

mc anonymous set download minio/directus

exit

Setup Mailhog:

mkdir -p mailhog/maildir

htpasswd -bnBC 4 mail.user mail.pass > mailhog/mailhog-auth.txt

docker run -it --rm \
--name=mailhog --hostname=mailhog \
--net=private -p 1025:1025 -p 8025:8025 \
-v ${PWD}/mailhog/maildir:/home/mailhog/maildir \
-v ${PWD}/mailhog/mailhog-auth.txt:/etc/mailhog-auth.txt \
-e MH_STORAGE=maildir \
-e MH_MAILDIR_PATH=/home/mailhog/maildir \
-e MH_AUTH_FILE=/etc/mailhog-auth.txt \
mailhog/mailhog:v1.0.1

Add directories for Directus:

mkdir -p directus/snapshots directus/temp

mkdir -p directus/extensions/{displays,endpoints,hooks,interfaces,layouts,modules,operations,panels,themes}

Configure Directus - directus/env.txt:

TELEMETRY=false
KEY=random-key
SECRET=random-secret
[email protected]
ADMIN_PASSWORD=password
WEBSOCKETS_ENABLED=true
EXTENSIONS_AUTO_RELOAD=true
TEMP_PATH=/directus/temp
EMAIL_TRANSPORT=smtp
EMAIL_SMTP_HOST=mailhog
EMAIL_SMTP_PORT=1025
EMAIL_SMTP_POOL=true
EMAIL_SMTP_NAME=directus
DB_CLIENT=cockroachdb
DB_CONNECTION_STRING=postgresql://root@crdb:26257/defaultdb?sslmode=disable
STORAGE_LOCATIONS=s3
STORAGE_S3_DRIVER=s3
STORAGE_S3_KEY=minio.user
STORAGE_S3_SECRET=minio.pass
STORAGE_S3_BUCKET=directus
STORAGE_S3_REGION=us-east-1
STORAGE_S3_ENDPOINT=http://minio:9000
STORAGE_S3_FORCE_PATH_STYLE=true

Setup Directus:

docker run -it --rm \
--name=directus --hostname=directus \
--net=private -p 8055:8055 \
-e CONFIG_PATH=./.env \
-v ${PWD}/directus/env.txt:/directus/.env:ro \
-v ${PWD}/directus/snapshots:/directus/snapshots:rw \
-v ${PWD}/directus/temp:/directus/temp:rw \
-v ${PWD}/directus/extensions:/directus/extensions:ro \
directus/directus:10.9.1

http://localhost:8055 [email protected] password

Add collection articles, id = generate uuid, next check all options, save

Transaction error is received. Collection is still created - but not sure what (if anything important) is missing/messed up.

I also tried using the Postgres driver instead of native CockroachDB driver to connect to crdb node. It can't complete the initial bootstrap - fails with on an unsupported ALTER TABLE command.

Clear folders:

rm -r directus/snapshots/*
rm -r directus/temp/*
rm -r directus/uploads/*
rm -r crdb/data/*

directus/env.txt:

DB_CLIENT=postgres
DB_HOST=crdb
DB_PORT=26257
DB_DATABASE=defaultdb
DB_USER=root
DB_PASSWORD=root
DB_VERSION=13.0.0

and re-running each of the docker run commands...

Got the following error(s):

[06:02:01.045] INFO: Initializing bootstrap...
[06:02:01.052] INFO: Installing Directus system tables...
[06:02:02.373] INFO: Running migrations...
[06:02:02.378] INFO: Applying Remove Collection Foreign Keys...
[06:02:02.700] INFO: Applying Remove System Relations...
[06:02:02.704] INFO: Applying Remove System Collections...
[06:02:02.709] INFO: Applying Remove System Fields...
[06:02:02.716] INFO: Applying Add Cascade System Relations...
error: alter table "directus_webhooks" alter column "url" type varchar(255) using ("url"::varchar(255)) - ALTER COLUMN TYPE from varchar to varchar is only supported experimentally
    at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 321,
  severity: 'ERROR',
  code: 'XCEXF',
  detail: undefined,
  hint: 'See: https://go.crdb.dev/issue-v/49329/v23.2\n' +
    '--\n' +
    'you can enable alter column type general support by running `SET enable_experimental_alter_column_type_general = true`',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'alter_column_type.go',
  line: '190',
  routine: 'alterColumnTypeGeneral'
}
[06:02:04.143] INFO: Applying Change Webhook URL Type...

The recommendation to enable the experimental ALTER TABLE feature only works for the current session, so needed to find a way to enable globally:

docker exec -it crdb ./cockroach sql --url="postgresql://root@crdb:26257/defaultdb?sslmode=disable"

SHOW ALL CLUSTER SETTINGS;

SET CLUSTER SETTING sql.defaults.experimental_alter_column_type.enabled = true;

Clearing the CRDB data and starting over, with the global ALTER TABLE setting changed....

That got a little further, but still err'd out in the bootstrap initialization:

[06:23:52.292] INFO: Initializing bootstrap...
[06:23:52.296] INFO: Installing Directus system tables...
[06:23:53.607] INFO: Running migrations...
[06:23:53.612] INFO: Applying Remove Collection Foreign Keys...
[06:23:53.940] INFO: Applying Remove System Relations...
[06:23:53.944] INFO: Applying Remove System Collections...
[06:23:53.949] INFO: Applying Remove System Fields...
[06:23:53.953] INFO: Applying Add Cascade System Relations...
[06:23:55.369] INFO: Applying Change Webhook URL Type...
[06:23:55.557] INFO: Applying Add Relations Sort Field...
[06:23:55.617] INFO: Applying Remove Locked Fields...
[06:23:55.801] INFO: Applying Webhooks Collections Text...
[06:23:55.992] INFO: Applying Add Refresh Interval...
[06:23:56.051] INFO: Applying Make Filesize Nullable...
[06:23:56.279] INFO: Applying Add Collections Accountability...
[06:23:56.462] INFO: Applying Remove Files Interface...
[06:23:56.465] INFO: Applying Rename Interfaces...
[06:23:56.481] INFO: Applying Restructure Relations...
[06:23:57.238] INFO: Applying Add Foreign Key Constraints...
[06:23:57.268] INFO: Applying Add System Fk Triggers...
[06:23:57.951] INFO: Applying Add Collections Icon Color...
[06:23:58.011] INFO: Applying Add Insights...
[06:23:58.179] INFO: Applying Add Deep Clone Config...
[06:23:58.238] INFO: Applying Change Filesize Bigint...
[06:23:58.446] INFO: Applying Add Conditions to Fields...
[06:23:58.505] INFO: Applying Add Default Folder...
[06:23:58.617] INFO: Applying Replace Groups...
[06:23:58.622] INFO: Applying Add Required to Fields...
[06:23:58.806] INFO: Applying Update Groups...
[06:23:58.808] INFO: Applying Change Image Metadata Structure...
[06:23:58.810] INFO: Applying Add Geometry Config...
[06:23:58.869] INFO: Applying Remove Limit Column...
error: alter table "directus_users" drop constraint "directus_users_email_unique" - unimplemented: cannot drop UNIQUE constraint "directus_users_email_unique" using ALTER TABLE DROP CONSTRAINT, use DROP INDEX CASCADE instead
    at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 355,
  severity: 'ERROR',
  code: '0A000',
  detail: undefined,
  hint: 'You have attempted to use a feature that is not yet implemented.\n' +
    'See: https://go.crdb.dev/issue-v/42840/v23.2',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'alter_table_drop_constraint.go',
  line: '93',
  routine: 'droppingUniqueConstraintNotImplemented'
}
[06:23:59.048] INFO: Applying Add Auth Provider...

For the record, regular Postgres works fine for both bootstrap initialization and adding a new collection.

mkdir -p postgres/data

docker run -it --rm \
--name=postgres --hostname=postgres \
--net=private -p 5432:5432 \
-v ${PWD}/postgres/data:/var/lib/postgresql/data \
-e POSTGRES_USER=directus_user \
-e POSTGRES_DB=directus \
-e POSTGRES_PASSWORD=password \
postgres:16.2-alpine3.19

directus/env.txt:

DB_CLIENT=pg
DB_CONNECTION_STRING=postgresql://directus_user:password@postgres:5432/directus

Directus Version

v10.9.1

Hosting Strategy

Self-Hosted (Docker Image)

Feb 14 '24 07:02 kulogix

Using: DB_CLIENT=cockroachdb DB_CONNECTION_STRING=postgresql://root@crdb:26257/defaultdb?sslmode=disable

Same issue on CockroachDB v23.1.14 (docker image cockroachdb/cockroach:v23.1.14)

ERROR: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use
    err: {
      "type": "DatabaseError",
      "message": "COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use",
      "stack":
          error: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use
              at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
              at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
              at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
              at Socket.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:368:12)
              at readableAddChunk (node:internal/streams/readable:341:9)
              at Readable.push (node:internal/streams/readable:278:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 289,
      "name": "error",
      "severity": "ERROR",
      "code": "40001",
      "hint": "See: https://www.cockroachlabs.com/docs/v23.1/transaction-retry-error-reference.html"
    }

Same issue on v22.2.18 (docker image cockroachdb/cockroach:v22.2.18). FYI- Warning: Cockroach Labs will stop providing Assistance Support for v22.2 on June 5, 2024. Prior to that date, upgrade to a more recent version to continue receiving support.

ERROR: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use
    err: {
      "type": "DatabaseError",
      "message": "COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use",
      "stack":
          error: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 149} {articles 137 1}], old versions still in use
              at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
              at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
              at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
              at Socket.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:368:12)
              at readableAddChunk (node:internal/streams/readable:341:9)
              at Readable.push (node:internal/streams/readable:278:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 289,
      "name": "error",
      "severity": "ERROR",
      "code": "40001",
      "hint": "See: https://www.cockroachlabs.com/docs/v22.2/transaction-retry-error-reference.html"
    }

Same issue on v22.1.22 (docker image cockroachdb/cockroach:v22.1.22). FYI- Warning: CockroachDB v22.1 is no longer supported.

ERROR: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 124} {articles 137 1}], old versions still in use
    err: {
      "type": "DatabaseError",
      "message": "COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 124} {articles 137 1}], old versions still in use",
      "stack":
          error: COMMIT; - restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{directus_users 106 124} {articles 137 1}], old versions still in use
              at Parser.parseErrorMessage (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
              at Parser.handlePacket (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
              at Parser.parse (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
              at Socket.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:368:12)
              at readableAddChunk (node:internal/streams/readable:341:9)
              at Readable.push (node:internal/streams/readable:278:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 289,
      "name": "error",
      "severity": "ERROR",
      "code": "40001",
      "hint": "See: https://www.cockroachlabs.com/docs/v22.1/transaction-retry-error-reference.html"
    }

Feb 15 '24 18:02 kulogix

Sounds like it may no longer be fully compatible with the postgres driver used in Directus 🤔

Feb 15 '24 18:02 br41nslug

I am having similar issues see below with Planetscale (mysql). Neon and Supabase working fine they both use postgres.

sqlMessage: `target: hq.-.primary: vttablet: rpc error: code = InvalidArgument desc = Duplicate column name 'accountability' (errno 1060) (sqlstate 42S21) (CallerID: 2ii9bi4ntfjmtcs6rejh): Sql: "alter table directus_collections add column accountability varchar(255) default 'all'", BindVars: {REDACTED}`,

Feb 17 '24 02:02 glennlaysonjr

Did further testing with prior versions of Directus (docker images).

9.20.4 and 9.21.0 (11/17/2022) work (with the latest version of CockroachDB, v23.2.1), but all later versions of Directus are failing to create a new collection on CockroachDB without the TransactionRetryWithProtoRefreshError error.

Looks like something introduced in 9.21.1 (from 11/28/2022, no docker image available) caused it to fail. 9.21.2 was just a markdown update, but does have a docker image available. Appears the "9.21" docker image was published 11/28 and is based on 9.21.2.

Hoping someone can look into these changes from 9.21.1, find and fix whatever broke CockroachDB compatibility for over a year.

v9.21.1 (November 28, 2022)

🚀 Improvements

App
- #16512 v-menu pointer event tweaks (by @azrikahar)
- #16511 Improve v-icon performance (by @azrikahar)
API
- #16501 Set auth_data to null when updating user provider or external_identifier (by @azrikahar)
- #16499 Remove named timezone usage on MySQL (by @licitdev)
Extensions
- #15989 Use esbuild to build typescript extensions (by @nickrum)

🐛 Bug Fixes

shared
- #16641 Fixup: Re-add entrypoint type definition files in @directus/shared (by @paescuj)
- #16606 Re-add entrypoint type definition files in @directus/shared (by @paescuj)
App
- #16570 fix missing collection after sorting in M2A (by @azrikahar)
- #16518 Fix .module-nav-resize-handle layout (by @d1rOn)
API
- #16562 Propagate mutation options for schema apply (by @licitdev)

🧽 Optimizations

Misc.
- #16591 Optimize stores hydration calls (by @azrikahar)
- #16481 Clean-up dependencies (by @paescuj)

Feb 25 '24 03:02 kulogix