Invalid default value for 'updatedAt'

Running into this issue, oddly enough this once worked and now breaks.

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:34122:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:51859:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}`

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:34122:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:51859:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}`

16 Replies

Angelelz•15mo ago

Is this for Mysql?

Jaxwn•15mo ago

i have the same problem, here is what i did to fixed it 1. copy and drop the entire table(in case you don't have any data yet) 2. paste it back in 3. i suggest copy the created_at and updated_at from this: https://discord.com/channels/1043890932593987624/1142956198488969359/1143601846183800853 ps: this is mysql btw

focused_morningOP•15mo ago

this is mysql @angelelz @therealguy_ dropping a table seems a little harsh

Angelelz•15mo ago

Interesting, I just tested the query:

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));

And it worked just fine.

focused_morningOP•15mo ago

Did you try to generate and push from a schema? I guess the generated sql would be the same. we are using planetscale btw

Angelelz•15mo ago

Can you show the generated migration?

focused_morningOP•15mo ago

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));--> statement-breakpoint
ALTER TABLE `InventoryItem` MODIFY COLUMN `updatedAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));--> statement-breakpoint
ALTER TABLE `InventoryItem` MODIFY COLUMN `updatedAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));

Angelelz•15mo ago

I'm not a planetscale user but that is correct mysql syntax. Do you want to try dropping the migration, and delete the fsp value on your schema. just to see the maybe planetscale doesn't support it.

IceAge2OnDVD•15mo ago

Had this same issue today! managed to fix it by changing all my

timestamp("time").defaultNow()

timestamp("time").defaultNow()

timestamp("time").default(sql`current_timestamp()`)

timestamp("time").default(sql`current_timestamp()`)

seems to be an issue introduced in more recent version, also was very fineky to fix. Had to push an empty schema to a development branch first before pushing the fix

focused_morningOP•15mo ago

@justhugo I would ask in a separate thread so this one stays on topic.

Hugo•15mo ago

my bad

focused_morningOP•15mo ago

no worries, I just want your question answered. Updating this post, this doesn't work. Trying @iceage2ondvd 's fix next and will update.

    createdAt: datetime("createdAt", { mode: "string", fsp: 3 })
      .default(sql`(now(3))`)
      .notNull(),

    createdAt: datetime("createdAt", { mode: "string", fsp: 3 })
      .default(sql`(now(3))`)
      .notNull(),

timestamp("time").default(sqlcurrent_timestamp()) doesnt work for me. @iceage2ondvd I also tried this with no success. Not sure what i'm doing wrong, any thoughts? @angelelz timestamp("createdAt").defaultNow()

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());

ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt timestamp default (now())", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt timestamp default (now())", BindVars: {REDACTED}`

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt timestamp default (now())", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt timestamp default (now())", BindVars: {REDACTED}`

Angelelz•15mo ago

I believe planetscale does not support .defaultNow() Try it like this

timestamp("createdAt").default(sql`current_timestamp`)

timestamp("createdAt").default(sql`current_timestamp`)

Again I don't use planetscale, I'm just shooting in the dark here

focused_morningOP•15mo ago

I tried this: timestamp("createdAt").default(sqlcurrent_timestamp) earlier without success.. Oddly enough the push command is failing, but planetscale is registering some of the changes including the createdAt change, but the command is failing so the other schema changes aren't included.

IceAge2OnDVD•15mo ago

So the fix for me was to comment out my whole schema.ts file, push to planetscale (just to drop all the tables), then apply the current_timestamp fix. For some reason this seemed to fix things if you do this on a seperate branch then you shouldn't have data loss (hopefully) I recently updated drizzle for the first time in a month, and i think it must have been working at some point because I've been using drizzle in my codebase for a while maybe im misremembering things

focused_morningOP•15mo ago

I appreciate the insights thank you, I'll try it out but I don't like the idea of dropping data to fix this.

Gaming

Programming

Invalid default value for 'updatedAt'