Hi All,

I just wanted to share my findings on an issue I have been experiencing after upgrading from 1.12.x to 1.17.2.

Server Information
osTicket Version v1.17.2 (8fbc7ee) — Up to date
Web Server Software Apache
MySQL Version 10.4.26
PHP Version 8.1.13

PHP Settings
cgi.fix_pathinfo "1" is recommended if AJAX is not working
date.timezone Europe/Amsterdam

The upgrade went without any problems. After the upgrade, Agents were not able to acquire any locks on tickets (or so the error messages kept indicating:

(e.g. when trying to resolve/close a ticket)

After digging around the internet, this appears to be a time-zoning issue, but I could not find a solution.
I checked the "Default Time Zone" setting in OsTicket to be Europe/Amsterdam, i.e. the same as the PHP timezone .
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone also both returned Europe/Amsterdam.

I observed however, that entries in the ost_lock table were always 1 hour earlier than my current time.
With 'Collision Avoidance Duration' set to 3 minutes, these locks seemed to be expired already at the time of creation.

For me, setting the 'Collision Avoidance Duration' to 63, fixes the problem (for now).

The cause, I think (in my case):
the MySQL system_time_zone is different from the GLOBAL, and SESSION timezone
In PHPMyAdmin, I ran:
select @@system_time_zone, @@GLOBAL.time_zone, @@SESSION.time_zone, returns:

@@system_time_zone 	@@GLOBAL.time_zone 	@@SESSION.time_zone 	
UTC 	Europe/Amsterdam 	Europe/Amsterdam

UTC, and 'Europe/Amsterdam' differ by 1 hour, so my guess is that the os_lock time entries are compared somewhere with different timezone settings. I wasn't able to pinpoint this in the php files though (I am not a php developer)

Hopefully this analysis is helpful in finding the underlying issue, and fixing it.

Kind regards,
Vincent

Are you able to set the MySQL system time zone? This is the result of your statement on my system:

AWST is GMT+8 (UTC+8)

    Boephe
    Thank you for your reply.

    I can't set the system_time_zone, as I am on a webhosting environment I don't own. I also don't have access to the my.cnf configuration.

    Kind regards,
    Vincent

        KevinTheJedi

        Thanks for your reply,

        It should, but wouldn't my findings suggest that this is somehow not the case when it comes to checking for lock expiration? Or am I missing something?
        I double checked, and I also have that code in mysqli.php, yet still have the issue.

        Kind regards,
        Vincent

          Just to add, even though, I can set the GLOBAL/SESSION time_zone manually (directly via SQL, I am assuming), those values will be reset on a system restart (over which I have no control(!)). So the issue would remain.

          Kind regards,
          Vincent

            6 days later

            Hi Kevin,

            Sorry for my delayed reply. I am aware of the differences in timezone variables.
            I guess I am (genuinely) having trouble understanding what you are trying to say?

            Is it the case that (currently) OsTicket 17.x does not support different timezone values for the 3 MySQL variables, and/or PHP timezone? and that I should try and change MySQL timezone settings in order to get this working properly again?

            If so, what are the recommended timezone settings? I did not see the upgrader complaining about it, nor did I see it in the docs (but that could just be me)

            Shouldn't this be qualifies as a bug? Or is this by design?

            Kind regards,
            Vincent

              vbonnet

              We use different timezones for different reasons.

              The database timezone is used to convert the dates/times selected in the UI when saving to the database. This is typically UTC or something that's global and easy to convert to subsequent user/agent timezones.

              Then, we use the PHP timezone/Agent timezone/System Timezone to convert the database times to display the time in the User's/Agent's timezone and to convert the times selected in the UI so they can see the correct time in their timezone.

              Cheers.

                  KevinTheJedi

                  I get that. My point is that something is going wrong with timezone conversions (in mysql/php) wrt ticket locks, and I haven't heard a suggestion on how to properly fix it. (either via setting mysql variables, or otherwise).

                  Will this be fixed in a later release? Or are there any settings I can set to prevent this from happening?

                  Kind regards,
                  Vincent

                      vbonnet

                      I would try setting the MySQL timezones to UTC and retest. If you are unable to do so then you will need to set the helpdesk timezone to something else to offset the difference.

                      Cheers.

                        Write a Reply...