server icon indicating copy to clipboard operation
server copied to clipboard
verified publisher icon spacebarchat

MariaDB - "Foreign key constraint is incorrectly formed" with empty database

Open siladjithomas opened this issue 7 months ago • 2 comments

Describe the bug After setting up a fresh database on MariaDB on Ubuntu, when I try to run npm run sync:db, I always get the error QueryFailedError: ER_CANT_CREATE_TABLE: Can't create table spacebar.message_user_mentions (errno: 150 "Foreign key constraint is incorrectly formed") even when there is nothing in the database.

When I do the same with SQLite, there is no error.

To Reproduce

  1. Follow the steps in the docs up until npm run setup.
  2. Set the MariaDB database string in .env, eg. DATABASE=mariadb://spacebar:<PASSWORD>@localhost/spacebar
  3. Run npm run sync:db

Expected behavior There should be no errors outputted and the migration should be successful.

Console Logs

spacebar@server:~/server$ npm run sync:db

> [email protected] sync:db
> npm run build && node scripts/syncronise.js


> [email protected] build
> tsc -p .

[Database] Connecting to mariadb db
/home/spacebar/server/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:162
                        return fail(new QueryFailedError_1.QueryFailedError(query, parameters, err));
                                    ^

QueryFailedError: ER_CANT_CREATE_TABLE: Can't create table `spacebar`.`message_user_mentions` (errno: 150 "Foreign key constraint is incorrectly formed")
    at Query.<anonymous> (/home/spacebar/server/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:162:37)
    at Query.<anonymous> (/home/spacebar/server/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/home/spacebar/server/node_modules/mysql/lib/Connection.js:488:16)
    at Sequence.end (/home/spacebar/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at Query.ErrorPacket (/home/spacebar/server/node_modules/mysql/lib/protocol/sequences/Query.js:92:8)
    at Protocol._parsePacket (/home/spacebar/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/spacebar/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/spacebar/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/spacebar/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/spacebar/server/node_modules/mysql/lib/Connection.js:88:28) {
  query: 'ALTER TABLE `message_user_mentions` ADD CONSTRAINT `FK_a343387fc560ef378760681c236` FOREIGN KEY (`messagesId`) REFERENCES `messages`(`id`) ON DELETE CASCADE ON UPDATE CASCADE',
  parameters: undefined,
  driverError: Error: ER_CANT_CREATE_TABLE: Can't create table `spacebar`.`message_user_mentions` (errno: 150 "Foreign key constraint is incorrectly formed")
      at Sequence._packetToError (/home/spacebar/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
      at Query.ErrorPacket (/home/spacebar/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
      at Protocol._parsePacket (/home/spacebar/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
      at Parser._parsePacket (/home/spacebar/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
      at Parser.write (/home/spacebar/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
      at Protocol.write (/home/spacebar/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
      at Socket.<anonymous> (/home/spacebar/server/node_modules/mysql/lib/Connection.js:88:28)
      at Socket.<anonymous> (/home/spacebar/server/node_modules/mysql/lib/Connection.js:526:10)
      at Socket.emit (node:events:517:28)
      at addChunk (node:internal/streams/readable:368:12)
      --------------------
      at Protocol._enqueue (/home/spacebar/server/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at PoolConnection.query (/home/spacebar/server/node_modules/mysql/lib/Connection.js:198:25)
      at /home/spacebar/server/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:151:36
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    code: 'ER_CANT_CREATE_TABLE',
    errno: 1005,
    sqlMessage: 'Can\'t create table `spacebar`.`message_user_mentions` (errno: 150 "Foreign key constraint is incorrectly formed")',
    sqlState: 'HY000',
    index: 0,
    sql: 'ALTER TABLE `message_user_mentions` ADD CONSTRAINT `FK_a343387fc560ef378760681c236` FOREIGN KEY (`messagesId`) REFERENCES `messages`(`id`) ON DELETE CASCADE ON UPDATE CASCADE'
  },
  code: 'ER_CANT_CREATE_TABLE',
  errno: 1005,
  sqlMessage: 'Can\'t create table `spacebar`.`message_user_mentions` (errno: 150 "Foreign key constraint is incorrectly formed")',
  sqlState: 'HY000',
  index: 0,
  sql: 'ALTER TABLE `message_user_mentions` ADD CONSTRAINT `FK_a343387fc560ef378760681c236` FOREIGN KEY (`messagesId`) REFERENCES `messages`(`id`) ON DELETE CASCADE ON UPDATE CASCADE'
}

Node.js v18.19.1

System Information (please complete the following information):

  • OS: Ubuntu
  • Version: Ubuntu 24.04.2 LTS
  • Node Version: v18.19.1
  • Python 3 Version: 3.12.3 (main, Feb 4 2025, 14:48:35) [GCC 13.3.0]

Env and Software info

  • Release: -
  • Branch (if release is not applicable): master
  • Commit Hash (if release is not applicable): 2429259
  • Database: mysqld Ver 10.11.11-MariaDB-0ubuntu0.24.04.2 for debian-linux-gnu on aarch64 (Ubuntu 24.04)
  • Reverse Proxy: nginx version: nginx/1.24.0 (Ubuntu)
  • Thread Count: 1

Additional context

siladjithomas avatar May 19 '25 10:05 siladjithomas

MariaDB is currently broken, use PostgreSQL instead. Also, sync:db is for sqlite or development only.

TheArcaneBrony avatar May 28 '25 06:05 TheArcaneBrony

Same thing with PostgreSQL 17 on Ubuntu 24.04.2 LTS though, with the latest from the master branch:

spacebar@dev02:~/server$ npm run start

> [email protected] start
> node dist/bundle/start.js


███████╗██████╗  █████╗  ██████╗███████╗██████╗  █████╗ ██████╗ 
██╔════╝██╔══██╗██╔══██╗██╔════╝██╔════╝██╔══██╗██╔══██╗██╔══██╗
███████╗██████╔╝███████║██║     █████╗  ██████╔╝███████║██████╔╝
╚════██║██╔═══╝ ██╔══██║██║     ██╔══╝  ██╔══██╗██╔══██║██╔══██╗
███████║██║     ██║  ██║╚██████╗███████╗██████╔╝██║  ██║██║  ██║
╚══════╝╚═╝     ╚═╝  ╚═╝ ╚═════╝╚══════╝╚═════╝ ╚═╝  ╚═╝╚═╝  ╚═╝

                spacebar-server | Pre-release (f03c620)

Commit Hash: f03c6209a42ae3e922bdb86954de04f1ae6daaa8 (f03c620)
Cores: 10 (Using 1 thread(s).)

[Path] running in /home/spacebar/server/dist/bundle
[CPU] Neoverse-N1 Cores x10
[System] linux arm64
[Process] running with PID: 1336292
[Process] starting with 1 threads
[Database] Connecting to postgres db
[Database] Applying missing migrations, if any.
Migration "webauthn1675044825710" failed, error: Relation »security_keys« existiert bereits
QueryFailedError: Relation »security_keys« existiert bereits
    at PostgresQueryRunner.query (/home/spacebar/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:216:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async webauthn1675044825710.up (/home/spacebar/server/dist/util/migration/postgres/1675044825710-webauthn.js:24:9)
    at async MigrationExecutor.executePendingMigrations (/home/spacebar/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/home/spacebar/server/node_modules/typeorm/data-source/DataSource.js:265:35)
    at async initDatabase (/home/spacebar/server/dist/util/util/Database.js:107:9)
    at async main (/home/spacebar/server/dist/bundle/Server.js:47: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« existiert bereits
      at /home/spacebar/server/node_modules/pg/lib/client.js:545:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/home/spacebar/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:181:25)
      at async webauthn1675044825710.up (/home/spacebar/server/dist/util/migration/postgres/1675044825710-webauthn.js:24:9)
      at async MigrationExecutor.executePendingMigrations (/home/spacebar/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/home/spacebar/server/node_modules/typeorm/data-source/DataSource.js:265:35)
      at async initDatabase (/home/spacebar/server/dist/util/util/Database.js:107:9)
      at async main (/home/spacebar/server/dist/bundle/Server.js:47:5) {
    length: 113,
    severity: 'FEHLER',
    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: '1160',
    routine: 'heap_create_with_catalog'
  },
  length: 113,
  severity: 'FEHLER',
  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: '1160',
  routine: 'heap_create_with_catalog'
}

siladjithomas avatar May 28 '25 13:05 siladjithomas