applier time value can be negative

[Seppo Jaakola]

Following has been observed in processlists:

mysql> show processlist;

+--------+-------------+-----------------+------+---------+--------+-----------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |

+--------+-------------+-----------------+------+---------+--------+-----------------------+------------------+
| 13 | system user | | NULL | Sleep | -204 | post SQL applying (0) | NULL |
| 14 | system user | | NULL | Sleep | 240432 | wsrep aborter idle | NULL |
| 15 | system user | | NULL | Sleep | -204 | post SQL applying (0) | NULL |
| 16 | system user | | NULL | Sleep | -204 | post SQL applying (0) | NULL |
| 17 | system user | | NULL | Sleep | -204 | post SQL applying (0) | NULL |

[shinguz (oli-sennhauser)]

Same to me on 5.5.20 MySQL Community Server (GPL), wsrep_23.4.r3713, wsrep_23.4.r3713

Interestingly it happens only on 2 Galera nodes out of 3 and in an other state:

show processlist;
+------+-------------+--------------------+------+---------+------+--------------------+----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+--------------------+------+---------+------+--------------------+----------------------------------------------------------+
| 1 | system user | | NULL | Sleep | 5283 | wsrep aborter idle | NULL |
| 2 | system user | | NULL | Query | 3 | committing 419838 | COMMIT |
| 1166 | root | localhost | NULL | Query | 0 | sleeping | show processlist |
| 1167 | app | 192.168.56.1:40884 | test | Query | 0 | query end | INSERT INTO test values (NULL, "Test data insert", NULL) |
| 2022 | app | 192.168.56.1:43648 | test | Query | 0 | query end | INSERT INTO test values (NULL, "Test data insert", NULL) |
| 6557 | app | 192.168.56.1:47964 | test | Query | 0 | query end | insert into test values (NULL, 'Test data insert', NULL) |
+------+-------------+--------------------+------+---------+------+--------------------+----------------------------------------------------------+

+------+-------------+--------------------+------+---------+------+--------------------------+----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+--------------------+------+---------+------+--------------------------+----------------------------------------------------------+
| 1 | system user | | NULL | Sleep | -8 | applied write set 420218 | NULL |
| 2 | system user | | NULL | Sleep | 5234 | wsrep aborter idle | NULL |
| 1373 | root | localhost | NULL | Query | 0 | sleeping | show processlist |
| 1374 | app | 192.168.56.1:44662 | test | Query | 0 | query end | INSERT INTO test values (NULL, "Test data insert", NULL) |
| 7091 | app | 192.168.56.1:52785 | test | Query | 0 | query end | insert into test values (NULL, 'Test data insert', NULL) |
+------+-------------+--------------------+------+---------+------+--------------------------+----------------------------------------------------------+

+------+-------------+--------------------+------+---------+------+--------------------------+----------------------------------------------------...

[Alex Yurchenko (ayurchen)]

IIRC, this is due to poor time sychronization between nodes and is not wsrep-related at all. We probably should invalidate this bug. NTP to the rescue.

[Przemek (pmalkowski)]

When all the nodes are in sync in terms of system time, this never happens. Also a situation where some nodes are in different timezone then others works just well and applier times are never negative, so time zone differences are handled correctly.
Only when there is time synchronization issue between the nodes, applier threads may report negative or too advance time since last event was applied, depending on actual time difference.
In my opinion there is nothing wrong in wsrep as it must rely on system clock. It is enough to use ntp daemon or ntpdate to deal with this.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1194