Drizzle Team•3mo 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.

9 Replies

TOSL•3mo ago

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

SpamixTheProducerOP•3mo 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•3mo 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•3mo ago

okay this seems like a mariadb-specific issue

TOSL•3mo ago

Did you run drizzle generate at any point?

SpamixTheProducerOP•3mo 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•3mo 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•2mo 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

Mario564•2mo ago

MariaDB support is very limited right now, but there are plans on officially supporting it in the future for both Kit and ORM.

Gaming

Programming

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

Did you find this page helpful?