[BUG]: Planetscale got packets out of order for 'serial' type on push

[Flowko]

What version of drizzle-orm are you using?

0.30.8

What version of drizzle-kit are you using?

0.20.16

Describe the Bug

trying to push my schema to planetscale i get an error, i have some tables with serial id:

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: timestamp('updated_at').notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
})

the error i get:

Warning: got packets out of order. Expected 0 but received 2
Error: unknown error: syntax error at position 46 near 'serial'
    at PromiseConnection.execute (C:\Users\folder\node_modules\.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:59949:26)
    at MySQL2Client.query (C:\Users\folder\node_modules\.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:62424:35)
    at Command.<anonymous> (C:\Users\folder\node_modules\.pnpm\[email protected]\node_modules\drizzle-kit\bin.cjs:66009:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'ALTER TABLE `posts` MODIFY COLUMN `id` serial AUTO_INCREMENT NOT NULL;',
  sqlState: 'HY000',
  sqlMessage: "unknown error: syntax error at position 46 near 'serial'"
}

mysql version im using:

"mysql2": "^3.9.6"

Expected behavior

it should push this schema fine, as it used to do before, this error only appeared after i updated ot the newer versions.

Environment & setup

No response

[Flowko]

i just downgraded the drizzle-kit version to 0.20.14 and that works fine, so something happneed in the newer versions.

seems to work with these, but not even sure: "mysql2": "3.9.2" "drizzle-kit": "0.20.14"

might be related to mysql driver: https://github.com/sidorares/node-mysql2/issues/653

[qw-in]

Just a note that I'm still seeing this issue on 0.20.17

[azezsan]

+1

[pahhh-blo]

The problem persists on version 0.21.00 with a mysql db on PlanetScale. This extremely annoying because I have to roll back to version 0.20.14 to push then go back to 0.20.17 to be able to use drizzle studio.

[kieronjmckenna]

+1

[rbuchberger]

Still seeing this issue on 0.23.0. My error is pretty much the same as OP but I'll add it for completeness:

Schema:

export const selfAssessmentReviews = mysqlTable('selfAssessmentReview', {
  id: serial('id').primaryKey(),
  selfAssessmentId: foreignKey('selfAssessmentId').notNull(),
  lgId: foreignKey('reviewerId').notNull(),
  isApproved: boolean('isApproved').notNull(),

  ...timestamps,
})

Error:

$ npm run db:push

> [email protected] db:push
> dotenv drizzle-kit push

drizzle-kit: v0.23.0
drizzle-orm: v0.32.0

No config path provided, using default path
Reading config file '/home/robert/development/learnlife/vitae/drizzle.config.ts'
[✓] Pulling schema from database...
Reading schema files:
/home/robert/development/learnlife/vitae/src/server/db/schema.ts

+ vitae_selfAssessmentReview table will be created
--- all table conflicts resolved ---

Warning: got packets out of order. Expected 0 but received 2
Error: unknown error: syntax error at position 57 near 'serial'
    at PromiseConnection.execute (/home/robert/development/learnlife/vitae/node_modules/drizzle-kit/bin.cjs:120
572:26)
    at Object.query (/home/robert/development/learnlife/vitae/node_modules/drizzle-kit/bin.cjs:121561:41)
    at mysqlPush (/home/robert/development/learnlife/vitae/node_modules/drizzle-kit/bin.cjs:124287:23)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async _Command.<anonymous> (/home/robert/development/learnlife/vitae/node_modules/drizzle-kit/bin.cjs:13
1596:7) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'CREATE TABLE `vitae_selfAssessmentReview` (\n' +
    '\t`id` serial AUTO_INCREMENT NOT NULL,\n' +
    '\t`selfAssessmentId` bigint unsigned NOT NULL,\n' +
    '\t`reviewerId` bigint unsigned NOT NULL,\n' +
    '\t`isApproved` boolean NOT NULL,\n' +
    '\t`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n' +
    '\t`updatedAt` timestamp ON UPDATE CURRENT_TIMESTAMP,\n' +
    '\tCONSTRAINT `vitae_selfAssessmentReview_id` PRIMARY KEY(`id`)\n' +
    ');\n',
  sqlState: 'HY000',
  sqlMessage: "unknown error: syntax error at position 57 near 'serial'"
}

A workaround is to downgrade to drizzle-orm 0.30.10 and drizzle-kit 0.20.14, as well as juggling the config changes since then. I've resorted to keeping a separate git worktree specifically for this purpose.

[wimluk]

The issue seems to be resolved with drizzle-kit: v0.23.0 and drizzle-orm: v0.32.1.

[Flowko]

sadly its not, it still throws that same error

[Flowko]

hi again, so one way of fixing this. at least it fixed it for me, is to drop using serial, since SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE as shown here https://orm.drizzle.team/docs/column-types/mysql#serial

i changed it from:

id: serial('id').primaryKey()

to:

id: bigint('id', {
		mode: 'bigint',
		unsigned: true,
	})
		.notNull()
		.autoincrement()
		.primaryKey()
		.unique(),

you could create a custom type as shown here: https://orm.drizzle.team/docs/custom-types but yeah, ill just use this instead to not deal with issues anymore

[qw-in]

edit: a poorly executed attempt at a joke

~~months later and SERIAL causing an out-of-order bug is still 👨🏻‍🍳💋 (read: comedy gold)~~

[rbuchberger]

Be nice to open source maintainers, especially considering that this team is only like a handful of people and their country is being invaded.

[rbuchberger]

@Flowko's approach works well, but instead of redefining every table you can just delete the serial import from drizzle and redefine it yourself at the top of your schema:

// Drizzle's serial function has a bug that breaks planetscale, so we need to
// reimplement it.
// https://github.com/drizzle-team/drizzle-orm/issues/2180
function serial(name: string) {
  return bigint(name, { mode: 'number', unsigned: true })
    .notNull()
    .unique()
    .autoincrement()
}

This does replace all of my unique key indices with new ones of a different name.

Edit: mode: 'bigint' broke all my query types; I had to use mode: 'number'

[qw-in]

Be nice to open source maintainers, especially considering that this team is only like a handful of people and their country is being invaded.

Ah, that is in no way how I meant this to be interpreted. That's my bad.