Timestamp mismatch between MySQL and PHP

Question

Every time I log in to CiviCRM I get the following error message:

Timestamps reported by MySQL (eg "2015-11-06 13:44") and PHP (eg "2015-11-06 11:44" ) are mismatched.

I am using CiviCRM 4.6.10 and Drupal 7.41.

The host (CiviHosting) has said that the MySQL server timezone is set to "Europe/Sofia" and can't be changed.

I have set the PHP timezone via php.ini to match this:

date.timezone = "Europe/Sofia"

However, I am still getting this error message.

The date section of phpinfo shows the following:

date/time support   enabled
"Olson" Timezone Database Version   2013.3
Timezone Database   internal
Default timezone    Europe/London

Directive   Local Value Master Value
date.default_latitude   31.7667 31.7667
date.default_longitude  35.2333 35.2333
date.sunrise_zenith 90.583333   90.583333
date.sunset_zenith  90.583333   90.583333
date.timezone   Europe/Sofia    Europe/Sofia

(Note that the 'Default timezone' is different to the 'date.timezone'.)

Any idea how I can fix this?

Thanks for your help.

Answer 1

I have flagged a similar problem with Joomla as an issue a long time ago and submitted some code to solve it:

https://issues.civicrm.org/jira/browse/CRM-15656

The CiviCRM team reckon it will be fixed in 4.7.7.

Answer 2

check what's the timezone under /admin/config/regional/settings, drupal can overwrite the php default timezone

Answer 3

This is one possible solution.

https://beta.suresupport.com/faq/article-1568/en/setting_mysql_session_time_zone

Another is setting the 'timezone' property in php.ini or .htaccess file, or in civicrm.settings.php or drupal's settings.php

Answer 4

As an experiment, I edited your CRM/Utils/System/Base.php file and changed:

      $sql = "SET time_zone = '$timeZoneOffset'";

to

      $sql = "SET time_zone = 'Europe/London'";

and that seems to have fixed the problem. This is certainly not the ideal solution, but it does work.

Answer 5

I have discovered that if I change the timezone for my Drupal user account to 'Europe/Sofia' then I no longer get this error message.

This solves the problem, but not in a very satisfactory way as I'm based in the UK and would like the timezone for my user to reflect this.

It appears that CiviCRM creates this error message if your Drupal user account isn't in the same timezone as the database server.

I'm still not sure if this actually causes any real problems. See my related post: Does the 'timestamp mismatch' error cause any real problems?