📌

[Docker + Prisma] MySQL containerにprisma migrate devするまで

2023/01/30に公開

Docker

記事の経緯と目的

NestJS学習中にDB向けに用意したMySQL containerにprisma migrate devしようとしたところ以下のエラーが起きました。

Error: P3014

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow

Original error: Error code: P1010

User `MS-DDD` was denied access on the database `fcc-nestjs-tutorial`

このエラー解消のため公式DocsにあるようにUser MS-DDDにCREATE, ALTER, DROP, REFERENCESの権限を付与しようとしましたが手こずりました。他の方々も上記エラーに関する記事を書かれているのですが、Docker上のMySQLに対して書かれているものが日本、海外問わず見つからず時間がかかりました。備忘録として残すとともに、誰かの役に立てばと思い対処法をまとめることにしました。

参考 Prisma Docs: About the shadow database

なぜ`prisma migrate dev`に`CREATE`, `ALTER`, `DROP`, `REFERENCES`が必要なのか

これは_prisma_migrationsというテーブルをデータベース側で管理しますが、その際に上記4つの権限が必要だからと考えています。本題ではないため詳細は割愛します。

参考 Mental model

留意事項

この記事はエラーが起きた状況説明=>それを解決する際に行った試行錯誤=>解決という流れで執筆しています。解決法のみを記載しているわけではないのである程度の長さになります。

いや細けぇことはいい。つまり何やったのか。という人向けに

# docker composeのhostname mysqlにrootユーザーとしてログイン
docker container exec -it mysql mysql -h mysql -u root -p

# 権限をユーザーに付与
# (env file内のMYSQL_USERにMS-DDDと設定してある)
grant create, alter, drop, references on *.* to `MS-DDD`@`%`;

# ログアウト
exit

# migrate dev
npx prisma migrate dev --name init

環境の説明からprisma migrate devのエラーまで

環境

"@nestjs/common": "^9.0.0",
"prisma": "^4.9.0",
"@prisma/client": "^4.9.0",
Docker version 20.10.21
Docker Compose version v2.13.0

docker-compose.yml

version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql
    env_file:
      - ./env/mysql.env
    volumes:
      - ./db/data:/var/lib/mysql
    ports:
      - 3306:3306
  mysql-test:
    image: mysql:8.0
    container_name: mysql-test
    env_file:
      - ./env/mysql.env
    ports:
      - 3307:3306

上記mysql.envにはMYSQL_ROOT_PASSWORD、MYSQL_DATABASE、MYSQL_USER、MYSQL_PASSWORDの4つを設定しています。

参考 Dockerhub: MySQL
https://hub.docker.com/_/mysql

Initialize Prisma

npx primsa init

✔ Your Prisma schema was created at prisma/schema.prisma
  You can now open it in your favorite editor.

warn You already have a .gitignore file. Don't forget to add `.env` in it to not commit any private information.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Set the provider of the datasource block in schema.prisma to match your database: postgresql, mysql, sqlite, sqlserver, mongodb or cockroachdb.
3. Run prisma db pull to turn your database schema into a Prisma schema.
4. Run prisma generate to generate the Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

.envファイルが以下のように生成されます。
prisma initで生成される.envファイルのデフォルトはPostgreSQL向けのようです。

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

今回はMySQLを使用するため、MySQL用にDATABASE_URLを書き換えます。

# .env file
# mysql format
# <Protocol>://<User>:<Password>@<Host>:<Port>/<Database>?<Key>=<Value>
DATABASE_URL="mysql://MS-DDD:password@localhost:3306/fcc-nestjs-tutorial"

参考 Prisma Docs: MySQL

schema.prisma

prisma initで生成されたschema.prismaにUserモデルを追加しています。

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model User {
  id            Int      @id @default(autoincrement()) @map("user_id")
  firstName     String
  lastName      String
  email         String   @unique
  password      String
  refresh_token String?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  bookmarks Bookmark[]

  @@map("users")
}

prisma migrate dev実行

npx prisma migrate dev

以下のように冒頭のエラーが発生します。

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "fcc-nestjs-tutorial" at "localhost:3306"

Error: P3014

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow

Original error: Error code: P1010

User `MS-DDD` was denied access on the database `fcc-nestjs-tutorial`

Prisma公式Docsには以下の記述があり、User MS-DDDがcreate, alter, drop, referencesの権限を持っていないといけないようです。

In order to create and delete the shadow database when using development
commands such as migrate dev and migrate reset,
Prisma Migrate currently requires that the database user defined in your
datasource has permission to create databases.

MySQL: Database user must have CREATE, ALTER, DROP, REFERENCES
ON *.* privileges

引用 Prisma Docs: About the shadow database

Docker上のMySQL Userへの権限付与

`MS-DDD`としてMySQL serverへ入る

docker container exec -it mysql mysql -u MS-DDD -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

