Misskey: [使い方の質問] マイグレーション時にエラーが出る

[tamaina]

Summary

マイグレーションをしようとすると以下のようなエラーが出ます。

D:\misskey-3>ts-node ./node_modules/typeorm/cli.js migration:run
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = current_schema() AND "table_name" = 'migrations'
query: CREATE TABLE "migrations" ("id" SERIAL NOT NULL, "timestamp" bigint NOT NULL, "name" character varying NOT NULL, CONSTRAINT "PK_8c82d7f526340ab734260ea46be" PRIMARY KEY ("id"))
query: SELECT * FROM "migrations" "migrations"
0 migrations are already loaded in the database.
4 migrations were found in the source code.
4 migrations are new migrations that needs to be executed.
query: START TRANSACTION
query: CREATE TYPE "page_visibility_enum" AS ENUM('public', 'followers', 'specified')
query failed: CREATE TYPE "page_visibility_enum" AS ENUM('public', 'followers', 'specified')
error: { error: 型"page_visibility_enum"はすでに存在します
    at Connection.parseE (D:\misskey-3\node_modules\pg\lib\connection.js:601:11)      
    at Connection.parseMessage (D:\misskey-3\node_modules\pg\lib\connection.js:398:19)
（以下省略）

どうすればマイグレーションを完了させることができますか？

[acid-chicken]

Off Topic: Bug Report でも Feature Request でもない Issue はここから建てられますので参考までに。

[tamaina]

@acid-chicken 今知った

[u1-liquid]

最初からアプデしてたのではなくv11の途中から立てた鯖でマイグレーションが初めての鯖なら多分init時点で最新のorm modelsでdbが作成されたはずなので新しく出来たmigrationだけ適用されて大丈夫です。
本来はdb init の時ormのマイグレーション対応関連作業が行わなければなりませんがまだその処理がてきておりませんのでこのようなエラーが出る模様・・・

解決方法としては最初鯖を立てた時以前のmigrationファイルをすべて削除すると行けると思います。

[tamaina]

@u1-liquid ありがとうございます！

[u1-liquid]

これのちょっと丁寧な対応策、なかなか見当たりませんね
db init の時最新のマイグレーションバージョンだけ残して中身を空にしたままmigration:run走ると
一応なんとかなるかもですが・・・

[mei23]

db init を マイグレーションを含まない初期のスキーマで行えば良さそう
→ 現行ソースのEntityをもとにinitする仕組みなので無理

migrationsテーブルにどこまでマイグレしたかが入ってるのでこれをいじれば飛ばせるかも
→ でも手動でいじくっていいのか不明

 id |   timestamp   |               name                
----+---------------+-----------------------------------
  1 | 1556348509290 | Pages1556348509290
  2 | 1556746559567 | UserProfile1556746559567
  3 | 1557476068003 | PinnedUsers1557476068003
  4 | 1557761316509 | AddSomeUrls1557761316509
  5 | 1557932705754 | ObjectStorageSetting1557932705754
  6 | 1558072954435 | PageLike1558072954435
  7 | 1558103093633 | UserGroup1558103093633
  8 | 1558257926829 | UserGroupInvite1558257926829
  9 | 1558266512381 | UserListJoining1558266512381

[rinsuki]

そもそもsync使わずにテーブル構造変更はmigrationsのみで行うべきだった説

[rinsuki]

でもそれ言いだすとv12ができそうだな

[syuilo]

migrationに一番初期のスキーマを生成するマイグレーションを追加して、npm run init を廃止して npm run migrate を実行させるようにするというのはどうですか？

[syuilo]

migrationに一番初期のスキーマを生成するマイグレーションを追加

これが困難だということが発覚
初期に遡ってマイグレ生成してみたけど、動的に生成されるタイプのチャートエンティティが認識されない(マイグレの自動生成にはエンティティファイルを指定する必要があるから、ファイルとして存在しないチャートエンティティが認識されない)

[u1-liquid]

migrationに一番初期のスキーマを生成するマイグレーションを追加

空のマイグレーションファイルを作成するといけるらしいけどやっては見なかった

[syuilo]

頑張って次のようなormconfig.json作れたけどなぜかやっぱり認識されない

ormconfig.json

{
  "entitySchemas": [
    {
      "name": "__chart__active_users",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_count": {
          "type": "bigint"
        },
        "___remote_count": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__drive",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_totalCount": {
          "type": "bigint"
        },
        "___local_totalSize": {
          "type": "bigint"
        },
        "___local_incCount": {
          "type": "bigint"
        },
        "___local_incSize": {
          "type": "bigint"
        },
        "___local_decCount": {
          "type": "bigint"
        },
        "___local_decSize": {
          "type": "bigint"
        },
        "___remote_totalCount": {
          "type": "bigint"
        },
        "___remote_totalSize": {
          "type": "bigint"
        },
        "___remote_incCount": {
          "type": "bigint"
        },
        "___remote_incSize": {
          "type": "bigint"
        },
        "___remote_decCount": {
          "type": "bigint"
        },
        "___remote_decSize": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__federation",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___instance_total": {
          "type": "bigint"
        },
        "___instance_inc": {
          "type": "bigint"
        },
        "___instance_dec": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__hashtag",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_count": {
          "type": "bigint"
        },
        "___remote_count": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__instance",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___requests_failed": {
          "type": "bigint"
        },
        "___requests_succeeded": {
          "type": "bigint"
        },
        "___requests_received": {
          "type": "bigint"
        },
        "___notes_total": {
          "type": "bigint"
        },
        "___notes_inc": {
          "type": "bigint"
        },
        "___notes_dec": {
          "type": "bigint"
        },
        "___notes_diffs_normal": {
          "type": "bigint"
        },
        "___notes_diffs_reply": {
          "type": "bigint"
        },
        "___notes_diffs_renote": {
          "type": "bigint"
        },
        "___users_total": {
          "type": "bigint"
        },
        "___users_inc": {
          "type": "bigint"
        },
        "___users_dec": {
          "type": "bigint"
        },
        "___following_total": {
          "type": "bigint"
        },
        "___following_inc": {
          "type": "bigint"
        },
        "___following_dec": {
          "type": "bigint"
        },
        "___followers_total": {
          "type": "bigint"
        },
        "___followers_inc": {
          "type": "bigint"
        },
        "___followers_dec": {
          "type": "bigint"
        },
        "___drive_totalFiles": {
          "type": "bigint"
        },
        "___drive_totalUsage": {
          "type": "bigint"
        },
        "___drive_incFiles": {
          "type": "bigint"
        },
        "___drive_incUsage": {
          "type": "bigint"
        },
        "___drive_decFiles": {
          "type": "bigint"
        },
        "___drive_decUsage": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__network",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___incomingRequests": {
          "type": "bigint"
        },
        "___outgoingRequests": {
          "type": "bigint"
        },
        "___totalTime": {
          "type": "bigint"
        },
        "___incomingBytes": {
          "type": "bigint"
        },
        "___outgoingBytes": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__notes",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_total": {
          "type": "bigint"
        },
        "___local_inc": {
          "type": "bigint"
        },
        "___local_dec": {
          "type": "bigint"
        },
        "___local_diffs_normal": {
          "type": "bigint"
        },
        "___local_diffs_reply": {
          "type": "bigint"
        },
        "___local_diffs_renote": {
          "type": "bigint"
        },
        "___remote_total": {
          "type": "bigint"
        },
        "___remote_inc": {
          "type": "bigint"
        },
        "___remote_dec": {
          "type": "bigint"
        },
        "___remote_diffs_normal": {
          "type": "bigint"
        },
        "___remote_diffs_reply": {
          "type": "bigint"
        },
        "___remote_diffs_renote": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__per_user_drive",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___totalCount": {
          "type": "bigint"
        },
        "___totalSize": {
          "type": "bigint"
        },
        "___incCount": {
          "type": "bigint"
        },
        "___incSize": {
          "type": "bigint"
        },
        "___decCount": {
          "type": "bigint"
        },
        "___decSize": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__per_user_following",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_followings_total": {
          "type": "bigint"
        },
        "___local_followings_inc": {
          "type": "bigint"
        },
        "___local_followings_dec": {
          "type": "bigint"
        },
        "___local_followers_total": {
          "type": "bigint"
        },
        "___local_followers_inc": {
          "type": "bigint"
        },
        "___local_followers_dec": {
          "type": "bigint"
        },
        "___remote_followings_total": {
          "type": "bigint"
        },
        "___remote_followings_inc": {
          "type": "bigint"
        },
        "___remote_followings_dec": {
          "type": "bigint"
        },
        "___remote_followers_total": {
          "type": "bigint"
        },
        "___remote_followers_inc": {
          "type": "bigint"
        },
        "___remote_followers_dec": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__per_user_notes",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___total": {
          "type": "bigint"
        },
        "___inc": {
          "type": "bigint"
        },
        "___dec": {
          "type": "bigint"
        },
        "___diffs_normal": {
          "type": "bigint"
        },
        "___diffs_reply": {
          "type": "bigint"
        },
        "___diffs_renote": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__per_user_reaction",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_count": {
          "type": "bigint"
        },
        "___remote_count": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__test_grouped",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___foo_total": {
          "type": "bigint"
        },
        "___foo_inc": {
          "type": "bigint"
        },
        "___foo_dec": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__test_unique",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___foo": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__test",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___foo_total": {
          "type": "bigint"
        },
        "___foo_inc": {
          "type": "bigint"
        },
        "___foo_dec": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    },
    {
      "name": "__chart__users",
      "columns": {
        "id": {
          "type": "integer",
          "primary": true,
          "generated": true
        },
        "date": {
          "type": "integer"
        },
        "group": {
          "type": "varchar",
          "length": 128,
          "nullable": true
        },
        "span": {
          "type": "enum",
          "enum": [
            "hour",
            "day"
          ]
        },
        "unique": {
          "type": "jsonb",
          "default": {}
        },
        "___local_total": {
          "type": "bigint"
        },
        "___local_inc": {
          "type": "bigint"
        },
        "___local_dec": {
          "type": "bigint"
        },
        "___remote_total": {
          "type": "bigint"
        },
        "___remote_inc": {
          "type": "bigint"
        },
        "___remote_dec": {
          "type": "bigint"
        }
      },
      "indices": [
        {
          "columns": [
            "date"
          ]
        },
        {
          "columns": [
            "span"
          ]
        },
        {
          "columns": [
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date"
          ]
        },
        {
          "columns": [
            "date",
            "group"
          ]
        },
        {
          "columns": [
            "span",
            "date",
            "group"
          ]
        }
      ]
    }
  ],
  "migrations": [
    "migration/*.ts"
  ],
  "cli": {
    "migrationsDir": "migration"
  }
}

