[Database] "security_keys" already exists

[icarusfactor]

Describe the bug
When running "npm run start" I get a database error what seems to be related to but not the same issue ER_TABLE_EXISTS #1211 and migration table duplicate security key bug. But the data I have looks nothing like that and has lots more columns and have remove the db and restarted to no avail.

To Reproduce
Steps to reproduce the behavior:

1. As spacebar user, I run "npm run start"
2. Then it throws errors while connected to postgresql database.
3. See error

Expected behavior
This should start the service on port 3001

Console Logs

[System] linux 5.14.0-611.20.1.el9_7.x86_64 x64
[System] AlmaLinux 9.7 (Moss Jungle Cat) (No build ID)
[Process] Running with PID: 291958
[Process] Starting with 1 threads
[Database] Connecting to postgres db
[Database] Applying missing migrations, if any.
Migration "webauthn1675044825710" failed, error: relation "security_keys" already exists
QueryFailedError: relation "security_keys" already exists
at PostgresQueryRunner.query (/var/www/spacebar.userspace.org/server/node_modules/typeorm/driver/src/driver/postgres/PostgresQueryRunner.ts:325:19)
at process.processTicksAndRejections (node:internal/process/task_queues:103:5)
at async webauthn1675044825710.up (/var/www/spacebar.userspace.org/server/src/util/migration/postgres/1675044825710-webauthn.ts:25:9)
at async MigrationExecutor.executePendingMigrations (/var/www/spacebar.userspace.org/server/node_modules/typeorm/migration/src/migration/MigrationExecutor.ts:336:17)
at async DataSource.runMigrations (/var/www/spacebar.userspace.org/server/node_modules/typeorm/data-source/src/data-source/DataSource.ts:404:13)
at async initDatabase (/var/www/spacebar.userspace.org/server/src/util/util/Database.ts:136:5)
at async main (/var/www/spacebar.userspace.org/server/src/bundle/Server.ts:61:5) {
query: 'CREATE TABLE "security_keys" ("id" character varying NOT NULL, "user_id" character varying, "key_id" character varying NOT NULL, "public_key" character varying NOT NULL, "counter" integer NOT NULL, "name" character varying NOT NULL, CONSTRAINT "PK_6e95cdd91779e7cca06d1fff89c" PRIMARY KEY ("id"))',
parameters: undefined,
driverError: error: relation "security_keys" already exists
at /var/www/spacebar.userspace.org/server/node_modules/pg/lib/client.js:545:17
at process.processTicksAndRejections (node:internal/process/task_queues:103:5)
at async PostgresQueryRunner.query (/var/www/spacebar.userspace.org/server/node_modules/typeorm/driver/src/driver/postgres/PostgresQueryRunner.ts:254:25)
at async webauthn1675044825710.up (/var/www/spacebar.userspace.org/server/src/util/migration/postgres/1675044825710-webauthn.ts:25:9)
at async MigrationExecutor.executePendingMigrations (/var/www/spacebar.userspace.org/server/node_modules/typeorm/migration/src/migration/MigrationExecutor.ts:336:17)
at async DataSource.runMigrations (/var/www/spacebar.userspace.org/server/node_modules/typeorm/data-source/src/data-source/DataSource.ts:404:13)
at async initDatabase (/var/www/spacebar.userspace.org/server/src/util/util/Database.ts:136:5)
at async main (/var/www/spacebar.userspace.org/server/src/bundle/Server.ts:61:5) {
length: 107,
severity: 'ERROR',
code: '42P07',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'heap.c',
line: '1150',
routine: 'heap_create_with_catalog'
},
length: 107,
severity: 'ERROR',
code: '42P07',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'heap.c',
line: '1150',
routine: 'heap_create_with_catalog'
}

System Information:

• OS: Alma Linux
• Version 9.7
• • Node Version: [e.g Node v18.7.0]
• Python 3 Version: [e.g Python 3.10.6]

Env and Software info

The number of threads to run Spacebar on.

THREADS=1

The port the server will listen on.

PORT=3001

The database connection string. Defaults to SQLite3 at project root (database.db).

DATABASE="postgres://spacebar_access:PASSWORD@localhost:5432/spacebar"

• Database: Postgresql 16
• Thread Count: 1

[TheArcaneBrony]

Just fixed tests in CI, but I haven't been able to reproduce this error.

Example from CI: https://github.com/spacebarchat/server/actions/runs/21156923322/job/60843501430#step:6:10624

[TheArcaneBrony]

