You cannot define an index on fields with Native type Text of MySQL.

[jamiev-dfm]

Bug description

My database has indices on a few text fields, and introspecting it put those indices in the schema, but prisma validate says "You cannot define an index on fields with Native type Text of MySQL.".

This error was also mentioned in #5288 and apparently fixed, but I'm still seeing it.

How to reproduce

I've included a minimal schema below. Just put that in a file and validate it.

Expected behavior

The schema seems correct to me, so I think it should validate without errors, otherwise the introspection shouldn't output the index.

Prisma information

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

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

model av {
    avid    Int     @id @default(autoincrement()) @db.UnsignedInt
    av_desc String? @db.Text

    @@index([av_desc], name: "av_desc")
}

Environment & setup

• OS: Mac OS
• Database: MySQL
• Node.js version: v14.15.4

Prisma Version

2.28.0

[thebiglabasky]

This infers an index on a TEXT column with a length limit as a normal index which is why it's not currently working.

We would need to design a PSL solution for defining specific kinds of indices, which is a feature we haven't picked up just yet.

The current workaround is definitely not nice:
If you only use introspection, you could remove the @@index notation from the schema, but that would mean that running a migration with Prisma migrate would drop it by default. This would thus mean you'd need to edit the generated SQL of the migration manually to create the specific kind of index.

We'll update this issue as soon as we start looking into a sustainable solution.

[janpio]

Feature issue tracking this: #7263

[pimeys]

This is something that should work with the upcoming 3.5.0 version using the extendedIndexes preview feature. Your datamodel would then need to define the amount of bytes you'd take from the text column for the index, because MySQL doesn't allow you to create indexes for arbitrary-sized heap-allocated columns:

generator client {
    provider = "prisma-client-js"
    previewFeatures = ["extendedIndexes"]
}

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

model av {
    avid    Int     @id @default(autoincrement()) @db.UnsignedInt
    av_desc String? @db.Text

    @@index([av_desc(length: 100)], name: "av_desc")
}

[janpio]

My database has indices on a few text fields, and introspecting it put those indices in the schema, but prisma validate says "You cannot define an index on fields with Native type Text of MySQL.".

Can you try to introspect your database again with 3.5.0 @jamiev-dfm? That should create a slightly different schema file that does include the length attribute that @pimeys mentioned and the schema file should validate cleanly.

[janpio]

Ping @jamiev-dfm

[masashi-sutou]

I got a similar error in another way so I'll keep a record here.

Running npx prisma db pull from sakila gives the same error (sakila is a MySQL sample database).

Prisma schema loaded from prisma/schema.prisma
Error: Schema parsing
error: You cannot define an index on fields with Native type Text of MySQL. If you are using the `extendedIndexes` preview feature you can add a `length` argument to allow this.
  -->  schema.prisma:152
   | 
151 | 
152 | model FilmText {
153 |   film_id     Int     @id @db.SmallInt
154 |   title       String  @db.VarChar(255)
155 |   description String? @db.Text
156 | 
157 |   @@index([title, description], map: "idx_title_description")
158 |   @@map("film_text")
159 | }
   | 

• However, I tried running npx prisma db pull with the following schema using prisma v3.10.0.

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

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

• I succeeded to generate scheme and had no error.

model FilmText {
  film_id     Int     @id @db.SmallInt
  title       String  @db.VarChar(255)
  description String? @db.Text

  @@fulltext([title, description], map: "idx_title_description")
  @@map("film_text")
}

[janpio]

Could you successfully use that schema afterwards @masashi-sutou?

[masashi-sutou]

@janpio

• Yes, I'm using the following schema.prisma successfully, except for Unsupported("geometry") in the address table location.
• I can see sakila's tables with prisma studio.
• I use mysql/mysql-server:latest with docker.

📄 schema.prisma（sakila）

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

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

model Actor {
  actor_id    Int         @id @default(autoincrement()) @db.UnsignedSmallInt
  first_name  String      @db.VarChar(45)
  last_name   String      @db.VarChar(45)
  last_update DateTime    @default(now()) @db.Timestamp(0)
  film_actor  FilmActor[]

  @@index([last_name], map: "idx_actor_last_name")
  @@map("actor")
}

model Address {
  address_id  Int                     @id @default(autoincrement()) @db.UnsignedSmallInt
  address     String                  @db.VarChar(50)
  address2    String?                 @db.VarChar(50)
  district    String                  @db.VarChar(20)
  city_id     Int                     @db.UnsignedSmallInt
  postal_code String?                 @db.VarChar(10)
  phone       String                  @db.VarChar(20)
  location    Unsupported("geometry")
  last_update DateTime                @default(now()) @db.Timestamp(0)
  city        City                    @relation(fields: [city_id], references: [city_id], map: "fk_address_city")
  customer    Customer[]
  staff       Staff[]
  store       Store[]

  @@index([city_id], map: "idx_fk_city_id")
  @@map("address")
}

model Category {
  category_id   Int            @id @default(autoincrement()) @db.UnsignedTinyInt
  name          String         @db.VarChar(25)
  last_update   DateTime       @default(now()) @db.Timestamp(0)
  film_category FilmCategory[]

  @@map("category")
}