[syuilo]

この状態でマイグレ生成しようとしても

No changes in database schema were found - cannot generate a migration.

といわれる(データベースはリセット済み)

[syuilo]

JSONをそれぞれファイルにして "entitySchemas":["entity-schema/*.json"], みたいにしても認識しない

[syuilo]

謎

[syuilo]

∩(´；ヮ；｀)∩ンヒィ～～～～～～～～～～～～～～～～～～～～

[syuilo]

https://github.com/typeorm/typeorm/issues/1731#issuecomment-371878181

[syuilo]

あっ、ormconfigをjsonじゃなくてjs形式にしたらできた

[syuilo]

これからMisskeyのインスタンスを作成する人にとってはもうこの問題は大丈夫だけど、既にインスタンスを作成している人はアップデートに失敗しそう(Initのマイグレが実行されていないため)
データベースに Init1000000000000 を既に実行したという記録を手動で挿入すれば回避できるけど...

[syuilo]

もしタイムスタンプが1000000000000以上のマイグレ実行ログがデータベースにあれば、Init1000000000000 をログに挿入する処理をどこかで実行するようにしたい(めんどい)

[syuilo]

Init1000000000000 をログに挿入するツールを用意して、管理者に手動実行してもらうのでもいいけど、Dockerだと厳しそう