Hello,
I’ve been troubleshooting timezone issues between Lucee / MySQL and wonder
if this is a kind of bug in Lucee. The issue boils down to, I think Lucee
is auto converting timezones specified and is changing the value written /
read from the database.
The following code is attached as a test case also.
DATABASE SETUP:
create database timetest;
create user timetest@‘%’ identified by ‘123456’;
grant all on timetest.* to timetest@‘%’ identified by ‘123456’;
use timetest;
CREATE TABLE dates (
date DATETIME NULL DEFAULT NULL,
timestamp TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)
APPLICAITON:
component {
this.timezone = “UTC”;
this.datasources[“timetest”] = {
class: ‘org.gjt.mm.mysql.Driver’
, connectionString:
‘jdbc:mysql://192.168.33.10:3306/timetest?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&useLegacyDatetimeCode=true’
, username: ‘timetest’
, password: “encrypted:5149d40ab848444f5678f3470433cd4e076102d7c532f4ec”
// optional settings
, timezone:‘UTC’
};
this.datasource = “timetest”;
}
TEST CASE:
params = [ {sqltype:'timestamp', value:now()} ];//Clear out any previous entries in the table
query name=“truncate” {
echo(“truncate dates”);
}
// //Insert a record to save our date
query name=“date” params=“#params#”{
echo(“INSERT INTO dates (date) values (?)”);
}
//Query the record, it should return in UTC
query name=“get” {
echo(“SELECT * FROM dates LIMIT 0,1”);
}
writeDump(get);
setting showdebugoutput=“true”;
Added to /etc/my.cnf
default_time_zone=‘+00:00’
Despite setting everything to UTC, when running the rest case, MySQL
receives the following insert from Lucee (note it is 12:40 PM EDT at the
time of this test):
https://lh3.googleusercontent.com/-gjk3BAkMuUg/VwPtBLHE0uI/AAAAAAAADho/TSEnaIdOMFke2EZXnhlwa9g9BYwFo3uIA/s1600/lucee.pngThe
timestamp field is a control to see that MySQL has the intended UTC time.
And Lucee is converting to the following on select (adding hours to both
the date AND timestamp fields):
https://lh3.googleusercontent.com/-gjk3BAkMuUg/VwPtBLHE0uI/AAAAAAAADho/TSEnaIdOMFke2EZXnhlwa9g9BYwFo3uIA/s1600/lucee.png
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png
Troublechooting further, it seems related to th
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png
ese:
https://issues.jboss.org/browse/RAILO-2555
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png
and
If I change the connection string to
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png
&useLegacyDatetimeCode=
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.pngfalse,
that appears to solve the issue.
The default behavior when creating a datasource is this setting is true,
should Lucee have the default behavior as false?