MySQL Bugs: #96774: The timer_start in performance_schema is much smaller than mysql' uptime

Bug #96774	The timer_start in performance_schema is much smaller than mysql' uptime
Submitted:	6 Sep 2019 7:42	Modified:	6 Sep 2019 12:26
Reporter:	ANG CHENG	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.23,5.7.21	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	performance_schema, statement, time

Description:
MySQL version:5.7.21-enterprise-commercial-advanced-log MySQL Enterprise Server
OS:CentOS release 6.7 (Final)

I need to monitor the mysql executing statement from  performance_schema.events_statements_current, and use the timer_strat column and  MySQL' uptime to calculate the approximate start_time. 

According to the official documentation:
The timer baseline (“time zero”) occurs at Performance Schema initialization during server startup. TIMER_START and TIMER_END values in events represent picoseconds since the baseline. 

So, the uptime should be similar to (timer_start*10e-13).

mysql> select round(timer_start*10e-13) P_S_time,now() from performance_schema.events_statements_current order by timer_start desc limit 1;
+----------+---------------------+
| P_S_time | now()               |
+----------+---------------------+
|  5393137 | 2019-09-06 15:36:37 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE uptime,now() from performance_schema.GLOBAL_STATUS where variable_name='Uptime';
+----------+---------------------+
| uptime   | now()               |
+----------+---------------------+
| 23839881 | 2019-09-06 15:36:38 |
+----------+---------------------+
1 row in set (0.00 sec)

you see, P_S_time is much smaller than the uptime.

I cannot repeat this bug, but I have found that this problem has occurred in two databases.One is 5.6.23, another is 5.7.21

How to repeat:
I don't know how to repeat.

I know that restarting MySQL server can solve this problem, but I don't know if it will appear again later.

Hi Mr. CHENG,

Thank you for your bug report.

I have tried for one hour to repeat what you are reporting, without any success. I have restarted MySQL 5.7.27 server several times, ran number of different statements. No help.

I always get very, very similar outputs like this one:

+--------+---------------------+
| uptime | now()               |
+--------+---------------------+
| 284    | 2019-09-06 14:29:18 |
+--------+---------------------+

+----------+---------------------+
| P_S_time | now()               |
+----------+---------------------+
|      285 | 2019-09-06 14:29:21 |
+----------+---------------------+

We truly can't do anything until we have a fully repeatable test case, the one that will show the bug every time that we run that test, in the latest available release of 5.7 version.

Can't repeat.

Hi Mr. Milivojevic,

Thank you for your reply.

I have only found a few MySQL servers which have this problem, in hundreds of database instances. 

I want to know under what circumstances, the timer_start will be less than the uptime. Maybe I can find a solution instead of restarting the database.

I must confess that we have never encountered such a problem, nor have we ever had a report on problem like that. Hence, when you discover a repeatable test case, come back to us .....