BUG `drizzle-kit push:mysql` with Two Primary Keys

[clburlison]

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 attempting to drop all primary keys.

Full Error Run

``` bun run db:push $ drizzle-kit push:mysql drizzle-kit: v0.20.7 drizzle-orm: v0.29.1 No config path provided, using default path Reading config file '/Users/clayton/src/other/drizzle-mysql-push-bug/drizzle.config.ts' Reading schema files: /Users/clayton/src/other/drizzle-mysql-push-bug/schema/index.ts Warning You are about to execute current statements: CREATE TABLE `book` ( `id` int AUTO_INCREMENT NOT NULL, `name` text, CONSTRAINT `book_id` PRIMARY KEY(`id`) ); CREATE TABLE `books_to_authors` ( `author_id` int NOT NULL, `book_id` int NOT NULL, CONSTRAINT `books_to_authors_book_id_author_id_pk` PRIMARY KEY(`book_id`,`author_id`) ); CREATE TABLE `user` ( `id` int AUTO_INCREMENT NOT NULL, `name` text, CONSTRAINT `user_id` PRIMARY KEY(`id`) ); [✓] Changes applied ❯ bun run db:push $ drizzle-kit push:mysql drizzle-kit: v0.20.7 drizzle-orm: v0.29.1 No config path provided, using default path Reading config file '/Users/clayton/src/other/drizzle-mysql-push-bug/drizzle.config.ts' Reading schema files: /Users/clayton/src/other/drizzle-mysql-push-bug/schema/index.ts Warning You are about to execute current statements: ALTER TABLE `books_to_authors` DROP PRIMARY KEY; ALTER TABLE `books_to_authors` ADD PRIMARY KEY(`book_id`,`author_id`); ALTER TABLE `books_to_authors` ADD PRIMARY KEY(`book_id`,`author_id`); Error: Multiple primary key defined at PromiseConnection.query (/Users/clayton/src/other/drizzle-mysql-push-bug/node_modules/drizzle-kit/bin.cjs:44278:26) at Command. (/Users/clayton/src/other/drizzle-mysql-push-bug/node_modules/drizzle-kit/bin.cjs:63113:33) at process.processTicksAndRejections (node:internal/process/task_queues:95:5) { code: 'ER_MULTIPLE_PRI_KEY', errno: 1068, sql: 'ALTER TABLE `books_to_authors` ADD PRIMARY KEY(`book_id`,`author_id`);', sqlState: '42000', sqlMessage: 'Multiple primary key defined' } ```

Reproducible repo which is just an example pulled from the docs website. https://github.com/clburlison/drizzle-mysql-push-bug.git

Docs example

Inlined schema example

import { int, text, primaryKey, mysqlTable } from "drizzle-orm/mysql-core";
export const user = mysqlTable("user", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name"),
});
export const book = mysqlTable("book", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name"),
});
export const booksToAuthors = mysqlTable("books_to_authors", {
  authorId: int("author_id"),
  bookId: int("book_id"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.bookId, table.authorId] }),
    pkWithCustomName: primaryKey({ name: 'custom_name', columns: [table.bookId, table.authorId] }),
  };
});

Versions

drizzle-kit: v0.20.8 drizzle-orm: v0.29.2 mysql: 8.0.33

Update log

• Dec 25, 2023 - confirmed v0.29.2 still has this bug

[clburlison]

Mostly user error.

This is not a good test case for what I was attempting to report. Problem with this test is that two primary keys were attempting to be assigned. Initial drizzle-kit push creates the database schema but secondary push failed as are two primary keys defined. Arguably this is still a bug that should be fixed but I'll leave it up to the maintainers to decide how to handle.