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:
... to the most recently-built version:
This update included a major, breaking change update in the underlying MySQL Connector/J JAR file. My Adobe ColdFusion install went from:
... which resulted in the following ColdFusion error the when I went to use
dateFormat() on one of the dates returned in my
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 a
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 the
singleServerMode. 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.
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.
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!
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)
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 the
SELECT statement too (assuming you're not doing a
SELECT * ....
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. 👍
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:
Product Version = 8.0.22
Operating system = platform independent
Then stop any CommandBox servers that are running.
After unzipping the download, I then placed the following:
Then restart your server and voila, it should work again!
Anyway, thanks for this very helpful blog as always. ❤️
Boom! 💥 My brain is just getting going on this fine Sunday. Caffeine hasn't kicked in yet.
Ha ha 😂