Bug #5676 Time column in 'show processlist;' is not showing the correct value
Submitted: 20 Sep 2004 20:57 Modified: 5 Feb 2005 15:27
Reporter: Chad Truemper Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.20 OS:Linux (Linux, Fedora Core 2)
Assigned to: Guilhem Bichot

[20 Sep 2004 20:57] Chad Truemper
Description:
In a dual master setup (database1 and database2), the time reported in the time column by 'show processlist;' is extremely high for the SQL thread on database2 and hovers right around 4294967246.  The time reported by 'show processlist;' on database1 appears to be accurate.  Almost all queries and updates are being made to database1 and it is under heavy load, approx 500 queries/sec.  When database1 is under a light load, the time reported by 'show processlist;' on both systems is accurate.  

+----+-------------+-------------------------+----+-------------+------------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host                    | db | Command     | Time       | State                                                                 | Info             |
+----+-------------+-------------------------+----+-------------+------------+-----------------------------------------------------------------------+------------------+
| 1  | system user |                         |    | Connect     | 210182     | Waiting for master to send event                                      |                  |
| 2  | system user |                         |    | Connect     | 4294967246 | Has read all relay log; waiting for the I/O slave thread to update it |                  |
| 3  | repl        | db1.rightstuf.com:59207 |    | Binlog Dump | 210178     | Has sent all binlog to slave; waiting for binlog to be updated        |                  |
| 8  | rich        | localhost               |    | Query       | 0          |                                                                       | show processlist |
+----+-------------+-------------------------+----+-------------+------------+-----------------------------------------------------------------------+------------------+

How to repeat:
Set up replication in a dual-master configuration with high activity on one of the servers.
[5 Feb 2005 15:27] Jorge del Conde
Hi!

Can you can have a look at:
Q: How do I know how late a slave is compared to the master? In other 
words, how do I know the date of the last query replicated by the 
slave?
in the manual's "replication FAQ".

This behaviour is nothing we can fix, but can you upgrade to 4.1 and see what Seconds_Behind_Master says ?

Thanks
[15 Nov 2007 12:52] Stanisław Pitucha
Probably connected to reported bug:
My processlist on replication slave:
+----+-------------+----------------------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host                 | db   | Command | Time       | State                                                                 | Info             |
+----+-------------+----------------------+------+---------+------------+-----------------------------------------------------------------------+------------------+
|  1 | system user |                      | NULL | Connect |      41089 | Waiting for master to send event                                      | NULL             | 
|  2 | system user |                      | NULL | Connect | 4294967295 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             | 
|  6 | root        | xx.xxx.xxx.xxx:33257 | NULL | Query   |          0 | NULL                                                                  | show processlist | 
+----+-------------+----------------------+------+---------+------------+-----------------------------------------------------------------------+------------------+

That's when mysql is idle. Time value flips between 4294967295 (-1?), 0 and 1.
It's quad core 64bit system with 5.0.45-community version running on both master and slave. (official rpm packages)