[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
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
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?
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)sorry, yes i used introspect/pull
every time i do a it will overwrite the contents of schema.prisma. It is getting the 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.
Ryan Sechrest
No value for post_date_gmt and post_modified_gmt when creating draf...
WordPress doesn't set post_date_gmt or post_modified_gmt when creating drafts. Here's the fix for that.
If i delete this row from the post table, run , i get no more errors loading in prisma studio
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 that copies the value from 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...
then after there is a new datetime column that prisma can read, ill use on the column in prisma.schema like this:
I hope this helps someone in the future building headless wordpress with t3/prisma