DOC for wsrep_sst_donor_rejects_queries neess to be updated

[Marco Tusa]

I don't know if this is the expected behavior, but for me is unusual and it should be managed.

So scenario :

3 PXC node version 5.7.16-10-27.19-log

xtrabackup = percona-xtrabackup-2.4.5-Linux-x86_64

I have ONE node up and running, if I have the value of wsrep_sst_donor_rejects_queries=1 (ON) then the SST will fail with:

Error: failed to execute query FLUSH NO_WRITE_TO_BINLOG TABLES: WSREP has not yet prepared node for application use

Also if I set SST to be executed with no-backup-locks.

If I set wsrep_sst_donor_rejects_queries=0 (OFF)

All works good.

Removing --no-backup-locks but keeping wsrep_sst_donor_rejects_queries=0

SST complete successfully.

Comments?

Details:

MySQL version:

root@localhost:pm) [(none)]>\s
--------------
/opt/mysql_templates/PXC-56/bin/mysql Ver 14.14 Distrib 5.6.28-76.1, for Linux (x86_64) using 6.2

Connection id: 156
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.16-10-27.19-log Percona XtraDB Cluster binary (GPL) 5.7.16-27.19, Revision bec0879, wsrep_27.19
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /opt/mysql_instances/galerat2/mysql.sock
Uptime: 7 min 35 sec

Threads: 12 Questions: 3601 Slow queries: 0 Opens: 1017 Flush tables: 1 Open tables: 1010 Queries per second avg: 7.914

WSREP set to:

(root@localhost:pm) [(none)]>show global variables like 'wsrep_sst_donor_rejects_queries';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| wsrep_sst_donor_rejects_queries | ON |
+---------------------------------+-------+

WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.0.231 4444 (20170209 18:44:05.902)
WSREP_SST: [INFO] Evaluating xtrabackup --defaults-file=/opt/mysql_instances/galerat2/my.cnf --defaults-group=mysqld --no-backup-locks $tmpopts $INNOEXTRA $keyringbackupopt --backup --galera-info --binlog-info=ON --stream=$sfmt --target-dir=$itmpdir 2>${DATA}/innobackup.backup.log | socat -u std

170209 18:44:58 [01] Streaming ./North_America/Country.ibd
170209 18:44:58 [01] ...done
170209 18:44:58 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
Error: failed to execute query FLUSH NO_WRITE_TO_BINLOG TABLES: WSREP has not yet prepared node for application use

Please note that XTRABACKUP is run with --no-backup-locks option.

I change the setting for WSREP:

(root@localhost:pm) [(none)]>show global variables like 'wsrep_sst_donor_rejects_queries';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| wsrep_sst_donor_rejects_queries | OFF |
+---------------------------------+-------+

Backup operation succeed:
170209 18:51:10 [00] ...done
170209 18:51:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '605334032443'
xtrabackup: Stopping log copying thread.
.170209 18:51:10 >> log scanned up to (605334032452)

170209 18:51:10 Executing UNLOCK TABLES
170209 18:51:10 All tables unlocked
170209 18:51:10 [00] Streaming ib_buffer_pool to <STDOUT>
170209 18:51:10 [00] ...done
170209 18:51:10 Backup created in directory '/tmp/tmp.nDtlv5Mj7C'
MySQL binlog position: filename 'binlog.000001', position '154'
170209 18:51:10 [00] Streaming backup-my.cnf
170209 18:51:10 [00] ...done
170209 18:51:10 [00] Streaming xtrabackup_info
170209 18:51:10 [00] ...done
xtrabackup: Transaction log of lsn (605334032443) to (605334032452) was copied.
170209 18:51:10 completed OK!

Remove --no-backup-locks and keep wsrep_sst_donor_rejects_queries=0

WSREP_SST: [INFO] Evaluating xtrabackup --defaults-file=/opt/mysql_instances/galerat2/my.cnf --defaults-group=mysqld $tmpopts $INNOEXTRA $keyringbackupopt --backup --galera-info --binlog-info=ON --stream=$sfmt --target-dir=$itmpdir 2>${DATA}/innobackup.backup.log | socat -u stdio TCP:192.168.0.

170209 19:12:42 [00] ...done
xtrabackup: Transaction log of lsn (605334032443) to (605334032459) was copied.
170209 19:12:42 completed OK!

[Krunal Bauskar (krunal-bauskar)]

Confusion came from the point that our document is bit stale on this front.

I found a clear documentation in codership and I am attaching the same.
It clarifies all the details including limitations.

http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-sst-donor-rejects-queries

<snippet>
wsrep_sst_donor_rejects_queries

Defines whether the node rejects blocking client sessions on a node when it is serving as a donor in a blocking state transfer method, such as mysqldump and rsync.

Command-line Format --wsrep-sst-donor-rejects-queries
System Variable Name: wsrep_sst_donor_rejects_queries
Variable Scope: Global
Dynamic Variable:
Permitted Values Type: Boolean
Default Value: OFF
Support Introduced: 1
This parameter determines whether the node rejects blocking client sessions while it is sending state transfers using methods that block it as the donor. In these situations, all queries return the error ER_UNKNOWN_COM_ERROR, that is they respond with Unknown command, just like the joining node does.

Given that a State Snapshot Transfer is scriptable, there is no way to tell whether the requested method is blocking or not. You may also want to avoid querying the donor even with non-blocking state transfers. As a result, when this parameter is enabled the donor node rejects queries regardless the state transfer and even if the initial request concerned a blocking-only transfer, (meaning, it also rejects during xtrabackup).

Note Warning: The mysqldump state transfer method does not work with this setting, given that mysqldump runs queries on the donor and there is no way to differentiate its session from the regular client session.

</snippet>

[Shahriyar Rzayev (rzayev-sehriyar)]

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