Drizzle Team•2y ago

Syntax error mysql migration using composite primary key

--> statement-breakpoint
CREATE TABLE `member_to_address` (
    `member_id` varchar(32) NOT NULL,
    `address_id` varchar(32) NOT NULL
);
ALTER TABLE `member_to_address` ADD PRIMARY KEY(`member_id`,`address_id`);

--> statement-breakpoint
CREATE TABLE `member_to_address` (
    `member_id` varchar(32) NOT NULL,
    `address_id` varchar(32) NOT NULL
);
ALTER TABLE `member_to_address` ADD PRIMARY KEY(`member_id`,`address_id`);

This part of my migration won't run due to:

DatabaseError: syntax error at position 114 near 'ALTER'
    at Connection.execute (webpack-internal:///(sc_server)/./node_modules/@planetscale/database/dist/index.js:95:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  status: 400,
  body: {
    message: "syntax error at position 114 near 'ALTER'",
    code: 'UNKNOWN'
  }
}

DatabaseError: syntax error at position 114 near 'ALTER'
    at Connection.execute (webpack-internal:///(sc_server)/./node_modules/@planetscale/database/dist/index.js:95:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  status: 400,
  body: {
    message: "syntax error at position 114 near 'ALTER'",
    code: 'UNKNOWN'
  }
}

The table looks like this:

export const memberToAddress = mysqlTable(
  "member_to_address",
  {
    memberId: varchar("member_id", { length: 32 }).notNull(),
    addressId: varchar("address_id", { length: 32 }).notNull(),
  },
  (memberToAddress) => ({
    cpk: primaryKey(memberToAddress.memberId, memberToAddress.addressId),
    memberIdx: index("member_idx").on(memberToAddress.memberId),
    addressIdx: index("address_idx").on(memberToAddress.addressId),
  })
);

export const memberToAddress = mysqlTable(
  "member_to_address",
  {
    memberId: varchar("member_id", { length: 32 }).notNull(),
    addressId: varchar("address_id", { length: 32 }).notNull(),
  },
  (memberToAddress) => ({
    cpk: primaryKey(memberToAddress.memberId, memberToAddress.addressId),
    memberIdx: index("member_idx").on(memberToAddress.memberId),
    addressIdx: index("address_idx").on(memberToAddress.addressId),
  })
);

Where I want to link multiple addresses to a member object. Im using the planetscale-serverless migrator.

3 Replies

Gerbuuun•2y ago

On the internet I found that composite primary keys are possible in MySQL. Am I mistaken? And if there is a better/standard way of doing... please let me know

Andrii Sherman•2y ago

No problems on your side Will fix it in next patch release Planned for tomorrow 🫡

Gerbuuun•2y ago

❤️

Gaming

Programming

Syntax error mysql migration using composite primary key