Bug #104405 show processlist return -1 in TIME field
Submitted: 24 Jul 2021 5:44 Modified: 26 Jul 2021 5:50
Reporter: yuxiang jiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.18, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[24 Jul 2021 5:44] yuxiang jiang
Description:
SQL command “show processlist;” return -1 in TIME field occasionally, while TIME field should not be negative. This will happen more frequently when instance has several connections.
The reason is that start time and end time are measured by different method.
In very short time,  (longlong) (now - thd_info->start_time) maybe negative.
    if (thd_info->start_time)
      protocol->store_long ((longlong) (now - thd_info->start_time));
    else
      protocol->store_null();

How to repeat:
We can use code below
void test_my_time_and_micro_time_now()
{
  ulonglong  start_utime;
  struct timeval start_time;
  time_t now;
  int counter = 0, trig_counter = 0;
  while (true)
  {
    start_utime= my_micro_time();
    my_micro_time_to_timeval(start_utime, &start_time);
    usleep(10);
    now= my_time(0);
    longlong diff= now - start_time.tv_sec;
    if (diff < 0)
    {
      trig_counter++;
    }
    counter++;
    if (trig_counter > 100)
      break;
  }
  if (trig_counter)
  {
    sql_print_error("my_time function and my_micro_time_to_timeval function "
                    "has fraction diff trigger %d in %d",
                    trig_counter, counter);
  }
}

Suggested fix:
Return 0 if (longlong) (now - thd_info->start_time)  is negative.
[24 Jul 2021 16:43] yuxiang jiang
bugfix for this defect

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-bugfix-issue-422-show-processlist-return-invalid-val.patch (application/octet-stream, text), 1.58 KiB.

[26 Jul 2021 5:50] MySQL Verification Team
Hello yuxiang jiang,

Thank you for the report and contribution.

regards,
Umesh