search

gboudreau / Greyhole

Greyhole uses Samba to create a storage pool of all your available hard drives, and allows you to create redundant copies of the files you store.
http://www.greyhole.net
GNU General Public License v3.0
263 stars 34 forks source link

greyhole timezone=-13:00 vs ubuntu 18 (mysql 5.7) #303

Closed jpwise9 closed 2 years ago

jpwise9 commented 2 years ago

Good morning from New Zealand - currently in NZDT timezone -13:00 as at 3am this morning.

Summary version - looks like there may have been a change some time in the past 12 months that impacted either the sql version check, or the timezone checks. No issues 12 months ago - but woke up this morning to 600 odd emails reporting: `PHP Fatal error: Uncaught Exception: SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: '-13:00' in /usr/bin/greyhole:730 Stack trace:

0 /usr/bin/greyhole(705): DB::execute('SET time_zone =...', Array)

1 /usr/bin/greyhole(8409): DB::connect(false)

2 /usr/bin/greyhole(8367): CommandLineHelper->getRunner()

3 /usr/bin/greyhole(8628): CommandLineHelper->processCommandLine()

4 {main}

thrown in /usr/bin/greyhole on line 730`

Identified cause - Ubuntu 18.04 LTS only bundled up to mysql-server-5.7 - timezone -13:00 was not supported until 8.0.13 Refer: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-variables greyhole is still attempting to set timezone -13:00 and repeatedly raising an error each time it fails

Workaround - add the mysql apt repo and upgrade to mysql-server-8 Official instructions at: https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/#repo-qg-apt-upgrading Example (shorter) read at: https://www.digitalocean.com/community/questions/upgrade-mysql-5-7-to-8-ubuntu-18-04?comment=153968 For my purposes I've retained legacy (5.7.x) authentication support when prompted during upgrading, and appears to have resolved the issue with greyhole no longer throwing an error.

Thx.

gboudreau commented 2 years ago

This change was made 8 months ago, in 0.5.11 Unless you never updated Greyhole since then, the most likely culprit would be a TZ change on your server, either at the OS level, or in MySQL.

The above code is only executed if the timezone of the system, and of MySQL, differs. It checks what time it is using time(), which returns the system's time (same you'd get issuing a date command in a shell), and checks what time MySQL thinks it is, using SELECT NOW(), and if the two ar more than 20 minutes apart, it will try to set the correct timezone in MySQL. That's what is causing the error you noticed.

The easiest fix would be to change either the system TZ, or MySQL TZ (using mysql.conf) so that both match.

I'll change the code to at least NOT crash in this case, and log a warning.

jpwise9 commented 2 years ago

Hi, thanks for the response. Looks like I'm running greyhole 0.15.18 - last updated 4-Aug-22 - no updates in apt repo when troubleshooting yesterday.

mysql is currently set to default timezone=SYSTEM I identified the issue after trying to similarly set the timezone manually to -13:00 based on the greyhole error - and running into the same error greyhole was having. As far as I can recall - the server has been set to NZDT timezone since it was set up, but I can't rule out that I've changed something along the way.

Below is the output since upgrading to mysql-server-8, although the timezone was also SYSTEM previously.

mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec)

mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2022-09-26 20:18:48 | +---------------------+ 1 row in set (0.00 sec)

timedatectl output is:

                   Local time: Mon 2022-09-26 20:19:12 NZDT
              Universal time: Mon 2022-09-26 07:19:12 UTC
                    RTC time: Mon 2022-09-26 07:19:12
                   Time zone: Pacific/Auckland (NZDT, +1300)
   System clock synchronized: yes

systemd-timesyncd.service active: yes RTC in local TZ: no

Re-reading my original notes though - NZDT is actually "+13:00", not "-13:00" as I originally wrote (pre-coffee), so I'm also a not sure how it calculated to a negative timezone offset to issue the command either? But after updating mysql to v8, timezone="-13:00" is a valid option, and as far as I can tell greyhole isn't repeatedly re-issuing the command, although I haven't turned on full sql logging to confirm fully.

Thx.