[BUG]: Error: Multiple primary key defined

[CarelessInternet]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

I cannot update a table which has a primary key using drizzle-kit push:mysql. Initializing the table works fine, but applying any or no changes afterwards causes the error. The same error occurs when using composite primary keys.

schema.ts:

import { boolean, index, int, mysqlTable } from 'drizzle-orm/mysql-core';

export const users = mysqlTable(
    'users',
    {
        id: int('id').autoincrement().primaryKey(),
        randomNumber: int('randomNumber').notNull(),
        what: boolean('what'),
    },
    (table) => ({
        numberIdx: index('numberIdx').on(table.randomNumber),
    }),
);

drizzle.config.ts:

import type { Config } from 'drizzle-kit';

export default {
    schema: './src/schema.ts',
    out: './migrations',
    driver: 'mysql2',
    dbCredentials: database stuff here,
    verbose: true,
    strict: true,
} satisfies Config;

Logs:

 Warning  You are about to execute current statements:

ALTER TABLE `users` MODIFY COLUMN `id` int AUTO_INCREMENT NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `randomNumber` int NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `what` boolean;
ALTER TABLE `users` ADD PRIMARY KEY(`id`);

 Warning  Found data-loss statements:

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
Error: Multiple primary key defined
    at PromiseConnection.query (/home/careless/typescript/docker-monorepo/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:35481:26)
    at Command.<anonymous> (/home/careless/typescript/docker-monorepo/node_modules/.pnpm/drizzle-kit@0.19.13/node_modules/drizzle-kit/index.cjs:53292:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_MULTIPLE_PRI_KEY',
  errno: 1068,
  sql: 'ALTER TABLE `users` ADD PRIMARY KEY(`id`);',
  sqlState: '42000',
  sqlMessage: 'Multiple primary key defined'
}

Expected behavior

Drizzle-kit should apply the schema successfully.

Environment & setup

I'm using MariaDB version 10.6.12 and mysql2 version 3.6.2.

[johanwestling]

Having the same issue. Adding the output of a clean push and a 2nd push directly after. I'm quite new to Drizzle, but I had the impression that it would "know" that no changes had been made. Same thing happens when schema is changed as well, so practically you have to drop all tables for a push to work after initial push (if you're using primary keys at least).

Push 1

> drizzle-kit push:mysql

drizzle-kit: v0.20.6
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/var/www/frontend/drizzle.config.ts'
Reading schema files:
/var/www/frontend/db/schema.ts

 Warning  You are about to execute current statements:

CREATE TABLE `products` (
        `id` varchar(64) NOT NULL,
        `productId` text,
        `title` text,
        `description` longtext,
        `image` text,
        `type` text,
        `json` json,
        `provider` text,
        `portal` text,
        `price` decimal(10,2),
        `meta` json,
        `created_at` timestamp DEFAULT (now()),
        `updated_at` timestamp DEFAULT (now()),
        CONSTRAINT `products_id` PRIMARY KEY(`id`)
);

CREATE TABLE `sso` (
        `id` varchar(64) NOT NULL,
        `state` text,
        `origin` text,
        `created_at` timestamp DEFAULT (now()),
        CONSTRAINT `sso_id` PRIMARY KEY(`id`)
);

CREATE TABLE `webhooks` (
        `id` varchar(64) NOT NULL,
        `params` json,
        `json` json,
        `created_at` timestamp DEFAULT (now()),
        `updated_at` timestamp DEFAULT (now()),
        CONSTRAINT `webhooks_id` PRIMARY KEY(`id`)
);

[✓] Changes applied

Push 2

> drizzle-kit push:mysql

drizzle-kit: v0.20.6
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/var/www/frontend/drizzle.config.ts'
Reading schema files:
/var/www/frontend/db/schema.ts

 Warning  You are about to execute current statements:

ALTER TABLE `products` MODIFY COLUMN `productId` text;
ALTER TABLE `products` MODIFY COLUMN `title` text;
ALTER TABLE `products` MODIFY COLUMN `description` longtext;
ALTER TABLE `products` MODIFY COLUMN `image` text;
ALTER TABLE `products` MODIFY COLUMN `type` text;
ALTER TABLE `products` MODIFY COLUMN `json` json;
ALTER TABLE `products` MODIFY COLUMN `json` json;
ALTER TABLE `products` MODIFY COLUMN `provider` text;
ALTER TABLE `products` MODIFY COLUMN `portal` text;
ALTER TABLE `products` MODIFY COLUMN `price` decimal(10,2);
ALTER TABLE `products` MODIFY COLUMN `meta` json;
ALTER TABLE `products` MODIFY COLUMN `meta` json;
ALTER TABLE `products` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `products` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `sso` MODIFY COLUMN `state` text;
ALTER TABLE `sso` MODIFY COLUMN `origin` text;
ALTER TABLE `sso` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `webhooks` MODIFY COLUMN `params` json;
ALTER TABLE `webhooks` MODIFY COLUMN `params` json;
ALTER TABLE `webhooks` MODIFY COLUMN `json` json;
ALTER TABLE `webhooks` MODIFY COLUMN `json` json;
ALTER TABLE `webhooks` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `webhooks` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `products` ADD PRIMARY KEY(`id`);
ALTER TABLE `sso` ADD PRIMARY KEY(`id`);
ALTER TABLE `webhooks` ADD PRIMARY KEY(`id`);

 Warning  Found data-loss statements:

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
Error: Multiple primary key defined
    at PromiseConnection.query (/var/www/frontend/node_modules/drizzle-kit/bin.cjs:44259:26)
    at Command.<anonymous> (/var/www/frontend/node_modules/drizzle-kit/bin.cjs:63071:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_MULTIPLE_PRI_KEY',
  errno: 1068,
  sql: 'ALTER TABLE `products` ADD PRIMARY KEY(`id`);',
  sqlState: '42000',
  sqlMessage: 'Multiple primary key defined'
}

