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:...

Jump to solution

3 Replies

Alky•5mo ago

I don't think there's a problem in your code. The error points toward a column named "details", so maybe is somewhere else. Another thing to keep in mind is that to index a Text type, your database needs to use the InnoDB or MyISAM engines.

NoelHuibers•5mo ago

Sorry forgot to change the errormessage to the corresponding table. Problem is with Drizzles kit. Is working when using SQL raw to create the table.

Solution

NoelHuibers•5mo ago

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

Gaming

Programming

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