syn2mas: duplicate key value violates unique constraint "compat_sessions_device_id_unique"

[guyguy333]

Hello,

I'm trying to migrate my users from Synapse to MAS. I use Synapse 1.100.0 and MAS 0.7.0.

However, there is an issue running migrate. I don't have issues when running with dryRun.

What's the right way to fix that ?

Thanks

[2024-02-05T23:55:09.478] [INFO] migrate - Running 80 updates for user @xxxx
[2024-02-05T23:55:10.332] [ERROR] default - error: insert into "compat_sessions" ("compat_session_id", "created_at", "device_id", "is_synapse_admin", "user_id") values ($1, $2, $3, $4, $5) - duplicate key value violates unique constraint "compat_sessions_device_id_unique"
    at Parser.parseErrorMessage (/private/tmp/matrix-authentication-service/tools/syn2mas/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/private/tmp/matrix-authentication-service/tools/syn2mas/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/private/tmp/matrix-authentication-service/tools/syn2mas/node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.<anonymous> (/private/tmp/matrix-authentication-service/tools/syn2mas/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 250,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (device_id)=(XXXXXXXXX) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'compat_sessions',
  column: undefined,
  dataType: undefined,
  constraint: 'compat_sessions_device_id_unique',
  file: 'nbtinsert.c',
  line: '664',
  routine: '_bt_check_unique'
}

[meson800]

I can confirm that I see the same issue. On my homeserver, duplicate device_id's seemingly come in two flavors:

1. People reinstalled an app on a phone and end up with two identical device IDs.
2. Everyone has "XXX Bridge" as one of the device IDs.

I didn't realize that shared secret auth is broken so ended up not using the migration, but just uniquifying the device_ids going into the migration allowed it to complete:

UPDATE access_tokens SET device_id = device_id || '_' || substr(md5(random()::text), 0, 10);

but this is probably not a great idea, since clients will probably happily add non-unique device IDs in the future. From the code, I couldn't get a sense of why the compat_sessions_device_id_unique constraint is there.

[meson800]

Whoops, this is just #2033