5 replies

Drizzle Planetscale "text" in schema (MySQL) "Key specification without key length"

I want a text type in my schema since my long string isn't fitting into a varchar.
When i do this:

export const mytable = mysqlTable(
  "mytable",
  {
    bigtext: text("bigtext"),
  },
  (mytable) => ({
    bigtext: index("bigtext").on(mytable.bigtext),
  }),
);

export const mytable = mysqlTable(
  "mytable",
  {
    bigtext: text("bigtext"),
  },
  (mytable) => ({
    bigtext: index("bigtext").on(mytable.bigtext),
  }),
);

I get the error:
vttablet: rpc error: code = InvalidArgument desc = BLOB/TEXT column 'bigtext' used in key specification without a key length (errno 1170)

Solution

For refrences what i found in the drizzle discord: Worked for me (Option 1)

Same question was asked by Theo there. He used Option2.

Option1:
You can create an Index for text/blob, but you should specify a length for it. This length indicates which portion of the text field will be indexed. Limits for that are 767 or 3072 bytes(depending on the row format). Drizzle doesn't have this possibility, but it's a great case to have it(I just never indexed text fields, I was using second option I'm going to explain)
Workaround to make it work with drizzle db push -> create this index manually. Be sure to name it same way you name it in drizzle. In this case drizzle-kit won't see a difference there and won't be triggered to change an index

Option 2
Use varchar with specific length and create an index for it. This one should work well with Drizzle. Varchar will be fully indexed, when text will be only partially indexed on a size you choose. So all depends on your usecase

Jump to solution

TTC

Theo's Typesafe Cult•3y ago•

5 replies

NoelHuibers

Drizzle Planetscale "text" in schema (MySQL) "Key specification without key length"

I want a text type in my schema since my long string isn't fitting into a varchar.
When i do this:

export const mytable = mysqlTable(
  "mytable",
  {
    bigtext: text("bigtext"),
  },
  (mytable) => ({
    bigtext: index("bigtext").on(mytable.bigtext),
  }),
);

export const mytable = mysqlTable(
  "mytable",
  {
    bigtext: text("bigtext"),
  },
  (mytable) => ({
    bigtext: index("bigtext").on(mytable.bigtext),
  }),
);

I get the error:
vttablet: rpc error: code = InvalidArgument desc = BLOB/TEXT column 'bigtext' used in key specification without a key length (errno 1170)

Solution

Jump to solution

Drizzle Planetscale "text" in schema (MySQL) "Key specification without key length"

Similar Threads

Drizzle Planetscale "text" in schema (MySQL) "Key specification without key length"

Similar Threads

Similar Threads

Similar Threads