bad fractional math in bash

Hi Matthew,

I am not sure whether the checking against fraction of seconds make any sense.
Why do you need to check against -w 6.1 and 6 is not OK?

About why it returns "OK -0.000700 seconds" on the first test - I am not able to reproduce that (fractional result).
It appears to be possible if the actual query includes NOW() but it's not.

Can you please show me the output from your host on these queries?

SELECT MAX(UNIX_TIMESTAMP(UTC_TIMESTAMP) - UNIX_TIMESTAMP(ts)) AS delay from heartbeat where server_id=104129;
SELECT MAX(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts)) AS delay from heartbeat where server_id=104129;

Also what's the version of pt-heartbeat in use on the master?

Thanks,
Roman

Also please show the actual value:
SELECT MAX(ts) from heartbeat where server_id=104129;

mysql> select * from heartbeat;
+----------------------------+-----------+--------------------+----------+-----------------------+---------------------+
| ts | server_id | file | position | relay_master_log_file | exec_master_log_pos |
+----------------------------+-----------+--------------------+----------+-----------------------+---------------------+
| 2013-05-03 17:23:15 | 1 | NULL | NULL | NULL | NULL |
| 2013-11-26T14:39:51.001960 | 101 | centos6-bin.000019 | 4711312 | NULL | NULL |
+----------------------------+-----------+--------------------+----------+-----------------------+---------------------+
2 rows in set (0.00 sec)

Related http://www.mysqlperformanceblog.com/2013/07/29/pt-heartbeat-2-1-your-troubles-are-over/

Unfortunately, I am still not able to reproduce the problem.

Hey Roman,
My apologies but I no longer have access to the server that originally had this issue. So I can't check the versions of everything.

To answer one of your questions, I would never test against a fractional delay. That was simply to test my patch. I'm more concerned about the "bash error illegal number" and possibly producing a false positive.

My patch allows pmp-check-mysql-replication-delay to work with both whole integers and fractions. I'd rather cover all bases and be safe than sorry. My $0.02.

If I ever get access to that machine again, I'll check things out and re-open this bug if necessary.

Thank you Matthew. Please feel free to re-open the bug if you have more details.

We have this problem on a few of our hosts; UNIX_TIMESTAMP(ts) is returning fractional values:

$ ./pmp-check-mysql-replication-delay --version
Percona Monitoring Plugins pmp-check-mysql-replication-delay 1.1.3

also pt-heartbeat on the master:
$ /usr/bin/pt-heartbeat --version
pt-heartbeat 2.2.6

mysql> SELECT UTC_TIMESTAMP, UNIX_TIMESTAMP(UTC_TIMESTAMP), ts, UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(UTC_TIMESTAMP) - UNIX_TIMESTAMP(ts) FROM heartbeat;
+---------------------+-------------------------------+----------------------------+--------------------+----------------------------------------------------+
| UTC_TIMESTAMP | UNIX_TIMESTAMP(UTC_TIMESTAMP) | ts | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(UTC_TIMESTAMP) - UNIX_TIMESTAMP(ts) |
+---------------------+-------------------------------+----------------------------+--------------------+----------------------------------------------------+
| 2014-05-12 23:25:03 | 1399937103 | 2014-05-12T23:25:03.000370 | 1399937103.000370 | -0.000370 |
| 2014-05-12 23:25:03 | 1399937103 | 2014-05-12T23:25:03.000410 | 1399937103.000410 | -0.000410 |
+---------------------+-------------------------------+----------------------------+--------------------+----------------------------------------------------+
2 rows in set (0.00 sec)

we're using
ii percona-nagios-plugins 1.1.3-1
ii percona-server-client-5.6 1:5.6.15-rel63.0-519.precise
ii percona-server-common-5.6 1:5.6.15-rel63.0-519.precise
ii percona-server-server-5.6 1:5.6.15-rel63.0-519.precise
ii percona-toolkit 2.2.6

Everything seems to work fine on the hosts that have mysql 5.5 and 5.1.

Is that because since 5.6.4, these functions now work with fractional values?
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

Another work around is to change the SQL query to wrap unix_timestamp through round to eliminate passing the fractional portion to bash. (see attachment)

Wrapped through round:

- SQL="SELECT MAX(${NOW_FUNC} - UNIX_TIMESTAMP(ts)) AS delay
+ SQL="SELECT MAX(${NOW_FUNC} - ROUND(UNIX_TIMESTAMP(ts))) AS delay