Drizzle Team•5d ago

drizzle-kit push tries to re-define all primary keys when updating schema (mysql/mariadb)

When I run drizzle kit push it tries to redefine all primary keys which of course ends up in a failure. I am using MariaDB with the mysql driver, so I've had to make a few adjustments to make it work (like not using serial).

❯ bun drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/drizzle.config.ts'
[✓] Pulling schema from database...
Reading schema files:
/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/src/drizzle/db/schema.ts

Error: Multiple primary key defined
    at PromiseConnection.execute (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/mysql2/lib/promise/connection.js:47:22)
    at Object.query (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:79025:40)
    at mysqlPush (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:82057:22)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.handler (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:92160:9)
    at async run (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:90501:7) {
  code: 'ER_MULTIPLE_PRI_KEY',
  errno: 1068,
  sql: 'ALTER TABLE `challengeFiles` ADD PRIMARY KEY(`id`);',
  sqlState: '42000',
  sqlMessage: 'Multiple primary key defined'
}

❯ bun drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/drizzle.config.ts'
[✓] Pulling schema from database...
Reading schema files:
/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/src/drizzle/db/schema.ts

Error: Multiple primary key defined
    at PromiseConnection.execute (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/mysql2/lib/promise/connection.js:47:22)
    at Object.query (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:79025:40)
    at mysqlPush (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:82057:22)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.handler (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:92160:9)
    at async run (/home/alexander/Documents/git/spamixofficial/decompil.in-web/backend/node_modules/drizzle-kit/bin.cjs:90501:7) {
  code: 'ER_MULTIPLE_PRI_KEY',
  errno: 1068,
  sql: 'ALTER TABLE `challengeFiles` ADD PRIMARY KEY(`id`);',
  sqlState: '42000',
  sqlMessage: 'Multiple primary key defined'
}

Schema: https://pastebin.com/WxPcMCX0

Pastebin

schema.ts - Pastebin.com

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

8 Replies

TOSL•5d ago

You had an already existing db with populated tables then started using Drizzle?

SpamixTheProducerOP•4d ago

Other way around No populated tables @TOSL seems I identified a separate issue, will rename this thread and update it to actually describe the issue

SpamixTheProducerOP•4d ago

I did not have an existing db, or I had an empty database to let drizzle populate it. First run always works, but if I try to update any tables with new fields it fails with the error above. Here's a github issue describing the same problem: https://github.com/drizzle-team/drizzle-orm/issues/3473

GitHub

BUG drizzle-kit push:mysql with Two Primary Keys · Issue #3473 · ...

When you have a schema that contains two Primary Keys drizzle-kit is able to push the initial database creation but fails on future runs. When checking the verbose logs you can see drizzle-kit is a...

SpamixTheProducerOP•4d ago

okay this seems like a mariadb-specific issue

TOSL•4d ago

Did you run drizzle generate at any point?

SpamixTheProducerOP•4d ago

Yes I did. Found a workaround. I can’t run push, instead I must first generate then migrate, push seems to be the command that simply fucks up

TOSL•4d ago

I could be wrong but suspect the bug is some type of conflict from having both. If you'd like to use push, I suggest you delete your migration files and stick to just push.

SpamixTheProducerOP•2d ago

Yeah Tbh I couldn’t get querys to work with MariaDB so I switched over to MySQL (even though I prefer MariaDB). Everything works smoothly now, but when the MariaDB driver comes out I’ll switch back

Gaming

Programming

drizzle-kit push tries to re-define all primary keys when updating schema (mysql/mariadb)

Did you find this page helpful?