search

alkem-io / server

Core server in the Alkemio platform, offering a GraphQL api for interacting with the logical domain model.
http://alkem.io
European Union Public License 1.2
26 stars 4 forks source link

BUG: Error running migration #2210

Closed ccanos closed 2 years ago

ccanos commented 2 years ago

Describe the bug An error came up running migrations: Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT

I don't think I have tampered much with the database, I think it should work because I cleaned the database last week and everything I've done has been through the client-web UI.

:~/DEV/Alkemio/server$ npm run migration:run

> alkemio-server@0.33.4 migration:run
> npm run typeorm-no-entities migration:run

> alkemio-server@0.33.4 typeorm-no-entities
> ts-node -r tsconfig-paths/register ./node_modules/typeorm/cli.js --config src/config/typeorm.cli.config.run.ts "migration:run"

query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'alkemio' AND `TABLE_NAME` = 'migrations_typeorm'
query: SELECT * FROM `alkemio`.`migrations_typeorm` `migrations_typeorm` ORDER BY `id` DESC
57 migrations are already loaded in the database.
58 migrations were found in the source code.
activityParentID21664212708443 is the last executed migration. It was executed on Mon Sep 26 2022 19:18:28 GMT+0200 (Central European Summer Time).
1 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: INSERT INTO preference_definition (id, version, definitionSet, groupName, displayName, description, valueType, type)
    VALUES
      ('d39092f1-6b76-4bb2-84da-ed515948fa34', 1, 'hub', 'Privileges', 'Allow Hub members to create Challenges', 'Allow members of the Hub to create Challenges.', 'boolean', 'AllowMembersToCreateChallenges')
query: SELECT id, preferenceSetId FROM hub
query: INSERT INTO authorization_policy VALUES ('a5b9792f-8ea2-4066-b1ca-23edb62841d9', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('8f294e64-de53-4f76-be73-e3f69c7cae32', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('fff601ba-8160-42c4-834a-a57204306c7f', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('f46f6301-343a-480d-bc6c-b5d3d50d3321', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('fad0294f-bc38-4923-a375-2e89d9b368e5', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('91f3273b-4f99-4696-a6d2-28847fd3b397', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO authorization_policy VALUES ('d9275fbd-94d6-45d0-88da-619b2cda8a84', NOW(), NOW(), 1, '', '', 0, '')
query: INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),
(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),
(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),
(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),
(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')
query failed: INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),
(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),
(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),
(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),
(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),
(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')
error: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
    at Query.Sequence._packetToError (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at Socket.emit (node:domain:475:12)
    --------------------
    at Protocol._enqueue (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at PoolConnection.query (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:198:25)
    at MysqlQueryRunner.<anonymous> (/home/carlos/DEV/Alkemio/server/src/driver/mysql/MysqlQueryRunner.ts:185:36)
    at step (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:143:27)
    at Object.next (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:124:57)
    at fulfilled (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:114:62)
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)',
  sqlState: '23000',
  index: 0,
  sql: "INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')"
}
Migration "hubPref1664603132147" failed, error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
query: ROLLBACK
Error during migration run:
QueryFailedError: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
    at QueryFailedError.TypeORMError [as constructor] (/home/carlos/DEV/Alkemio/server/src/error/TypeORMError.ts:7:9)
    at new QueryFailedError (/home/carlos/DEV/Alkemio/server/src/error/QueryFailedError.ts:9:9)
    at Query.<anonymous> (/home/carlos/DEV/Alkemio/server/src/driver/mysql/MysqlQueryRunner.ts:196:37)
    at Query.<anonymous> (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:488:16)
    at Query.Sequence.end (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at Query.ErrorPacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Query.js:92:8)
    at Protocol._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:43:10) {
  query: "INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')",
  parameters: undefined,
  driverError: Error: ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
      at Query.Sequence._packetToError (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
      at Query.ErrorPacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
      at Protocol._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
      at Parser._parsePacket (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
      at Parser.write (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
      at Protocol.write (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
      at Socket.<anonymous> (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:88:28)
      at Socket.<anonymous> (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:526:10)
      at Socket.emit (node:events:527:28)
      at Socket.emit (node:domain:475:12)
      --------------------
      at Protocol._enqueue (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at PoolConnection.query (/home/carlos/DEV/Alkemio/server/node_modules/mysql/lib/Connection.js:198:25)
      at MysqlQueryRunner.<anonymous> (/home/carlos/DEV/Alkemio/server/src/driver/mysql/MysqlQueryRunner.ts:185:36)
      at step (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:143:27)
      at Object.next (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:124:57)
      at fulfilled (/home/carlos/DEV/Alkemio/server/node_modules/tslib/tslib.js:114:62)
      at processTicksAndRejections (node:internal/process/task_queues:96:5) {
    code: 'ER_NO_REFERENCED_ROW_2',
    errno: 1452,
    sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)',
    sqlState: '23000',
    index: 0,
    sql: "INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),\n" +
      "(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')"
  },
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`alkemio`.`preference`, CONSTRAINT `FK_88881fbd1fef95a0540f7e7d1e2` FOREIGN KEY (`preferenceSetId`) REFERENCES `preference_set` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)',
  sqlState: '23000',
  index: 0,
  sql: "INSERT INTO preference VALUES (UUID(), NOW(), NOW(), 1, 'false', 'a5b9792f-8ea2-4066-b1ca-23edb62841d9', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '8f294e64-de53-4f76-be73-e3f69c7cae32', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fff601ba-8160-42c4-834a-a57204306c7f', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'null'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'f46f6301-343a-480d-bc6c-b5d3d50d3321', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '50f7c09c-3ead-4d20-84fd-4d574510e475'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fad0294f-bc38-4923-a375-2e89d9b368e5', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '6ad59072-e346-47ce-8924-5ec2a329d8a2'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'fbe9a0b7-946d-42e2-8c7a-ce2a73b5e6fd', 'd39092f1-6b76-4bb2-84da-ed515948fa34', '8c46f820-7e82-4c5b-84df-96125aefc50a'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', '91f3273b-4f99-4696-a6d2-28847fd3b397', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'aeb798f4-3980-49cf-bac0-02612c373536'),\n" +
    "(UUID(), NOW(), NOW(), 1, 'false', 'd9275fbd-94d6-45d0-88da-619b2cda8a84', 'd39092f1-6b76-4bb2-84da-ed515948fa34', 'b07a31f8-e3e1-41f1-8607-40f5cee77e7a')"
}

To Reproduce Steps to reproduce the behavior:

  1. Get latest Alkemio develop
  2. npm run migration:run

Expected behavior Migration should work fine

valentinyanakiev commented 2 years ago

the migration fails due to corrupt data and preferenceSetId NULL on multiple hubs.