Invalid default value for timestamp

Hi, I have recently started learning SQL and I've run into this problem with Drizzle Kit. I have this promoCodes table:

// PlanetScale (MySQL) + Drizzle ORM
export const promoCodes = mysqlTable(
  'promo_codes',
  {
    id: serial('id').primaryKey().autoincrement(),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').onUpdateNow(),
    // ...
  }
)

// PlanetScale (MySQL) + Drizzle ORM
export const promoCodes = mysqlTable(
  'promo_codes',
  {
    id: serial('id').primaryKey().autoincrement(),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').onUpdateNow(),
    // ...
  }
)

and when I try to push it using pnpm drizzle-kit push:mysql, I get this error:

target: xxx.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: ues4twwhaqs5wh3lm76u): Sql: "alter table promo_codes modify column created_at timestamp default now()", BindVars: {REDACTED}

target: xxx.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: ues4twwhaqs5wh3lm76u): Sql: "alter table promo_codes modify column created_at timestamp default now()", BindVars: {REDACTED}

9 Replies

bloberenober•17mo ago

@Andrii Sherman

Andrii Sherman•17mo ago

If you are using PlanetScale try to change .defaultNow() to

.default(sql`CURRENT_TIMESTAMP`)

.default(sql`CURRENT_TIMESTAMP`)

PlanetScale is restricting (now()) expressions

Fyzz•17mo ago

Just inquiring further about this, any fix yet? I did as suggested and changed .defaultNow() to

  .default(sql`CURRENT_TIMESTAMP`)

  .default(sql`CURRENT_TIMESTAMP`)

but i'm still receiving the same error Ps I am also using planetscale

bloberenober•17mo ago

Could you try running the alter column query manually on planetscale to check if it works at all?

Fyzz•17mo ago

looks to be the same error on their end ill check the docs

create table users (
id varchar(256) not null primary key
created_at timestamp(2) not null default current_timestamp()
);
target: hobby.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'created_at timestamp(2) not null default now() )' at line 3 (errno 1064) (sqlstate 42000) (CallerID: oyl1qfn8efl5jv0d0jot): Sql: "create table users (\nid varchar(256) not null primary key\ncreated_at timestamp(2) not null default now()\n)", BindVars: {REDACTED}

create table users (
id varchar(256) not null primary key
created_at timestamp(2) not null default current_timestamp()
);
target: hobby.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'created_at timestamp(2) not null default now() )' at line 3 (errno 1064) (sqlstate 42000) (CallerID: oyl1qfn8efl5jv0d0jot): Sql: "create table users (\nid varchar(256) not null primary key\ncreated_at timestamp(2) not null default now()\n)", BindVars: {REDACTED}

Liltripple_reid•17mo ago

Hey is this error still showing up or have you found a workaround?

Fyzz•17mo ago

For now I just removed the created at and updated at, lol If I figure it out I’ll post in here 😁

ryanagillie•17mo ago

I had to change it to

.default(sql`CURRENT_TIMESTAMP(3)`)

.default(sql`CURRENT_TIMESTAMP(3)`)

and for updateNow()

.default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)

.default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)

but that updateNow is probably really not recommended For some reason planetsale requires CURRENT_TIMESTAMP to be called as a function and not just an interpreted value

bloberenober•17mo ago

Weird We'll probably need to allow customizing the onUpdate value, in addition to onUpdateNow

Gaming

Programming

Invalid default value for timestamp