使用Prisma向TiDB推送数据时报错但数据已写入的问题

问题详情 #

使用兼容MySQL的TiDB数据库，首次执行npx prisma db push时无报错，但再次执行时触发报错FULLTEXT and SPATIAL index is not supported，不过数据仍能成功推送至数据库。

相关配置文件 #

.env配置 #

DATABASE_URL='mysql://dQGUvqJrGU77inw.root:1v3jgSCfBHcY6lH7@gateway01.eu-central-1.prod.aws.tidbcloud.com:4000/test?sslaccept=strict'

schema.prisma配置 #

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "fullTextIndex"]
}

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

model Category {
  id         String @id @default(uuid())
  name       String
  companions Companion[]
}

model Companion {
  id           String @id @default(uuid())
  userId       String
  userName     String
  src          String
  seed         String @db.Text
  name         String @db.Text
  instructions String @db.Text
  description  String
  categoryId   String
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  category     Category @relation(fields: [categoryId], references: [id])
  messages     Message[]

  @@index([categoryId])
  @@fulltext([name])
}

enum Role {
  user
  system
}

model Message {
  id          String @id @default(uuid())
  userId      String
  companionId String
  content     String @db.Text
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  role        Role

  companion Companion @relation(fields: [companionId], references: [id], onDelete: Cascade)

  @@index([companionId])
}

控制台输出 #

PS C:\Users\anato\OneDrive\Desktop\ai-companions> npx prisma generate
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma

✔ Generated Prisma Client (v5.17.0) to .\node_modules\@prisma\client in 68ms

Start by importing your Prisma Client (See: http://pris.ly/d/importing-client)

Tip: Want real-time updates to your database without manual polling? Discover how with Pulse: https://pris.ly/tip-0-pulse

PS C:\Users\anato\OneDrive\Desktop\ai-companions> npx prisma db push 
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": MySQL database "test" at "gateway01.eu-central-1.prod.aws.tidbcloud.com:4000"
Error: FULLTEXT and SPATIAL index is not supported
   0: sql_schema_connector::apply_migration::migration_step
           with step=CreateIndex { table_id: (Some(TableId(1)), TableId(1)), index_id: IndexId(3), from_drop_and_recreate: false }
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:21
   1: sql_schema_connector::apply_migration::apply_migration
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:10
   2: schema_core::state::SchemaPush
             at schema-engine\core\src\state.rs:461

解决方案 #

原因分析 #

TiDB本身支持FULLTEXT索引，但Prisma的MySQL连接器在检测数据库类型时，误将TiDB判定为不支持FULLTEXT索引的MySQL变体，导致重复执行db push时尝试创建索引失败。

临时规避方案 #

1. 手动创建全文索引：移除schema中的@@fulltext([name])定义，直接在TiDB中执行SQL创建索引：

   CREATE FULLTEXT INDEX idx_companion_name ON Companion(name);
   

2. 跳过错误执行：若需保留schema中的@@fulltext定义，执行db push时添加--accept-data-loss参数（需确认无数据风险）：

   npx prisma db push --accept-data-loss
   

长期解决方案 #

• 等待Prisma官方完善TiDB兼容性，或在Prisma datasource中明确指定TiDB作为provider（目前Prisma尚未正式支持TiDB，暂需使用mysql provider）。
• 改用Prisma Migrate替代db push，手动编写迁移文件，自定义索引创建逻辑，避免自动检测的兼容性问题。

内容的提问来源于stack exchange，提问作者A-Makeyev