MySQL Timezone Confusion

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/-kJleb_Z5JLA/VwPrAqyTnCI/AAAAAAAADhU/N8iScuxMOAQYnflVMP6WUqqH5hsjYEMsA/s1600/mysql.png

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

http://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-java-connection
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png

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?

test case attachedOn Tuesday, April 5, 2016 at 12:56:37 PM UTC-4, Rory Laitila wrote:

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/-kJleb_Z5JLA/VwPrAqyTnCI/AAAAAAAADhU/N8iScuxMOAQYnflVMP6WUqqH5hsjYEMsA/s1600/mysql.png

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

http://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-java-connection
https://lh3.googleusercontent.com/-scCWAasnjAU/VwPrP1dXKoI/AAAAAAAADhY/-N1rynb_M2w1FJ_zyxsoEArCCOiVGEoIQ/s1600/lucee.png

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?

timetest.zip (31.4 KB)

I have just seen this on my dev machine. Very confusing… and updating the datasource resolves the issue. How i reproduced. Add a simple error to the query to get lucee to dump out the query text. Then paste into workbench. Lucee is giving different result than workbench, workbench is correct result.

click “update” to datasource and issue resolves itself.

Lucee 5.3.7.47