Description:
When using the mysql-connector-java-8.0.15.jar with Adobe ColdFusion2016 running a query against a mysql database to select a date value returns an incorrect date value. The returned value is a date that is one day back from the actual value. If the database value is "2019-03-03" the select statement returned by ColdFusion is "2019-03-02". I've confirmed this on multiple date fields in multiple mysql databases. When using workbench the same query returns the correct value. This only occurs when using Adobe ColdFusion 2016 with the mysql-connector-java-8.0.15.jar.
I'm running:
RHEL 7 Server
mysql 8.0.15 Community Server
mysql-connector-java-8.0.15
Adobe ColdFusion 2016 Update 10
Once I switched back to using the mysql-connector-java5.1.47.jar the problem goes away and queries return the correct date value from the mysql database.
How to repeat:
Install ColdFusion 2016 on a rhel7 server.
Install mysql 8.0.15.
Copy the mysql-connector-java-8.0.15.jar to the /coldfusion2016/cfusion/lib directory.
Restart the coldfusion service.
Create a datasource in the coldfusion administrator that connects to a local mysql database.
Create a coldfusion file that runs a query against the database to select a date field from a table and displays that value on the screen.
Compare that value against the same query run from mysql workbench.
Mysql workbench will display the actual value but the coldfusion script will display a value that is one day back from the actual value.
Example:
Mysql workbench = "2019-03-03"
ColdFusion script = "2019-03-02"
Suggested fix:
Remove the mysql-connector-java-8.0.15.jar file from the /coldfusion2016/cfusion/lib directory.
Copy the mysql-connector-java-5.1.47.jar to the /coldfusion2016/cfusion/lib directory.
Restart the coldfusion service.