model City {
  city_id     Int       @id @default(autoincrement()) @db.UnsignedSmallInt
  city        String    @db.VarChar(50)
  country_id  Int       @db.UnsignedSmallInt
  last_update DateTime  @default(now()) @db.Timestamp(0)
  country     Country   @relation(fields: [country_id], references: [country_id], map: "fk_city_country")
  address     Address[]

  @@index([country_id], map: "idx_fk_country_id")
  @@map("city")
}

model Country {
  country_id  Int      @id @default(autoincrement()) @db.UnsignedSmallInt
  country     String   @db.VarChar(50)
  last_update DateTime @default(now()) @db.Timestamp(0)
  city        City[]

  @@map("country")
}

model Customer {
  customer_id Int       @id @default(autoincrement()) @db.UnsignedSmallInt
  store_id    Int       @db.UnsignedTinyInt
  first_name  String    @db.VarChar(45)
  last_name   String    @db.VarChar(45)
  email       String?   @db.VarChar(50)
  address_id  Int       @db.UnsignedSmallInt
  active      Boolean   @default(true)
  create_date DateTime  @db.DateTime(0)
  last_update DateTime? @default(now()) @db.Timestamp(0)
  address     Address   @relation(fields: [address_id], references: [address_id], map: "fk_customer_address")
  store       Store     @relation(fields: [store_id], references: [store_id], map: "fk_customer_store")
  payment     Payment[]
  rental      Rental[]

  @@index([address_id], map: "idx_fk_address_id")
  @@index([store_id], map: "idx_fk_store_id")
  @@index([last_name], map: "idx_last_name")
  @@map("customer")
}

model Film {
  film_id                                      Int            @id @default(autoincrement()) @db.UnsignedSmallInt
  title                                        String         @db.VarChar(128)
  description                                  String?        @db.Text
  release_year                                 Int?           @db.Year
  language_id                                  Int            @db.UnsignedTinyInt
  original_language_id                         Int?           @db.UnsignedTinyInt
  rental_duration                              Int            @default(3) @db.UnsignedTinyInt
  rental_rate                                  Decimal        @default(4.99) @db.Decimal(4, 2)
  length                                       Int?           @db.UnsignedSmallInt
  replacement_cost                             Decimal        @default(19.99) @db.Decimal(5, 2)
  rating                                       FilmRating?    @default(G)
  special_features                             String?
  last_update                                  DateTime       @default(now()) @db.Timestamp(0)
  language_film_language_idTolanguage          Language       @relation("film_language_idTolanguage", fields: [language_id], references: [language_id], map: "fk_film_language")
  language_film_original_language_idTolanguage Language?      @relation("film_original_language_idTolanguage", fields: [original_language_id], references: [language_id], onDelete: Restrict, map: "fk_film_language_original")
  film_actor                                   FilmActor[]
  film_category                                FilmCategory[]
  inventory                                    Inventory[]

  @@index([language_id], map: "idx_fk_language_id")
  @@index([original_language_id], map: "idx_fk_original_language_id")
  @@index([title], map: "idx_title")
  @@map("film")
}

enum FilmRating {
  G
  PG
  PG_13  @map("PG-13")
  R
  NC_17  @map("NC-17")

  @@map("film_rating")
}

model FilmActor {
  actor_id    Int      @db.UnsignedSmallInt
  film_id     Int      @db.UnsignedSmallInt
  last_update DateTime @default(now()) @db.Timestamp(0)
  actor       Actor    @relation(fields: [actor_id], references: [actor_id], map: "fk_film_actor_actor")
  film        Film     @relation(fields: [film_id], references: [film_id], map: "fk_film_actor_film")

  @@id([actor_id, film_id])
  @@index([film_id], map: "idx_fk_film_id")
  @@map("film_actor")
}

model FilmCategory {
  film_id     Int      @db.UnsignedSmallInt
  category_id Int      @db.UnsignedTinyInt
  last_update DateTime @default(now()) @db.Timestamp(0)
  category    Category @relation(fields: [category_id], references: [category_id], map: "fk_film_category_category")
  film        Film     @relation(fields: [film_id], references: [film_id], map: "fk_film_category_film")

  @@id([film_id, category_id])
  @@index([category_id], map: "fk_film_category_category")
  @@map("film_category")
}

model FilmText {
  film_id     Int     @id @db.SmallInt
  title       String  @db.VarChar(255)
  description String? @db.Text

  @@fulltext([title, description], map: "idx_title_description")
  @@map("film_text")
}

model Inventory {
  inventory_id Int      @id @default(autoincrement()) @db.UnsignedMediumInt
  film_id      Int      @db.UnsignedSmallInt
  store_id     Int      @db.UnsignedTinyInt
  last_update  DateTime @default(now()) @db.Timestamp(0)
  film         Film     @relation(fields: [film_id], references: [film_id], map: "fk_inventory_film")
  store        Store    @relation(fields: [store_id], references: [store_id], map: "fk_inventory_store")
  rental       Rental[]

  @@index([film_id], map: "idx_fk_film_id")
  @@index([store_id, film_id], map: "idx_store_id_film_id")
  @@map("inventory")
}