(For what it's worth, the apply-migrations script here does nothing different than just starting the server)

[TheArcaneBrony]

Gonna try reproducing this on alma9, taking some guesses...
Get base system:

• Obtain alma9 LXC container image (for systemd-nspawn)
• Set root password (lol)
• Install vim (no KiTTY terminfo package :c)
• dnf module enable nodejs:24 (No clue which version you're using, so I just picked latest)
• dnf module enable postgresql:16 (No clue which version you're using, so I just picked latest - 16 is outdated and untested, though.)
• dnf in nodejs git gcc postgresql postgresql-server
• PGSETUP_INITDB_OPTIONS='--encoding=UTF8 --locale=C.UTF-8 --data-checksums' postgresql-setup --initdb (initdb options i normally use, shouldnt matter as the defaults are sane too)
• systemctl enable --now postgresql
• echo 'CREATE DATABASE spacebar;' | psql -U postgresql

Now we have the basics...

• mkdir -p git/sb-server && cd git/sb-server
• git clone https://github.com/spacebarchat/server.git . -v
• npm i --foreground-scripts
• npm run build
• DATABASE=postgres://postgres@127.0.0.1/spacebar

Result:

[Database] Connecting to postgres db
[Database] This appears to be a fresh database. Running initial DDL.
[Database] Found initial migration file, running it.
[Database] Applying missing migrations, if any.
[Database] Connected
[Config] Loading configuration...

(aka: no such error?)

[icarusfactor]

Thanks for the testing process run.
I don't get what you had gotten:

[Database] This appears to be a fresh database. Running initial DDL.

How do I get it to run the DDL?

Node Version: v24.11.1
Python 3 Version: Python 3.9.25
Postgresql 16.11

I tried your steps with postgres user nd ip address along with PGSETUP_INITDB_OPTIONS and just does not work and I get the same error.
How/where does it recognize if its a fresh database or not.
As this is not happening in my first run process.

Does the "npm run start" have a way I can force it to run the initial DDL or do it manually?

As it appears to be a problem in detecting a fresh database or not.

As I simply type the below to create base database.
create database spacebar;

postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
spacebar | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |

postgres=# \c spacebar
You are now connected to database "spacebar" as user "postgres".
spacebar=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------------+-------+----------
public | application_commands | table | postgres
public | applications | table | postgres
public | attachments | table | postgres
public | audit_logs | table | postgres
public | automod_rules | table | postgres
public | backup_codes | table | postgres
public | badges | table | postgres
public | bans | table | postgres
public | categories | table | postgres
public | channels | table | postgres
public | client_release | table | postgres
public | cloud_attachments | table | postgres
public | config | table | postgres
public | connected_accounts | table | postgres
public | connection_config | table | postgres
public | embed_cache | table | postgres
public | emojis | table | postgres
public | guilds | table | postgres
public | instance_bans | table | postgres
public | invites | table | postgres
public | member_roles | table | postgres
public | members | table | postgres
public | message_channel_mentions | table | postgres
public | message_role_mentions | table | postgres
public | message_stickers | table | postgres
public | message_user_mentions | table | postgres
public | messages | table | postgres
public | migrations | table | postgres
public | notes | table | postgres
public | rate_limits | table | postgres
public | read_states | table | postgres
public | recipients | table | postgres
public | relationships | table | postgres
public | report_menus | table | postgres
public | roles | table | postgres
public | security_keys | table | postgres
public | security_settings | table | postgres
public | sessions | table | postgres
public | sticker_packs | table | postgres
public | stickers | table | postgres
public | stream_sessions | table | postgres
public | streams | table | postgres
public | team_members | table | postgres
public | teams | table | postgres
public | templates | table | postgres
public | user_settings | table | postgres
public | user_settings_protos | table | postgres
public | users | table | postgres
public | valid_registration_tokens | table | postgres
public | voice_states | table | postgres
public | webhooks | table | postgres
(51 rows)

Again posting the error I get below when running npm as root or user "npm run start".

[Database] Connecting to postgres db
[Database] Applying missing migrations, if any.
Migration "webauthn1675044825710" failed, error: relation "security_keys" already exists

[TheArcaneBrony]

Running the DDL is exactly what it does when it logs that it's doing so.

It checks whether to run the initial DDL by checking if the config table exists at all. (See src/util/util/Database.ts:115)

Did you DROP DATABASE spacebar; inbetween attempts?
What does the migrations table contain for you?

[icarusfactor]

Yes, I usually drop the db spacebar and try again. But I have even removed and reinstalled postgrsql 16 and some times ran initdb after removing the data directory as well.

postgres=# \c spacebar
You are now connected to database "spacebar" as user "postgres".
spacebar=# SELECT * FROM migrations;
id | timestamp | name
----+-----------+------
(0 rows)

I did forced it to run the DDL. it gets to other duplicate errors. Will debug this
further and follow up. Maybe its some loop it gets stuck in when performing
the queries.

Thanks for the help.

[TheArcaneBrony]

Unsure what works for you, but I'd like to go over this synchronously with you (ie. a quick call).

Another thing you could try: Can you run spacebar with this?
DB_LOGGING=true npm run start |& tee start.log ?
This will start the server with verbose query logging enabled, as well as copying any output to a log file.