[prisma] prisma 에러 - Original error: Error code: P1010

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 `test` was denied access on the database `study`

npx prisma migrate dev --name init

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "basic" at "localhost:3307"

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 `test` was denied access on the database `basic`

schema.prisma를 db에 migration하기 위해 npx prisma migrate 명령어를 입력해야하는데, 이런 에러 메세지가 출력됐습니다.

처음에는 user의 권한 문제인줄 알았는데, prisma에서 shadowdatabaseurl을 설정해주지 않아서 발생한 문제였습니다.


해결 방법은 https://github.com/prisma/prisma/issues/4571 이 링크에서 찾았습니다.
mysql을 docker로 실행시켰는데, prisma는 docker로 실행된 db를 cloud host로 인식하여 이런 현상이 발생하는것으로 추정됩니다.

on premise로 prisma를 실행하게 되면, shadow database가 자동으로 생성되는데, cloud로 실행한 db를 연결할 때에는 shadow database를 직접 생성해주어야 한다고 공식문서에 나와있습니다.
https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database#cloud-hosted-shadow-databases-must-be-created-manually

따라서 아래와 같이 shadow용 db를 추가 실행하고, schema.prisma에 shadowDatabaseUrl를 표기한 후 migrate 명령어를 실행하면 정상적으로 작동합니다.

// schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
  provider = "prisma-client-js"
  // output = "../node_modules/.prisma/client"

}

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

model Order {
  id String @id @default(uuid())
  totalQty Int @db.Int()
  deletedAt DateTime? @db.DateTime()

  orderItems OrderItem[]
}

model OrderItem {
  id String @id @default(uuid())
  name String @db.VarChar(255)
  company String @db.VarChar(255)
  qty Int @db.Int()
  deletedAt DateTime? @db.DateTime()

  order Order @relation(fields: [orderId], references: [id])
  orderId String
}

// .env
PORT=3000


DATABASE_URL="mysql://test:test@localhost:3307/study?allowPublicKeyRetrieval=true"
SHADOW_DATABASE_URL="mysql://test:test@localhost:3308/study?allowPublicKeyRetrieval=true"
MONGODB_URL="mongodb://root:root@localhost:27018/admin"

docker-compose.yml

services:
  db1:
    image: mysql:8.0
    ports:
      - '3307:3306'
    restart: always
    container_name: basic-db
    stdin_open: true
    tty: true
    environment:
      TZ: Asia/Seoul
      MYSQL_USER: test
      MYSQL_PASSWORD: test
      MYSQL_DATABASE: study
      MYSQL_ROOT_PASSWORD: root
      MYSQL_INITDB_CHARSET: utf8mb4
      MYSQL_INITDB_COLLATION: utf8mb4_unicode_ci
    # volumes:
    #   - ./mysql:/var/lib/mysql:rw
    # tmpfs:
    #   - /var/lib/mysql:rw
  db2:
    image: mysql:8.0
    ports:
      - '3308:3306'
    restart: always
    container_name: basic-shadow
    stdin_open: true
    tty: true
    environment:
      TZ: Asia/Seoul
      MYSQL_USER: test
      MYSQL_PASSWORD: test
      MYSQL_DATABASE: study
      MYSQL_ROOT_PASSWORD: root
      MYSQL_INITDB_CHARSET: utf8mb4
      MYSQL_INITDB_COLLATION: utf8mb4_unicode_ci
    # volumes:
    #   - ./mysql2:/var/lib/mysql:rw
    # tmpfs:
    #   - /var/lib/mysql:rw
volumes:
  basic: