Support `0000-00-00 00:00:00` for MySQL

[adamfranco2012]

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 connection string but prisma converts those back to DateTime. Also tried to return zero dates as null on the connection string.

generator photon {
  provider = "photonjs"
}

generator nexus_prisma {
  provider = "nexus-prisma"
}

datasource db {
  provider = "mysql"
  url = env("MYSQL_URL")
}

model Compliance {
  id                     Int
  expirationDate         DateTime?
  lastUpdateUTC          DateTime  
}

leads to

Could not retrieve NaiveDateTime from Value in src/libcore/option.rs:1034:5

[divyenduz]

@adamfranco2012 would it be possible to share a minimal repro?

Our mysql instance allow datetime to be zero

Is that a specific configuration? Was the schema generated by Prisma or is this an existing project?

[adamfranco2012]

We are using an existing project where the MySQL instance allows for dates to be like “0000-00-00 00:00:00” instead of null. We are using MySQL 5.6 which allows this and MySQL 5.7 with strict mode disabled. If you need more information please let me know

…

On Thu, Sep 26, 2019 at 8:31 AM Divyendu Singh ***@***.***> wrote: @adamfranco2012 <https://github.com/adamfranco2012> would it be possible to share a minimal repro? Our mysql instance allow datetime to be zero Is that a specific configuration? Was the schema generated by Prisma or is this an existing project? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/prisma/photonjs/issues/226?email_source=notifications&email_token=ABEDUNB7EEEMWP2YPQ2RMCLQLTIUHA5CNFSM4IWTREZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7V72II#issuecomment-535559457>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABEDUNFE2WGUCFU2RWKY5W3QLTIUHANCNFSM4IWTREZA> .

[divyenduz]

If possible, a small repro, with your schema and the steps you take to reach the error. Otherwise, we will try an isolated reproduction.

[pantharshit00]

@adamfranco2012 If you can share your database schema, that would be very helpful.

[adamfranco2012]

CREATE TABLE `test_schema` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expiration_date` datetime(3) DEFAULT NULL,
  `last_update_UTC` datetime(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

We also disable zero_date_allowed to allow '0000-00-00'. Here is an article about that https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

[pantharshit00]

I am able to reproduce this now.

To reproduce you need to execute SET sql_mode="NO_ZERO_DATE" in your mysql instance than you need to query any date field set to 0000-00-00 00:00:00

I am not sure we will support it though, MySQL docs say this feature is deprecated.

[tomhoule]

Status update: this is a fringe MySQL feature we do not support at all at the moment, and would probably be a significant amount of work. Whether we want to support this is a product decision.