[PRISMA] Value out of range for the type ERROR

I am attempting to create a headless wordpress setup with t3 1) I created a new wordpress instance and connected it to Planetscale 2) Tables are automatically generated (i believe during wordpress initialization) and pushed to Planetscale 3) I then connected t3 to the same Planetscale database and ran "npx prisma generate" 4) I got all the models introspected from my 'existing' wordpress database. 5) I try to load prisma studio. It loads all tables but the 'wp_posts' table fine. wp_posts table gives me an error
Value out of range for the type. The column `post_date_gmt` contained an invalid datetime value with either day or month set to zero.
Value out of range for the type. The column `post_date_gmt` contained an invalid datetime value with either day or month set to zero.
Reference: https://github.com/prisma/prisma/issues/5006 In the github thread a couple people posted their solutions, not sure how to make them work for me. I am looking for a solution that I can use with new and existing wordpress installs and wanting to see what this community thinks. Planetscale wp_posts screenshot:
9 Replies
Trader Launchpad
From what I can tell the issue is that wordpress is creating the wp_posts table in a way that prisma cannot read, specifically regarding 'zero' DateTime. I seem to have been successful replacing the default 'Entry' router for a post router, and rendering them on the frontend. Why am i getting this error in Prisma studio only? Has anyone else had an issue like this before? How would i possibly solve it? Wordpress is the one that created the structure of the table. Would I need to alter wordpress code to insert a unix timestamp here instead of a string?
andersgee
andersgee2y ago
Ive not used wordpress, but if wordpress created models in your database did you run prisma db pull to get the proper prisma.schema file or how did you "introspect" it? ( prisma generate only generates typescript types from the prisma.schema file, it does not sync your types with actual database)
Trader Launchpad
sorry, yes i used introspect/pull every time i do a
npx prisma db pull
npx prisma db pull
it will overwrite the contents of schema.prisma. It is getting the
@default(dbgenerated("'0000-00-00 00:00:00'")) @db.DateTime(0)
@default(dbgenerated("'0000-00-00 00:00:00'")) @db.DateTime(0)
from the planetscale database table which was created by wordpress. What I need is some sort of translator, or intermediate parser that lets it stay default 0000-00-00 00:00:00 in the database but prisma reads it as 0 or NULL or on the wordpress side, modify the db interpreter to reqrite Null as 0000-00-00 00:00:00 I pulled up the db in dbeaver, noticed that the only records that had 0000 as a post_date_gmt was wordpress drafts. It stores post_date as the now() time but not post_date_gmt (i agree with the post below they should be in sync for all posts, published or drafts...bad wordpress.
Trader Launchpad
If i delete this row from the post table, run
npx prisma db pull && npx prisma generate
npx prisma db pull && npx prisma generate
, i get no more errors loading in prisma studio
Trader Launchpad
I could add a db.ini file to wordpress to alter how it stores in database, to get rid of 0000 values in dateTime. This is not efficient with existing large databases where we may have to patch previous tables to work even after the codefix. Since the error is on prismas side, I am going to insert a new column into the table
post_date_gmt_prisma
post_date_gmt_prisma
that copies the value from
post_date_gmt
post_date_gmt
if it is non 0, or is NULL if it is 0 see https://github.com/prisma/prisma/issues/5006#issuecomment-1224036491
GitHub
Support 0000-00-00 00:00:00 for MySQL · Issue #5006 · prisma/pri...
Our mysql instance allow datetime to be zero. Seems to be failing at as some values as null or 0000-00-00, 0000-00-00 00:00:00 or 0000-00-00 00:00:00.000. Tried to read datetimes as just strings on...
Trader Launchpad
then after there is a new datetime column that prisma can read, ill use
@ignore
@ignore
on the
post_date_gmt
post_date_gmt
column in prisma.schema like this:
post_date DateTime @default(dbgenerated("'0000-00-00 00:00:00'")) @db.DateTime(0)
post_date_gmt DateTime @default(dbgenerated("'0000-00-00 00:00:00'")) @ignore @db.DateTime(0)
post_date_gmt_prisma DateTime?@map("post_date_gmt_prisma") @db.DateTime(0)
post_date DateTime @default(dbgenerated("'0000-00-00 00:00:00'")) @db.DateTime(0)
post_date_gmt DateTime @default(dbgenerated("'0000-00-00 00:00:00'")) @ignore @db.DateTime(0)
post_date_gmt_prisma DateTime?@map("post_date_gmt_prisma") @db.DateTime(0)
I hope this helps someone in the future building headless wordpress with t3/prisma
Want results from more Discord servers?
Add your server