参考 MySQL: 4.2.4 Connecting to the MySQL Server Using Command Options

権限の確認

mysql> show grants;
+-----------------------------------------------------------------+
| Grants for MS-DDD@%                                             |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `MS-DDD`@`%`                              |
| GRANT ALL PRIVILEGES ON `fcc-nestjs-tutorial`.* TO `MS-DDD`@`%` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

CREATE権限を試しに付与

mysql> grant create on `fcc-nestjs-tutorial`.* to `MS-DDD`@`%`;
ERROR 1044 (42000): Access denied for user 'MS-DDD'@'%' to database 'fcc-nestjs-tutorial'

エラーが起きる。
冷静に考えてみると、管理者でもないユーザーが自分に権限を付与できるというのはおかしいので、rootユーザーとして入り直してみることに。

rootユーザーとして権限を付与してみる

docker container exec -it mysql mysql -u root -p

# 権限の確認
mysql> show grants;
| Grants for root@%                                                           
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 権限の付与
mysql> grant create on `fcc-nestjs-tutorial`.* to `MS-DDD`@`%`;
Query OK, 0 rows affected (0.01 sec)

MySQLの公式Docsの以下の通りに'Query OK, 0 rows affected'と表示され、権限の付与に成功したように見えます。

From the mysql program, GRANT responds with Query OK, 0 rows affected
when executed successfully. To determine what privileges result from the
operation, use SHOW GRANTS. See Section 13.7.7.21, “SHOW GRANTS Statement”.

引用　MYSQL: 13.7.1.6 GRANT Statement

早速権限の確認をします。

mysql> show grants for `MS-DDD`@`%`;
+-----------------------------------------------------------------+
| Grants for MS-DDD@%                                             |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `MS-DDD`@`%`                              |
| GRANT ALL PRIVILEGES ON `fcc-nestjs-tutorial`.* TO `MS-DDD`@`%` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

ん？全く何もかわっておらんのだが？
rootユーザーとしてログインし、MS-DDDに対して権限付与をしたのに何がおかしいのか。

なぜ権限を付与できないのか

ユーザー一覧を表示してみました。

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| MS-DDD           | %         |
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

rootが二つあります。今ログインしているユーザーを確認します。

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

どうやらMS-DDDとは異なるホストのrootユーザーにログインしているために、権限を付与できていないようです。

Docker上の`%`ホストに`root`ユーザーとしてログイン

%ホストに対してログインを試みます。

docker container exec -it mysql mysql -h % -u root -p
Enter password: 
ERROR 2005 (HY000): Unknown MySQL server host '%' (-2)

MySQLに「％なんてホストは知らん」と言われてしまいました。
そもそも%ホストとはなんなのか。
以下の文面から、どうやら設定を省略するとデフォルトで設定されるものらしいです。

The host name part of the account or role name, if omitted, defaults to '%'.

引用 13.7.1.6 GRANT Statement

何か%ホストを特定するものはないか探していたところ、Docker composeのhostnameはどうかと思い立ちました。早速試してみます。

docker container exec -it mysql mysql -u root -h mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

// now we login as root@%
mysql> select user();
+-----------------+
| user()          |
+-----------------+
| root@172.21.0.2 |
+-----------------+
1 row in set (0.00 sec)

どうやら%ホストにrootユーザーとしてログインできたようです。

`MS-DDD`に各権限を付与

CREATEを付与してみます。

mysql> grant create on *.* to `MS-DDD`@`%`;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for `MS-DDD`@`%`;
+-----------------------------------------------------------------+
| Grants for MS-DDD@%                                             |
+-----------------------------------------------------------------+
| GRANT CREATE ON *.* TO `MS-DDD`@`%`                             |
| GRANT ALL PRIVILEGES ON `fcc-nestjs-tutorial`.* TO `MS-DDD`@`%` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

GRANT CREATEへと表示が変わり、変更を確認できました。
残りのALTER, DROP, REFERENCESを付与していきます。

mysql> show grants for `MS-DDD`@`%`;
+-----------------------------------------------------------------+
| Grants for MS-DDD@%                                             |
+-----------------------------------------------------------------+
| GRANT CREATE, DROP, REFERENCES, ALTER ON *.* TO `MS-DDD`@`%`    |
| GRANT ALL PRIVILEGES ON `fcc-nestjs-tutorial`.* TO `MS-DDD`@`%` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

CREATE, ALTER, DROP, REFERENCESの4つを付与することができました。

`npx prisma migrate dev`の成功

改めてnpx prisma migrate devを実行します。

npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "fcc-nestjs-tutorial" at "localhost:3306"

✔ Enter a name for the new migration: … first-migration
Applying migration `20230120103212_first_migration`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20230120103212_first_migration/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (4.9.0 | library) to ./node_modules/@prisma/client in 109ms

prisma migrate devが成功しました。