mysqli fails to set session timezone on connection. Remain UTC. On RDS mysql instance

Question

I'm on AWS using a mysql RDS instance version 5.5.40-log.

I'm using PHP 5.5.12 and connecting with mysqli.

I've been trying to track down an sql bug in a script and found that despite setting the timezone on the connection to 'Australia/Sydney' it remains in UTC and the sql call to now() returns the wrong time. So my results are out.

Below is a simplified call I'm making which, despite the TZ change makes no difference to the result of now().

$db = new mysqli("axxxx.chxxxxx.ap-soxxxxt-2.rds.amazonaws.com", "xxxx", "xxxxx", "xxxx");
$db->query("SET timezone = 'Australia/Sydney'");

$res = $db->query("SELECT @@session.time_zone tz, now() now");


for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
$res->data_seek($row_no);
$row = $res->fetch_assoc();
echo " id = " . $row['tz'] . "\n";
echo " now = " . $row['now'] . "\n";
}

This results in:

id = UTC now = 2015-06-07 01:46:05

Actual time here is 11:46.

If i log onto the mysql database through mysqlworkbench and run the same code it works. So the problem seems to be that the session TZ is not getting set on the php mysqli connection and I'm not sure why?

I can see the mysql.time_zone_name table is populated similar to:

Africa/Abidjan 1 Africa/Abidjan 2 ...etc

EDIT

I create a new direct session in mysqlworkbench:

SELECT @@global.time_zone, @@session.time_zone; -- UTC UTC
SELECT now(); -- 2015-06-07 21:55:54 <<incorrect time

SET time_zone = 'Australia/Sydney';

SELECT @@global.time_zone, @@session.time_zone; -- UTC Australia/Sydney
SELECT now(); -- 2015-06-08 07:55:03 <<correct time

I used @@session.time_zone in my example as this is the variable updated when setting the time_zone system setting directly.

Replicating the same thing via mysqli and php does not update this and the now() function does not return the correct date. Hence something is missing.

possible duplicate of [How to set time zone of mysql?](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql) — umka, Jun 07 '15 at 05:50

score 1 · Answer 1 · answered Jun 07 '15 at 05:53

1

You're setting timezone variable, but selecting @@session.time_zone. The right way is to set @@session.time_zone variable:

mysql> SET @@session.time_zone='Australia/Sydney';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| Australia/Sydney    |
+---------------------+
1 row in set (0.00 sec)

answered Jun 07 '15 at 05:53

umka

1,655
1
12
18

The underlying fail was ignoring the return value of `$db->query()` which should have made the problem obvious. – Michael - sqlbot Jun 07 '15 at 16:55
I updated my question to clarify the example I used with @@session.time_zone. – sapatos Jun 07 '15 at 22:00
@Michael-sqlbot good point, I was using this from another example found on stackoverflow. I'll test the return value – sapatos Jun 07 '15 at 22:00

mysqli fails to set session timezone on connection. Remain UTC. On RDS mysql instance

1 Answers1