Prisma迁移Schema至远程MySQL数据库失败，求解决方案

本地环境执行npx prisma migrate dev或npx prisma db push可正常完成数据库迁移，但部署到远程MySQL服务器时出现以下两个错误：

1. 执行npx prisma migrate dev时触发P3014错误，提示无法创建影子数据库，用户fitish无dev-fitish-v2数据库访问权限；
2. 执行npx prisma db push时，提示用户fitish被拒绝使用REFERENCES命令操作dev-fitish-v2.User表。

附Prisma Schema代码 #

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

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

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

enum Role {
  ADMIN
  COACH
  CLEINT
}

model User {
  id                  Int                  @id @default(autoincrement())
  email               String
  profile             Profile?
  role                Role                 @default(CLEINT)
  createdAt           DateTime             @default(now())
  updatedAt           DateTime             @updatedAt
  underCoach          Int?
  coach               User?                @relation("CoachToUser", fields: [underCoach], references: [id])
  clients             User[]               @relation("CoachToUser")
  connectRequestsFrom ConnectRequest[]     @relation("ConnectRequestFrom")
  connectRequestsTo   ConnectRequest[]     @relation("ConnectRequestTo")
  meal                Meal[]
  MealAssignment      MealAssignment[]
  // setByUser           MealTime[]       @relation("setBy")
  // setToUser           MealTime[]       @relation("setTo")
  WorkoutsAssignment  WorkoutsAssignment[]
  Workouts            Workouts[]
}

model Profile {
  id          Int      @id @default(autoincrement())
  name        String
  dob         String
  gender      Gender   @default(MALE)
  height      Int?
  weight      Int?
  sugar       Int?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  userId      Int      @unique
  user        User     @relation(fields: [userId], references: [id])
  coachTypeId Int?
  coach       Coach?   @relation(fields: [coachTypeId], references: [id])
}

enum Gender {
  MALE
  FEMALE
  TRANSGENDER
}

model Coach {
  id        Int       @id @default(autoincrement())
  coachName String    @unique
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  profile   Profile[]
}

model ConnectRequest {
  id        Int           @id @default(autoincrement())
  fromId    Int
  toId      Int
  status    RequestStatus @default(PENDING)
  from      User          @relation("ConnectRequestFrom", fields: [fromId], references: [id])
  to        User          @relation("ConnectRequestTo", fields: [toId], references: [id])
  createdAt DateTime      @default(now())
  updatedAt DateTime      @updatedAt
}

enum RequestStatus {
  PENDING
  ACCEPTED
  REJECTED
}

model Meal {
  id             Int              @id @unique @default(autoincrement())
  name           String
  protiens       Int
  fats           Int
  carbs          Int
  mealType       MealType
  // mealPlan  MealPlan[] @relation("meals")
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  userId         Int
  user           User?            @relation(fields: [userId], references: [id])
  MealAssignment MealAssignment[]
}

model MealAssignment {
  id        Int      @id
  userId    Int // Adding userId directly instead of using @relation
  mealId    Int // Adding mealId directly instead of using @relation
  user      User     @relation(fields: [userId], references: [id])
  meal      Meal     @relation(fields: [mealId], references: [id])
  day       Int // Day number for which the meal is assigned
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

enum MealType {
  BREAKFAST
  LUNCH
  DINNER
  SNACK
}

model MealTime {
  id        Int      @id @unique @default(autoincrement())
  setBy     Int
  setTo     Int
  whichMeal MealType
  time      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model WorkoutsAssignment {
  id              Int      @id @unique @default(autoincrement())
  assigneddUserId Int
  workoutId       Int
  user            User     @relation(fields: [assigneddUserId], references: [id])
  workout         Workouts @relation(fields: [workoutId], references: [id])
  day             Int
  completed       Boolean  @default(false)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
}

model Workouts {
  id                 Int                  @id @unique @default(autoincrement())
  name               String
  vcLink             String?
  createdUserId      Int
  user               User                 @relation(fields: [createdUserId], references: [id])
  createdAt          DateTime             @default(now())
  updatedAt          DateTime             @updatedAt
  WorkoutsAssignment WorkoutsAssignment[]
}

解决方案 #

1. 解决P3014影子数据库权限问题 #

prisma migrate dev需要创建影子数据库做安全校验，报错核心是用户权限不足，两种解决方式：

• 方式一：授予用户创建数据库的权限
  登录远程MySQL服务器，执行以下SQL命令（若需限制访问，将%替换为允许的IP地址）：

  GRANT CREATE, ALTER, DROP, REFERENCES, INSERT, UPDATE, DELETE, SELECT ON *.* TO 'fitish'@'%';
  FLUSH PRIVILEGES;
  

• 方式二：手动指定影子数据库
  取消Schema中shadowDatabaseUrl的注释，在环境变量中设置SHADOW_DATABASE_URL为已存在的数据库链接，确保fitish用户拥有该数据库的全读写权限。

2. 解决REFERENCES权限被拒绝问题 #

该错误是因为用户缺少创建外键所需的REFERENCES权限，直接执行以下SQL授予权限：

GRANT REFERENCES ON `dev-fitish-v2`.* TO 'fitish'@'%';
FLUSH PRIVILEGES;

若需要更全面的权限保障，直接使用方式一中的权限授予命令即可覆盖。

注意事项 #

• 远程MySQL通常有严格权限控制，若不想给用户全局权限，可精准授予dev-fitish-v2数据库的CREATE、ALTER、DROP、REFERENCES、INSERT、UPDATE、DELETE、SELECT权限；
• 权限变更后，需重新连接数据库再执行迁移命令。

内容的提问来源于stack exchange，提问作者Mohamed Rifay