Drizzle Team•11mo ago

Planetscale db:push has issues with default values

 Warning  You are about to execute current statements:

ALTER TABLE `comments` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `comments` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `follows` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `media` MODIFY COLUMN `metadata` json DEFAULT ('{}');
ALTER TABLE `media` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `media` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `post_actions` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `posts` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `posts` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `gender` text NOT NULL DEFAULT ('other');
ALTER TABLE `users` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());

 Warning  You are about to execute current statements:

ALTER TABLE `comments` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `comments` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `follows` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `media` MODIFY COLUMN `metadata` json DEFAULT ('{}');
ALTER TABLE `media` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `media` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());
ALTER TABLE `post_actions` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `posts` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `posts` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `gender` text NOT NULL DEFAULT ('other');
ALTER TABLE `users` MODIFY COLUMN `created_at` timestamp DEFAULT (now());
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp DEFAULT (now());

Just tried to update my database, was prototyping it for some time, and thought I should sync it again (actually to add a property). Now I can see that there are a lot of default sets (unnecessary, as they are like so in the db), and especially the ones for "timestamp" are not working. Repeatidly getting error for an invalid default value:

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

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

Even if I manually modify the database hoping drizzle-kit would then forget these updates, it doesn't work. Running all on drizzle's latest versions.

3 Replies

Yiannis•11mo ago

If you are using .defaultNow() it will be removed soon. Use this instead: default(sqlCURRENT_TIMESTAMP) (Make sure you import the sql operator)

dsds10•11mo ago

This was very helpful, thank you very much! Now it passes at least. But still gives me these update statements on every run again:

 Warning  You are about to execute current statements:

ALTER TABLE `follows` MODIFY COLUMN `created_at` timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `media` MODIFY COLUMN `metadata` json DEFAULT ('{}');
ALTER TABLE `posts` MODIFY COLUMN `published` boolean;
ALTER TABLE `posts` MODIFY COLUMN `published` boolean DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `gender` text NOT NULL DEFAULT ('other');
ALTER TABLE `users` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

[✓] Changes applied

 Warning  You are about to execute current statements:

ALTER TABLE `follows` MODIFY COLUMN `created_at` timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `media` MODIFY COLUMN `metadata` json DEFAULT ('{}');
ALTER TABLE `posts` MODIFY COLUMN `published` boolean;
ALTER TABLE `posts` MODIFY COLUMN `published` boolean DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL;
ALTER TABLE `users` MODIFY COLUMN `is_creator` boolean NOT NULL DEFAULT false;
ALTER TABLE `users` MODIFY COLUMN `gender` text NOT NULL DEFAULT ('other');
ALTER TABLE `users` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

[✓] Changes applied

Is it caching the metadata or is drizzle just wrongly detecting the urge to change the schema?

Yiannis•11mo ago

That’s interesting! Since you are using planetscale, maybe try to comment out your schema completely in your schema drizzle file, then db push to your dev branch (providing you don’t store any data on there, which is my understanding that you shouldn’t be) and then uncomment your schema, and db push again

Gaming

Programming

Planetscale db:push has issues with default values