Zenn
📌

【Prisma】could not create the shadow databaseの対応

2023/11/14に公開
Docker
MySQL
TypeScript
Prisma
tech

prismaのmigrate時に発生する下記のエラーの対応手順を解説します。

% npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "express_db" at "mysql: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 `admin` was denied access on the database `express_db

Shadow Databse

prismaのshadow databaseとは、migration操作内で利用する一時的なdbです。
prisma migrateでdb schemaを更新する際に使用されます。

エラー原因

Please make sure the database user has permission to create databasesから、dbの作業ユーザーにcreate権限がない事が原因と考えられます。

対応

mysqlのGRANT文を使用して作業ユーザーにcreate権限を与えます。

  1. rootユーザーで入り、作業ユーザーの権限を確認する
    ※ ユーザー名には仮でadminを記述
mysql> SHOW GRANTS FOR 'admin'@'%';
+--------------------------------------------------------+
| Grants for admin@%                                     |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%`                      |
| GRANT ALL PRIVILEGES ON `express\_db`.* TO `admin`@`%` |
+--------------------------------------------------------+
2 rows in set (0.01 sec)
  1. create権限の付与
mysql> GRANT CREATE, CREATE TEMPORARY TABLES ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.10 sec)
  1. 権限の確認
mysql> SHOW GRANTS FOR 'admin'@'%';
+--------------------------------------------------------------------------+
| Grants for admin@%                                                       |
+--------------------------------------------------------------------------+
| GRANT CREATE, CREATE TEMPORARY TABLES ON *.* TO `admin`@`%` |
| GRANT ALL PRIVILEGES ON `express\_db`.* TO `admin`@`%`                   |
+--------------------------------------------------------------------------+
2 rows in set (0.01 sec)

prisma migrate再実行

% npx prisma migrate dev

Prisma schema loaded from prisma/schema.prisma

✔ Enter a name for the new migration: …
Applying migration `20231114133424_`

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

migrations/
  └─ 20231114133424_/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (v5.5.2) to ./node_modules/@prisma/client in 601ms

成功しました☺️

Discussion