ColdFusion 2018 MySQL 8 datetime field error

[stuartw81]

Hi

We're in the process of setting up our new Windows 2016 server running ColdFusion 2018 with Update 1 installed. We run MySQL 8 and are using the most current Connector (mysql-connector-java-8.0.13.jar).

We're having a problem with a SELECT statement when the only field chosen is a DATETIME field that is only being used for the time section. The returned value from the database is '0000-00-00 00:01:00' (ie 1 hour).

SELECT Employee_LunchBreak_Length

FROM t_Employee

WHERE t_Employee_ID = '1'

The query above returns a 500 error and creates the following entry in the Application log.

"Error Executing Database Query.Zero date value prohibited The specific sequence of files included or processed is: (*path to template*)"

The query works in both MySQL Workbench and in PHP without issue. It also works on our previous CF10 / MySQL 5.6 server.

On both the new server (CF18) and old server (CF10) we're setting a connection string on the DSN of zeroDateTimeBehavior=CONVERT_TO_NULL&characterEncoding=utf8

I've seen problems with invalid TIMESTAMP fields, but understood that it was OK to use partial values in DATETIME fields.

Can anyone shed any light on this for me please?

Kind regards, Stuart

[Dave Watts]

I don't know the answer to this question. But what I'd recommend is that you perform some sort of casting or conversion right within the query to return a usable value.

Dave Watts, Fig Leaf Software

[WolfShade · Accepted solution]

stuartw81  wrote

the only field chosen is a DATETIME field that is only being used for the time section.

This is, most likely, the culprit.  0000-00-00 is an invalid date.  The column name "Employee_Lunchbreak_Length" should not be a DATETIME datatype.  Try TIME or TIMESTAMP datatypes.

UPDATE:  TIMESTAMP also uses year-mon-date as part of the format.  So, I think you need the TIME datatype.

HTH,

^ _ ^

[stuartw81]

Hi WolfShade, yes changing the field's datatype has worked, thanks.

It's odd that MySQL workbench allowed these values, but then ColdFusion would throw the 500 error. I would have expected a more descriptive standard message.

Many thanks, Stuart

[WolfShade]

I have no explanation for the discrepancy, unfortunately.

Please mark my answer as correct, for any future users who have the same issue.

V/r,

^ _ ^

[WolfShade]

Thank you for marking my answer as correct.  I do appreciate it.

V/r,

^ _ ^