[Meid-KH]

Any update on this BUG ? I'm using a local db with MAMP, only the initial drizzle-kit push:mysql works.

[Winter]

Reporting the same issue as those above.

drizzle-orm version: 0.30.1 drizzle-kit version: 0.20.14 mysql2 version: 3.9.2 MariaDB version: 11.1.2

[YpsilonTM]

Anyone found a solutions for this ?

[johanwestling]

We ended up commenting out the troublesome statements in the generated .sql files to get the migration to complete. But we're not in production so we'll probably remove all "dev"-migrations and make an initial production migration before going live.

[jdelauney]

Same issue here :

• drizzle-orm : ^0.30.10
• drizzle-kit: ^0.21.1
• mysql2 : ^3.9.7
• MariaDB: Latest version

import { text, mysqlTable, timestamp, varchar, int, primaryKey,  uniqueIndex  } from 'drizzle-orm/mysql-core';

export const accounts = mysqlTable(
  'account',
  {
    userId: varchar('id', { length: 128 })
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    type: text('type').$type<AdapterAccount['type']>().notNull(),
    provider: varchar('provider',{ length: 128 }).notNull(),
    providerAccountId: varchar('providerAccountId',{ length: 128 }).notNull(),
    refresh_token: text('refresh_token'),
    access_token: text('access_token'),
    expires_at: int('expires_at'),
    token_type: text('token_type'),
    scope: text('scope'),
    id_token: varchar('id_token', { length: 128 }),
    session_state: text('session_state'),
  },
  account => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
  })
);

Error: Multiple primary key defined
    at PromiseConnection.execute (E:\CO-LAB-DIGITAL\sites-web\co-lab-digital\node_modules\.pnpm\drizzle-kit@0.21.1\node_modules\drizzle-kit\bin.cjs:103587:26)
    at Object.query (E:\CO-LAB-DIGITAL\sites-web\co-lab-digital\node_modules\.pnpm\drizzle-kit@0.21.1\node_modules\drizzle-kit\bin.cjs:104574:41)
    at mysqlPush (E:\CO-LAB-DIGITAL\sites-web\co-lab-digital\node_modules\.pnpm\drizzle-kit@0.21.1\node_modules\drizzle-kit\bin.cjs:107426:23)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Command.<anonymous> (E:\CO-LAB-DIGITAL\sites-web\co-lab-digital\node_modules\.pnpm\drizzle-kit@0.21.1\node_modules\drizzle-kit\bin.cjs:114419:7) {
  code: 'ER_MULTIPLE_PRI_KEY',
  errno: 1068,
  sql: 'ALTER TABLE `account` ADD PRIMARY KEY(`provider`,`providerAccountId`);',
  sqlState: '42000',
  sqlMessage: 'Multiple primary key defined'
}

No problem with a PostgreSQL DB, however in my case here, i can only use Mysql with MariaDB

[jdelauney]

It's ok for me I fixed the outcome:

1. I deleted the previously created tables.
2. I ran 'drizzle-kit generate'
3. I launched 'drizzle-kit push'

[YpsilonTM]

Deleting the tables works, but thats the whole point that you dont have to do that yourself 😅

[CDE90]

Any update on this? I'm getting the same error with these versions:

• "drizzle-orm": "^0.31.0"
• "drizzle-kit": "^0.22.1"
• "mysql2": "^3.10.0"
• MariaDB: 10.3.39-MariaDB-1:10.3.39+maria~ubu2004

[cannap]

i have the same i just dont understand what todo

https://github.com/user-attachments/assets/0d564603-d67c-45c2-9851-35e9ba25c626

"drizzle-orm": "^0.33.0", "drizzle-kit": "^0.24.1", 10.4.32-MariaDB - mariadb.org binary distribution

   import process from 'node:process'
import type { Config } from 'drizzle-kit'

export default {
  schema: './src/db/schema',
  out: './src/db/migrations',
  dialect: 'mysql',
  dbCredentials: {
    url: process.env.DB as string,
  },
} satisfies Config

okay i found out

i mixed generate/migration and push this is not good this messed everything up