Bug #91837 IS.innodb_trx results filtered by trx_started depends on timezone
Submitted: 31 Jul 2018 4:35 Modified: 31 Jul 2018 5:59
Reporter: Nikolai Ikhalainen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.11, 8.0.12, 5.7.23 OS:Any
Assigned to: CPU Architecture:Any

[31 Jul 2018 4:35] Nikolai Ikhalainen
Description:
INFORMATION_SCHEMA.innodb_trx.trx_started contains "Transaction start time.".
Unfortunately it's not documented what it always stays in server's system timezone regardless to system/session value for time_zone variable.

Currently the query requesting transactions started longer than 5 seconds ago require conversion:
select count(*) from information_schema.innodb_trx where convert_tz(trx_started, @@system_time_zone, @@session.time_zone) < date_sub(now(), interval 5 second);

Similar columns in performance schema (e.g. performance_schema.events_statements_summary_by_digest.last_seen) converting timestamps correctly to current timezone.

How to repeat:
docker run --rm -it -e MYSQL_ALLOW_EMPTY_PASSWORD=1 --name m8 mysql:latest
it starts mysql with system timezone UTC.
in one session (docker exec -it m8 mysql) start long running transaction:
begin;select * from mysql.innodb_table_stats LIMIT 1;

in second session:
set session time_zone='-09:00';
mysql> select count(*) from information_schema.innodb_trx where trx_started < date_sub(now(), interval 5 second);
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Expected results the same as before running time_zone:
mysql> select count(*) from information_schema.innodb_trx where trx_started < date_sub(now(), interval 5 second);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
[31 Jul 2018 5:59] Umesh Shastry
Hello Nikolai,

Thank you for the report!

Thanks,
Umesh
[31 Jul 2018 12:43] Peter Zaitsev
I want to point out this behavior seems to come from the fact this column is DATETIME type:

| trx_started                | datetime            | NO   |     | 0000-00-00 00:                                      00:00 |    

Why is DATETIME used here while in other cases (like mentioned Performance Schema table)  TIMESTAMP is used is unclear.  Looks like mistake to me.