Bug #97319 sys schema lock wait view not timezone-aware

Submitted: 21 Oct 2019 22:19 Modified: 22 Oct 2019 5:53
Reporter: Trey Raymond Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:5.7.26, 8.0.18, 5.7.28 OS:Any
Assigned to: CPU Architecture:Any

[21 Oct 2019 22:19] Trey Raymond
Description:
sys schema lock wait view reports invalid times if db time zone is not utc, because now() and the info schema info are in different time zones

mysql> select * from x$innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-10-21 22:14:29
                    wait_age: -06:59:23
               wait_age_secs: -25163
                locked_table: `ops`.`ops_settings`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 27820096424
         waiting_trx_started: 2019-10-21 22:14:29
             waiting_trx_age: -06:59:23
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 65694006
               waiting_query: select * from ops_settings for update
             waiting_lock_id: 27820096424:177700:3:3
           waiting_lock_mode: X
             blocking_trx_id: 27820095928
                blocking_pid: 65693981
              blocking_query: NULL
            blocking_lock_id: 27820095928:177700:3:3
          blocking_lock_mode: X
        blocking_trx_started: 2019-10-21 22:14:24
            blocking_trx_age: -06:59:18
    blocking_trx_rows_locked: 3
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 65693981
sql_kill_blocking_connection: KILL 65693981
1 row in set, 3 warnings (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| US/Pacific  |
+-------------+
1 row in set (0.00 sec)

How to repeat:
select a row for update in two separate transactions, look at the view results

Suggested fix:
use convert_tz, we have many monitoring queries of this form, one example (not this exact view but shows the pattern):

select unix_timestamp()-unix_timestamp(ifnull(convert_tz(min(t.trx_started),'UTC',if(\@\@global.time_zone='SYSTEM',\@\@global.system_time_zone,\@\@global.time_zone)),now())) from information_schema.INNODB_TRX t where t.trx_rows_locked>0
[22 Oct 2019 5:53] MySQL Verification Team
Hello Trey Raymond,

Thank you for the report and feedback.
Verified as described with 5.7.28 and 8.0.18 builds.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.