drizzle-kit push:mysql drops unchanged primary key & introspect:mysql fails hard

drizzle-kit version: 0.19.3 database: Planetscale drizzle.config.ts:

export default {
    schema: './src/lib/db/schema.ts',
    out: './src/lib/db/migrations',
    driver: 'mysql2',
    dbCredentials: {
        connectionString: process.env.DATABASE_URL,
    },
} satisfies Config;

export default {
    schema: './src/lib/db/schema.ts',
    out: './src/lib/db/migrations',
    driver: 'mysql2',
    dbCredentials: {
        connectionString: process.env.DATABASE_URL,
    },
} satisfies Config;

Running drizzle-kit push:mysql errored out because it was trying to drop a primary key

  sql: 'ALTER TABLE `TrainingRegistrations` DROP PRIMARY KEY;',
  sqlState: '42000',
  sqlMessage: 'target: master-data.-.primary: vttablet: rpc error: code = InvalidArgument desc = Incorrect table definition; there can be only one auto column and it must be defined as a key (errno 1075) (sqlstate 42000) (CallerID: wg09dvsn07hoyy0q9wzp): Sql: "alter table TrainingRegistrations drop primary key", BindVars: {REDACTED}'}

  sql: 'ALTER TABLE `TrainingRegistrations` DROP PRIMARY KEY;',
  sqlState: '42000',
  sqlMessage: 'target: master-data.-.primary: vttablet: rpc error: code = InvalidArgument desc = Incorrect table definition; there can be only one auto column and it must be defined as a key (errno 1075) (sqlstate 42000) (CallerID: wg09dvsn07hoyy0q9wzp): Sql: "alter table TrainingRegistrations drop primary key", BindVars: {REDACTED}'}

As per https://orm.drizzle.team/kit-docs/overview#prototyping-with-db-push i understood that drizzle-kit executes a schema introspection, and then diffs it against my current schema. So i ran a schema introspection, and the result very wrong Attached screenshots of the current table structure, obtained by running SHOW CREATE TABLE [name] and the schema that drizzle generated

Drizzle Kit - DrizzleORM

Drizzle ORM | %s

8 Replies

kstulgys•13mo ago

I have same issue. SO fking anoying, been trying to debug the whole day

Scai•13mo ago

I have the same issue Are you using next-auth too? Seems a issue with next auth schema It's a little annoying to delete all tables, push:mysql, import some data to test the new schema

kstulgys•13mo ago

Actually solved by commenting out strict

export default {
  out: "./src/database/drizzle/migrations",
  // strict: true,
  schema: "./src/database/drizzle/schema.ts",
  // verbose: true,
  driver: "mysql2",
  dbCredentials: {
    connectionString: ...
  },
} satisfies Config;

export default {
  out: "./src/database/drizzle/migrations",
  // strict: true,
  schema: "./src/database/drizzle/schema.ts",
  // verbose: true,
  driver: "mysql2",
  dbCredentials: {
    connectionString: ...
  },
} satisfies Config;

Scai•13mo ago

umm, I don't have it in my config

AKI•13mo ago

Im experiencing this too, someone help please Im using next auth

ckalgos•13mo ago

Did you guys found any work around?. Its getting annoying to delete tables each time to push the new schema changes.

kstulgys•13mo ago

For me commenting out strict and verbose solved this. Make sure you have latest drizzle orm version

natedunn•13mo ago

Yeah I didn't have either of those (and verbose just displays what its going to do afaik). I had to downgrade to make this work.

Gaming

Programming

drizzle-kit push:mysql drops unchanged primary key & introspect:mysql fails hard