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
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.11, 8.0.12, 5.7.23
• OS: Any
• 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] MySQL Verification Team

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.