[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.

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:

LaunchThat.AppOP•3/30/23, 9:47 PM

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?

LaunchThat.AppOP•3/30/23, 9:47 PM

oQUCBAgQIECAAAECBAgQIECAAAECBAgQIECAAAECBAgQIECAAAECBAgQIJAIiAATdqcECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQOAXEAHhhYIECBAgAABAgQIECBAgAABAgQIECBAgAABAgQIECBAgAABAgQIECBAgEAiIAJM2J0SIECAAAECBAgQIECAAAECBAgQIECAAAECBAgQIECAAAECBAgQIECAAIFfQAT4G1ogQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQKJgAgwYXdKgAABAgQIECBAgAABAgQIECBAgAABAgQIECBAgAABAgQIECBAgAABAgRARHgb2iBAAECBAgQIECAAAECBAgQIECAAAECBAgQIECAAAECBAgQIECAAAECBAgkAiLAhN0pAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBAgQIAAAQIECBD4BU..png

andersgee•3/31/23, 12:45 PM

Ive not used wordpress, but if wordpress created models in your database did you run prisma db pullprisma db pull to get the proper prisma.schema file or how did you "introspect" it?

(

prisma generate

prisma generate

only generates typescript types from the prisma.schema file, it does not sync your types with actual database)

LaunchThat.AppOP•3/31/23, 2:13 PM

sorry, yes i used introspect/pull

LaunchThat.AppOP•3/31/23, 3:12 PM

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

LaunchThat.AppOP•3/31/23, 3:24 PM

or on the wordpress side, modify the db interpreter to reqrite Null as 0000-00-00 00:00:00

LaunchThat.AppOP•3/31/23, 4:12 PM

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.

LaunchThat.AppOP•3/31/23, 4:12 PM

https://ryansechrest.com/2013/02/no-value-for-post_date_gmt-and-post_modified_gmt-when-creating-drafts-in-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.

LaunchThat.AppOP•3/31/23, 4:13 PM

LaunchThat.AppOP•3/31/23, 4:13 PM

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

LaunchThat.AppOP•3/31/23, 4:17 PM

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...

LaunchThat.AppOP•3/31/23, 4:20 PM

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)

LaunchThat.AppOP•3/31/23, 4:20 PM

I hope this helps someone in the future building headless wordpress with t3/prisma

[PRISMA] Value out of range for the type ERROR

Similar Threads

Similar Threads

Similar Threads