model Language {
  language_id                              Int      @id @default(autoincrement()) @db.UnsignedTinyInt
  name                                     String   @db.Char(20)
  last_update                              DateTime @default(now()) @db.Timestamp(0)
  film_film_language_idTolanguage          Film[]   @relation("film_language_idTolanguage")
  film_film_original_language_idTolanguage Film[]   @relation("film_original_language_idTolanguage")

  @@map("language")
}

model Payment {
  payment_id   Int       @id @default(autoincrement()) @db.UnsignedSmallInt
  customer_id  Int       @db.UnsignedSmallInt
  staff_id     Int       @db.UnsignedTinyInt
  rental_id    Int?
  amount       Decimal   @db.Decimal(5, 2)
  payment_date DateTime  @db.DateTime(0)
  last_update  DateTime? @default(now()) @db.Timestamp(0)
  customer     Customer  @relation(fields: [customer_id], references: [customer_id], map: "fk_payment_customer")
  rental       Rental?   @relation(fields: [rental_id], references: [rental_id], map: "fk_payment_rental")
  staff        Staff     @relation(fields: [staff_id], references: [staff_id], map: "fk_payment_staff")

  @@index([rental_id], map: "fk_payment_rental")
  @@index([customer_id], map: "idx_fk_customer_id")
  @@index([staff_id], map: "idx_fk_staff_id")
  @@map("payment")
}

model Rental {
  rental_id    Int       @id @default(autoincrement())
  rental_date  DateTime  @db.DateTime(0)
  inventory_id Int       @db.UnsignedMediumInt
  customer_id  Int       @db.UnsignedSmallInt
  return_date  DateTime? @db.DateTime(0)
  staff_id     Int       @db.UnsignedTinyInt
  last_update  DateTime  @default(now()) @db.Timestamp(0)
  customer     Customer  @relation(fields: [customer_id], references: [customer_id], map: "fk_rental_customer")
  inventory    Inventory @relation(fields: [inventory_id], references: [inventory_id], map: "fk_rental_inventory")
  staff        Staff     @relation(fields: [staff_id], references: [staff_id], map: "fk_rental_staff")
  payment      Payment[]

  @@unique([rental_date, inventory_id, customer_id], map: "rental_date")
  @@index([customer_id], map: "idx_fk_customer_id")
  @@index([inventory_id], map: "idx_fk_inventory_id")
  @@index([staff_id], map: "idx_fk_staff_id")
  @@map("rental")
}

model Staff {
  staff_id                            Int       @id @default(autoincrement()) @db.UnsignedTinyInt
  first_name                          String    @db.VarChar(45)
  last_name                           String    @db.VarChar(45)
  address_id                          Int       @db.UnsignedSmallInt
  picture                             Bytes?    @db.Blob
  email                               String?   @db.VarChar(50)
  store_id                            Int       @db.UnsignedTinyInt
  active                              Boolean   @default(true)
  username                            String    @db.VarChar(16)
  password                            String?   @db.VarChar(40)
  last_update                         DateTime  @default(now()) @db.Timestamp(0)
  address                             Address   @relation(fields: [address_id], references: [address_id], map: "fk_staff_address")
  store_staff_store_idTostore         Store     @relation("staff_store_idTostore", fields: [store_id], references: [store_id], map: "fk_staff_store")
  payment                             Payment[]
  rental                              Rental[]
  store_staffTostore_manager_staff_id Store?    @relation("staffTostore_manager_staff_id")

  @@index([address_id], map: "idx_fk_address_id")
  @@index([store_id], map: "idx_fk_store_id")
  @@map("staff")
}

model Store {
  store_id                            Int         @id @default(autoincrement()) @db.UnsignedTinyInt
  manager_staff_id                    Int         @unique(map: "idx_unique_manager") @db.UnsignedTinyInt
  address_id                          Int         @db.UnsignedSmallInt
  last_update                         DateTime    @default(now()) @db.Timestamp(0)
  address                             Address     @relation(fields: [address_id], references: [address_id], map: "fk_store_address")
  staff_staffTostore_manager_staff_id Staff       @relation("staffTostore_manager_staff_id", fields: [manager_staff_id], references: [staff_id], map: "fk_store_staff")
  customer                            Customer[]
  inventory                           Inventory[]
  staff_staff_store_idTostore         Staff[]     @relation("staff_store_idTostore")

  @@index([address_id], map: "idx_fk_address_id")
  @@map("store")
}

prisma studio top page	FilmText with prisma studio

[adnanfuat]

I got the same error too. I converted the Text fields to VarChar type.
VarChar comes with length. In this way, the problem was solved.

[janpio]

With Prisma 4 next week we will stabilize the extendedIndexes preview. As mentioned before, that includes support for defining a length for @@index: https://www.prisma.io/docs/concepts/components/prisma-schema/indexes#configuring-the-length-of-indexes-with-length-mysql

This will also include a (hopefully) helpful error message when you are not using length in an index:

We consider this problem solved then 🥳 Please let us know if you disagree or this does not work as expected for you. Thanks!