Osticket: Issue reading database timezone

[mattkolb]

Prerequisites

• [Y] Can you reproduce the problem in a fresh installation of the "develop" branch?
• [N] Do you have any errors in the PHP error log, or javascript console?
• [Y] Did you check the osTicket forums?
• [Y] Did you perform a cursory search to see if your bug or enhancement is already reported?

For more information on how to write a good bug report

Description

https://github.com/osTicket/osTicket/blob/cca97edae0bb11eb6f565b2b250ec72ba0c9e2a2/include/mysqli.php#L118

That line reads global.system_time_zone instead of the session.time_zone. If using Amazon Web Services Relational Database Service (AWS RDS), the system_time_zone cannot be set to anything other than UTC. However, it is possible to set the time zone uses for all connections. Doing so is reflected in session.time_zone (and global.time_zone), but it is not reflected in global.system_time_zone.

The line of code mentioned above in db_timezone() should be changed to this:
db_get_variable('time_zone', 'session')

Expected behavior: [What you expected to happen]

session.time_zone should be read

Actual behavior: [What actually happened]

global.system_time_zone is read

Versions

Admin panel -> Dashboard -> Information which also additionally gives you information about your server.

v.1.12.3 though the current master branch has the same issue

Also, please include the OS and what version of the OS you're running. As well as your browser and browser version.

The pertinent information is that MySQL is running via Amazon Web Services Relational Database Service (AWS RDS).

[mattkolb]

Other notes and symptoms of the issue:

/scp/system.php
When viewing that in the browser, the "Timezone" listed under "Database Information and Usage" will be incorrectly displayed until the line of code is changed.

This was first noticed because we were unable to obtain a lock on any tickets. The database timezone is set to -6 hours (America/Chicago). When Lock->getTime() is called, Lock->getExpireTime() would output the correct expiration time (and the row in the database likewise uses the correct timezone). However, Misc::dbtime() reports a time 6 hours into the future because the database timezone is incorrect (stated as UTC). When it takes a time in the current timezone and adjusts by the timezone offset (ie. makes an adjustment that has already been made), the result is a time 6 hours in the future. Because of this, Lock->getTime() always returns 0 (ie. $this->getExpireTime() - Misc::dbtime() is always negative and max(0, $this->getExpireTime() - Misc::dbtime()) always returns 0).

[protich]

@mattkolb - thank you very much for the report!

You're indeed correct about the issue, but the suggested solution won't work and actually would make the problem worse! Let me explain'

--

MySql stores data based on system_time_zone - that's all and good, but the problem is osTicket uses DATETIME fields - unlike TIMESTAMP field - the time doesn't get translated to session time zone on select. osTicket converts the stored 'date time` - to user's time zone - this meant we could totally ignore session time zone.

So we thought!

It turns out session time zone affects some _MySQL's Date & Time Functions_ like NOW() - this is the culprit in this case.

We're thinking hard on how to solve the issue - I just wanted to acknowledge it.

[JediKev]

@mattkolb

I have linked below a pull request that should address this issue. Please apply the patch, do some testing, and let us know if this indeed solves the issue. 👍

• https://github.com/osTicket/osTicket/pull/5163

Cheers.

[mattkolb]

The patch is close. Instead of using @@global.time_zone, use @@global.system_time_zone

https://github.com/osTicket/osTicket/blob/develop/include/mysqli.php#L119

The existing function db_timezone() also reads global.system_time_zone instead of global.time_zone so I'm fairly sure that they should match.

For an RDS database where you use Amazon's settings (ie. a parameter group) to change the timezone, here is what is set when connecting without specifying a timezone for the connection:

SELECT @@global.system_time_zone, @@global.time_zone, @@session.time_zone
UTC US/Central US/Central

US/Central being the configuration option I have set. So, global.time_zone and session.time_zone are identical and the suggested pull request didn't change anything.

I have briefly tested the patch using global.system_time_zone. It solves the issue that we initially noticed with ticket locks. I know our support staff typically uses osTicket mostly in the morning so it may be a few days before they notice any issues. I'll update if I hear of any.

[JediKev]

@mattkolb Updated to match your findings. Thanks again for the report!

[protich]

@mattkolb - We ended up deciding to use SYSTEM to avoid potential timezone validation issues - can you test d8adf8508f9c407158d4a0ee10651a6fd8d3e846 and let us know.

[mattkolb]

That appears to work as well.