5

I'm having an issue where MySQL is reporting the time as six minutes later than the system time. I have verified all my timezone settings are correct but don't know how to troubleshoot the six minute difference:

mysql> SELECT NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2015-04-24 16:05:24 | 2015-04-24 | 16:05:24  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)


[root@server]# clock
Fri 24 Apr 2015 03:59:04 PM UTC  -0.860391 seconds

Thank you!

3 Answers 3

2

I hate to state the obvious, but is MySQL running on the same system as the bash instance?

Are you SSHing into the server or is it a local Linux desktop? How are you connecting to MySQL?

I recently had to troubleshoot this exact issue, thus finding this post. In our case the dev was using an alias to connect to MySQL, and he did not remember that MySQL was actually running on a different host than he was SSHing into (also with an alias).

1

I also encountered the same issue.

In my case, the server's time zone is set to IST but mysql is referring to UTC timezone.

This issue occurred as I haven't restarted mysql after changing the server's time zone.

service mysql restart will work!

0

mysqld tries to determine your timezone at startup. You can see these values with:

SELECT @@global.time_zone, @@session.time_zone;

You can set the timezone per-session or globally at runtime:

SET GLOBAL time_zone = timezone;

You can, and it seems you should, set it in the config file:

default-time-zone='+0:00'

The documentation says these are valid values for default-time-zone:

  1. The value 'SYSTEM' indicates that the time zone should be the same as the system time zone.

  2. The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.

  3. The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

Finally, the connecting client to mysql is guessing and providing the timezone to the session, and thus affecting some date/time functions, which can cause headaches.

3
  • 1
    Thank you for the reply. My timezones are already set correctly. I would expect, if I had the timezones wrong, the time difference would be one or more hours. In my case, the time is only off by a few minutes.
    – Jason
    Apr 24, 2015 at 17:44
  • Ah, what is the difference between your OS time and your server's BIOS time? I'm unfamiliar with the clock command. Apr 24, 2015 at 17:57
  • Could you run the following (not as root) date +"Timezone:%Z Offset:%z" && date && mysql -u root -p --batch -e 'SELECT @@global.time_zone, @@session.time_zone, utc_timestamp()\G' && date Apr 24, 2015 at 18:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .