March 19, 2021
Alert about MySQL Connector/J 8.0.23
Comments
(17)
March 19, 2021
Alert about MySQL Connector/J 8.0.23
Stargate Connections - Media Services Department
Newbie 1 posts
Followers: 1 people
(17)

We encountered a problem while setting up a new ColdFusion 2021 server, and felt it warranted an alert to other users who want to use MySQL through the community version of the MySQL Connector/J.

The currently available connector version 8.0.23 implements a change that breaks date values returned in queries in ColdFusion. Instead of returning the typical date or datetime string one expects, the connector now returns a LocalDateTime Java object. This is incompatible with any of the standard ColdFusion date-related functions, and causes errors.

To resolve the issue, we had to replace Connector/J 8.0.23 with Connector/J 8.0.22 instead, and everything is fine.

To download the connector: https://dev.mysql.com/downloads/connector/j/

To read about the issue:
https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-23.html#connector-j-8-0-23-feature

17 Comments
2023-09-27 22:48:42
2023-09-27 22:48:42

For those finding this in late 2023, there is good news to share, and indeed a few things to note (which are communicated in many comments of the tracker ticket offered above, but I am simplifying them here):

  • First, be aware that there have been various attempts at resolution to this issue, with more to come (as of my writing, Sep 27, 2023)
  • For those on CF2021 and CF2018, there is a new hotfix jar for you to obtain, and a new single jvm arg to use to revert this behavior (and treat the mysql date objects as strings)
  • For those on CF2023, you do NOT need any hotfix jar but you DO (for now) still need to add the JVM arg to revert this behavior
  • Most important: there is a planned coming update (for CF2021 and 2023) which will instead REVERT the default behavior of how CF processes the date objects returned from mySQL (with that connectorj driver more recent than 8.0.23). Once that comes out, we won’t need EITHER the hotfix jar OR the jvm arg to revert the behavior

Again, all this is communicated in the comments at the bottom of the tracker ticket. These are not my ideas or solutions but Adobe’s. I’m just serving as a messenger.

The new JVM arg (to be used, for now, with CF2023 or when using the newer hotfix jars for CF2021 or 2018) is:
-Dcoldfusion.jdbc.mysql.datetime.str=true
The newer hotfix jars for CF2021 are these: for CF2021 and for CF2018.

To be clear, there WERE indeed different hotfix jars and even different jvm args for CF2021 and 2018, offered over the past 3 years that this problem has existed.  These above are the latest.

And one more time: an update is planned (said to the the next one) which WILL finally put all this behind us, no longer needing EITHER the hotfix jar OR the JVM arg, as CF will correctly handle the returned date objects and convert them to the expected strings for us…and all this mess will (hopefully) be over.

Like
(1)
(4)
>
Charlie Arehart
's comment
2024-03-08 12:45:49
2024-03-08 12:45:49
>
Charlie Arehart
's comment

So, I think this change in the latest updater created a breaking-change in my app in a really subtle way. I was taking the date from the CFQuery object and I was using it to set the expires value on a Cookie. But, the cookie expected either a date/time value, a number of days, or some other special strings (ex, never). However, now that the date/time values aren’t _actually_ date/time values, my Cookies were all being set to expire in 2092. This is unfortunate.

I wrote a poc here: https://www.bennadel.com/blog/4608-coldfusion-2021-mysql-query-no-longer-returning-dates-as-dates.htm

Like
>
BenNadel
's comment
2024-03-08 12:50:18
2024-03-08 12:50:18
>
BenNadel
's comment

Yowza. Bummer if so, Ben, of course. I’m not able to test the poc right now, but thanks for sharing. Hope to later, or perhaps others will chime on. Have you opened a new tracker ticket? If so, share the number here and folks can follow along, of course.

Like
>
Charlie Arehart
's comment
2024-03-08 19:32:32
2024-03-08 19:32:32
>
Charlie Arehart
's comment

