pt-heartbeat check-read-only option does not prevent creates or inserts

[monty solomon]

pt-heartbeat 2.2.8
Server version: 5.5.35-33.0-log Percona Server (GPL), Release rel33.0, Revision 611
CentOS release 6.5 (Final)
Linux 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

# cat /etc/percona-toolkit/pt-heartbeat.conf
utc

With create-table option

# pt-heartbeat -uSUSR_Heartbeat --ask-pass --database heartbeat --table heartbeat --update --create-table --daemonize --stop --check-read-only
Successfully created file /tmp/pt-heartbeat-sentinel
Enter password:
DBD::mysql::db do failed: The MySQL server is running with the --read-only option so it cannot execute this statement [for Statement " CREATE TABLE IF NOT EXISTS `heartbeat`.`heartbeat` (
    ts varchar(26) NOT NULL,
    server_id int unsigned NOT NULL PRIMARY KEY,
    file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
    position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
    relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
    exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
  );"] at /usr/bin/pt-heartbeat line 4905.

Without create-table option

# pt-heartbeat -uSUSR_Heartbeat --ask-pass --database heartbeat --table heartbeat --update --daemonize --stop --check-read-only
Successfully created file /tmp/pt-heartbeat-sentinel
Enter password:
DBD::mysql::db do failed: The MySQL server is running with the --read-only option so it cannot execute this statement [for Statement "INSERT INTO `heartbeat`.`heartbeat` (server_id, ts) VALUES ('3133137489', UTC_TIMESTAMP())"] at /usr/bin/pt-heartbeat line 5007, <STDIN> line 1.

[Frank Cizmich (frank-cizmich)]

Hello Monty,

The check-read-only option is there to allow pt-heartbeat to continue running (waiting) if the read_only restriction is temporarily set and later removed.
If pt-heartbeat is initiated while read_only is set AND the heartbeat table isn't already created and initialized AND the user hasn't super privilege, it can't do it's job and should not begin to run.
Maybe clearer error messages, and perhaps documentation of this behavior is in order.

Regards,
Frank

[monty solomon (monty+launchpad)]

I disagree with the statement "it should not begin to run."

I have a cloud infrastructure where I want to install the pt-heartbeat daemon on every mysql server. The daemon should run without failing or generating error messages when it is running on a read-only slave and the check-read-only option is set.

I want pt-heartbeat to work exactly as specified in the documentation for that option - "Check if the server has read_only enabled; If it does, the tool skips doing any inserts."

[Frank Cizmich (frank-cizmich)]

I've reopened the issue to keep it in the discussion.

[monty solomon (monty+launchpad)]

Please fix pt-heartbeat to work as documented.

--check-read-only
Check if the server has read_only enabled; If it does, the tool skips doing any inserts.

[Frank Cizmich (frank-cizmich)]

Now if --check-read-only is given, the tool waits for server "read_only" status to be disabled before starting to run. ( exists if run-time expires, or sentinel file is created )

[Brian Lee (chinchy)]

This seems like it's still an issue for me.

pt-heartbeat --version
pt-heartbeat 2.2.17

Server version: 5.5.48-37.8-log Percona Server (GPL), Release 37.8, Revision c8ae1b6

Linux kernel 2.6.32-504.23.4.el6.x86_64

Target Server:
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.04 sec)

mysql> select * From heartbeat;
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
| ts | server_id | file | position | relay_master_log_file | exec_master_log_pos |
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
| 2016-04-11T23:26:02.003620 | 3001 | percona-01-bin.000231 | 23389229 | NULL | NULL |
| 2016-04-11T23:26:02.002950 | 3011 | percona-11-bin.000202 | 23818867 | NULL | NULL |
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
2 rows in set (0.03 sec)

Execute the pt-heartbeat command:

$ sudo pt-heartbeat h=percona-3.iad,P=3306,u=toolkit --check-read-only --update --interval 1.0 --database heartbeat --table heartbeat --ask-pass --pid /var/lib/mysql/pt-heartbeat.pid
Enter password:
Overwriting PID file /var/lib/mysql/pt-heartbeat.pid because the PID that it contains, 1416, is not running at /usr/bin/pt-heartbeat line 3095.
Overwriting PID file /var/lib/mysql/pt-heartbeat.pid because the PID that it contains, 1416, is not running at /usr/bin/pt-heartbeat line 3095.

Check database:
mysql> select * From heartbeat;
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
| ts | server_id | file | position | relay_master_log_file | exec_master_log_pos |
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
| 2016-04-11T23:29:00.003060 | 3001 | percona-01-bin.000231 | 23448147 | NULL | NULL |
| 2016-04-11T23:27:54.002420 | 3003 | percona-03-bin.000230 | 52568 | NULL | NULL |
| 2016-04-11T23:28:59.003110 | 3011 | percona-11-bin.000202 | 23877454 | NULL | NULL |
+----------------------------+-----------+--------------------------+----------+-----------------------+---------------------+
3 rows in set (0.03 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.03 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

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