Invalid default value when using defaultNow()

Receiving error:
Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: 2ga5bxx7mhtso265npy7): Sql: "alter table menus add UNIQUE INDEX menus_public_id_idx (public_id)", BindVars: {REDACTED}`
Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: 2ga5bxx7mhtso265npy7): Sql: "alter table menus add UNIQUE INDEX menus_public_id_idx (public_id)", BindVars: {REDACTED}`
12 Replies
Cory
Cory14mo ago
Seems to happen where there is uniqueindex on table
Andrii Sherman
Andrii Sherman14mo ago
is it PlanetScale?
Cory
Cory14mo ago
Yes Any updates on this?
Andrii Sherman
Andrii Sherman14mo ago
Yeah, sorry Could you please explain steps to reproduce? Does this error happen in insert/select? Or do you see it while running migrations?
Cory
Cory14mo ago
It happens on drizzle-push. If I have defaultNow() on one of the columns, it starts to complain when I am trying to add indexes or push another update Not sure if this is somethikng on Planetscales side? Intersseing someone having similar issue here without Drizzle
Cory
Cory14mo ago
Stack Overflow
Planetscale: Creating unique index fails when a date column has a d...
I created this table in Planetscale CREATE TABLE community ( id serial PRIMARY KEY, name varchar(256), email varchar(256) NOT NULL, created_at timestamp(2) NOT NULL DEFAUL...
Cory
Cory14mo ago
This seems to solve it
Cory
Cory14mo ago
GitHub
Alter table not work due to the created_at timestamp column · pla...
Here is a my pages table: CREATE TABLE pages ( id bigint unsigned NOT NULL AUTO_INCREMENT, owner_id int, space_id int, type varchar(256) NOT NULL, name varchar(256) NOT NULL, slug var...
Cory
Cory14mo ago
So it seems like it was some sort of PlanetScale issue
Andrii Sherman
Andrii Sherman14mo ago
Yeah, so the case is that PlanetScale have some issues with using default (now()) I would suggest using
default(sql`CURRENT_TIMESTAMP`)
default(sql`CURRENT_TIMESTAMP`)
For “default now” with PlanetScale Try to regenerate table with this default and then it should work properly We will add .defaultCurrentTimestamp() for PlanetScale cases
TSilver
TSilver13mo ago
Facing the same issue, when will defaultCurrentTimestamp() be released? I'll try the suggested solution in the meantime and submit a PR to the docs repo about this. CURRENT_TIMESTAMP did not work for me i just get
vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'createdAt'
vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'createdAt'
Adding (3) aka my fsp after CURRENT_TIMESTAMP fixed the issue with default
TSilver
TSilver13mo ago
I found this issue regarding it but it seems to indicate that there is no workout for onUpdateNow() 😦 https://github.com/drizzle-team/drizzle-orm/issues/472
GitHub
[BUG]: planetscale - now() and current_timestamp() doesn't work whe...
What version of drizzle-orm are you using? 0.23.13 What version of drizzle-kit are you using? 0.17.4 Describe the Bug Create table fails on planetscale if FSP not specified for now() and current_ti...