The Value Class java.time.LocalDateTime Cannot Be Converted To A Date In ColdFusion
Yesterday, I went to debug an issue with the latest Adobe ColdFusion 2018 Updater. So, I first updated my CommandBox Docker Image (in an effort to reproduce the issue in my local development environment). This sent me down a 4-hour rabbit hole just trying to getting my ColdFusion site up-and-running before I could even debug the originally issue. One of the problems that I ran up against was an incompatibility with Adobe ColdFusion and the latest MySQL Connector/J, version 8.0.23+.
When I updated my CommandBox Docker Image, I went from:
ortussolutions/commandbox:adobe2018-2.7.2
... to the most recently-built version:
ortussolutions/commandbox:adobe2018-3.4.4
This update included a major, breaking change update in the underlying MySQL Connector/J JAR file. My Adobe ColdFusion install went from:
mysql-connector-java-5.1.42-bin.jar
... to:
mysql-connector-java-8.0.25.jar
... which resulted in the following ColdFusion error the when I went to use dateFormat() on one of the dates returned in my CFQuery tag:
The value class java.time.LocalDateTime cannot be converted to a date.
Of course, I had no idea that this was related to the MySQL driver update. In fact, I had no idea what in the heck-fire was going on at all! Thankfully, after about an hour of Googling unsuccessfully for the issue, I found a MySQL bug ticket in which Scott Jibben mentioned that the latest MySQL Connector/J version was breaking ColdFusion:
This change breaks the MySQL implementation in Adobe ColdFusion.
For query resultsets, a
java.time.LocalDateTimeobject is returned instead of adatetimestring.Reverting to 8.0.22 fixed this problem.
This is when I checked in the Adobe ColdFusion Admin and saw that I was on 8.0.25. I needed to downgrade the MySQL driver. I didn't really know what the best was to do this was, using Docker and CommandBox. But, I ended up downloading the 8.0.22 Connector/J version, and then creating a Dockerfile that added it to the Image while removing the newer version:
FROM ortussolutions/commandbox:adobe2018-3.4.4
# The core image seems to break if APP_DIR is using wwwroot.
# --
# https://github.com/Ortus-Solutions/docker-commandbox/issues/55
RUN box config set server.singleServerMode=false
# Downgrade MySQL Connector/J to fix LocalDateTime casting issue.
ADD ./mysql-connector-java-8.0.22.jar /usr/local/lib/serverHome/WEB-INF/cfusion/lib/
RUN rm /usr/local/lib/serverHome/WEB-INF/cfusion/lib/mysql-connector-java-8.0.25.jar
This seems to have fixed my local development environment for Adobe ColdFusion 2018, updater 3 using the latest CommandBox Docker Image.
ASIDE: You'll see that my
Dockerfilealso includes a CommandBox config setting to disable thesingleServerMode. This was also something that broke in the latest CommandBox image. The fix I applied was suggested in a CommandBox issue on GitHub.
Once I had a better understanding of what was going on, I did some better Googling and came across more information on the issue:
I'm not sure why these articles didn't come up in my original search?
The whole adventure served to remind me how little I actually know about Docker. And about getting the core server stuff working. So, this was a "nice" reality check. I assume that the future Adobe ColdFusion Updater will either fix the underlying issue; or, will downgrade the MySQL Connector/J. In any case, at least I have my local development environment up-and-running (and can get back to debugging the original Updater 3 issue).
Want to use code from this post? Check out the license.
Reader Comments
Thanks for the info, I just ran into this issue myself! Unfortunately, I didn't find your article until after fixing it, but it did lead me to another solution that doesn't mess with the connector. If you use 'parsedatetime' : example - #dateformat(parsedatetime(yourdatetimevar),'mm/dd/yyyy')# - it works. It works with other date functions as well. I'm not concerned about multiple time zones, so I don't know if that would have an impact, but it worked for my needs.
@Stuart,
Oh, interesting! I wonder why that works - maybe it implicitly converts the date to String before then parsing it into a ColdFusion date/time value. All in all, just frustrating stuff. I hope Adobe fixes it soon!
Hi Ben
Just wanted to thank you for this post - I ran in this issue when upgrading to CF2021 on my dev machine. Thought I was going to have to convert all datetime columns to timestamps (ugh) but using 8.0.22 connector .jar also worked for me.
Shame that 6 months later and Adobe still haven't fixed this as this means we're having to use a connector version that is now quite old (current v is 8.0.29)
@Nick,
Awesome! Glad this helped out. Yeah, seems like such a strange breaking change in the driver. Or the runtime? I'm not really sure where the problem is 🤪 I just want to query my data and get on with my app!
Posting another solution would be to
CONVERT(theDateField, DATE)in theSELECTstatement too (assuming you're not doing aSELECT * ....@Steve,
Oh that's good to know. That could work in a pinch if it wasn't an entire application breaking, but just a small script something. 👍
Hi Ben
I am getting this issue after a migration for a client from CF2018.02 to CF2021.
I have just managed to secure a new Coldfusion contract after 3 years of pure Angular development.
Coldfusion projects in the UK are rarer than hen's teeth. 😕
I am over the moon.🙂
Anyway, just a little more information, for those that cannot be bothered to engage their brain on a Sunday 😀
With the link you have posted:
For Windows users, I then clicked on Archives and then:
Then stop any CommandBox servers that are running.
After unzipping the download, I then placed the following:
Into:
Then restart your server and voila, it should work again!
Anyway, thanks for this very helpful blog as always. ❤️
@Charles,
Boom! 💥 My brain is just getting going on this fine Sunday. Caffeine hasn't kicked in yet.
@Ben,
Ha ha 😂
@Ben,
I tried this, it initially worked, but now getting this error:
is not indexable by CONVERT(EXPIRATION, type: java.lang.IllegalArgumentException
@Vahe,
Sorry, I am not sure what that error means. Given that you are seeing a Java error (and not a MySQL error), it makes me think that you're executing
CONVERT(EXPIRATIONin the ColdFusion context, and not in the SQL context. But, I don't understand how that would be possible.@Charles
Thank you so much! I'm on ColdFusion 11 and switching the .jar file from 8.0.33 to 8.0.22 as you described fixed the problem for me.
@Stuart - your solution did work for me as well, before the switch, but would have involved a lot of recoding. Great solution for those unable to go backward.