I left a comment in the previous ticket. I wasn’t sure if this was a bug, or more of a breaking change. Personally, I would much rather a real date/time come out of the database. But, it’s unclear to me what the intended behavior is.

Like
>
Charlie Arehart
's comment
2024-03-15 19:28:36
2024-03-15 19:28:36
>
Charlie Arehart
's comment

I have a weird date related issue I just email support about as well… 

Like
2023-02-05 16:56:43
2023-02-05 16:56:43

This is still broker as of mysql-connector-j-8.0.32.jar

Is there a CF fix for this, since does not look like mysql will be going back on this? Do we keep on using the 8.0.22 library then?

Like
(3)
>
Vahes
's comment
2023-02-05 19:39:46
2023-02-05 19:39:46
>
Vahes
's comment

Yes, there is a fix. It’s what’s referred to below, https://tracker.adobe.com/#/view/CF-4211276. See there the last comments about how there’s both a cf hotfix jar AND a JVM arg for you to add to cf, to solve this, in both cf2018 and cf2021 – – and yes, it’s STILL needed here in Feb 2023, because for some reason Adobe has not introduced the fix into any updates of the few cf updates since then. 🙁

Like
>
Charlie Arehart
's comment
2023-02-07 14:35:14
2023-02-07 14:35:14
>
Charlie Arehart
's comment

This might be just me, but is tracker.adobe.com working for everyone else. I’m not able to see the bug ticket or any ticket for that matter. Getting 400 Bad Request when requesting https://tracker.adobe.com/api/jira/project/CF

Like
>
ellipsisces1292chris
's comment
2023-02-08 20:54:02
2023-02-08 20:54:02
>
ellipsisces1292chris
's comment

@ellipsisces1292chris, that was a temporary problem that day, since fixed.

Like
2021-07-22 18:28:15
2021-07-22 18:28:15

I have some news on this. First, there is indeed already a bug report filed for this (CF-4211276). FWIW, it was filed on Mar 9, a week before this post.

Second, I also just added a comment there, clarifying both how this issue is clarified in the docs on the change in that 8.0.23 driver, and how it really would be up to Adobe to fix it (as it requires just a very simple change in a single java call they do for us)

Folks seeing this post should register a vote on the bug. There was only 1, when I added the second just now.

Like
(1)
(1)
>
Charlie Arehart
's comment
2021-07-22 18:31:04
2021-07-22 18:31:04
>
Charlie Arehart
's comment

Thanks Charlie

Like
2021-07-22 13:27:56
2021-07-22 13:27:56

Has a ticket been created?

Like
(3)
>
ellipsisces1292chris
's comment
2021-07-22 14:35:02
2021-07-22 14:35:02
>
ellipsisces1292chris
's comment

If one has not, then before that may be done, I have a question for anyone focused on this problem: has the problem been confirmed to still exist–and to not be solved by a later mysql driver update? (I am not focused on the problem myself, to be clear. I’m just helping out here, as I can.)

Like
>
Charlie Arehart
's comment
2021-07-22 17:03:08
2021-07-22 17:03:08
>
Charlie Arehart
's comment

Yes, tested 8.0.23, 24, 25, 26 all the same error on CF 2021

Like
>
ellipsisces1292chris
's comment
2021-07-22 18:27:26
2021-07-22 18:27:26
>
ellipsisces1292chris
's comment

Thanks for that clarification. I have done some more digging into this today.

I’ll add a new comment as a top-level reply, as sometimes people don’t see or pay attention to replies to replies.

Like
2021-03-24 17:48:13
2021-03-24 17:48:13

Stargate, if you may not know, the place where you would create the sort of bug report that Brad is referring to is https://tracker.adobe.com .

Like
2021-03-23 17:47:06
2021-03-23 17:47:06

Is there a ticket in for CF to better handle this new Java class out of the box in its date-related functions?

Like
Add Comment