# Percona MySQL Slave out of sync because pxc\_strict\_mode from XtraDB Cluster...

**URL:** <https://forums.percona.com/t/percona-mysql-slave-out-of-sync-because-pxc-strict-mode-from-xtradb-cluster/7188>\
**Category:** Other MySQL® Questions\
**Created:** [September 7, 2019, 9:11am UTC](https://forums.percona.com/t/percona-mysql-slave-out-of-sync-because-pxc-strict-mode-from-xtradb-cluster/7188 "2019-09-07T09:11:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jbiancot](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jbiancot/32/6030_2.png) [@jbiancot](https://forums.percona.com/u/jbiancot)\
**Post date:** [September 7, 2019, 9:11am UTC](https://forums.percona.com/t/percona-mysql-slave-out-of-sync-because-pxc-strict-mode-from-xtradb-cluster/7188/1 "2019-09-07T09:11:32Z")

</div>

Hi,

We have a Percona 5.7.26-29-57 XtraDB Cluster (4 Nodes), on one of the nodes we have it setup as a traditional MySQL master, e.g: log\_bin, etc.

We setup a standalone Percona MySQL server 5.7.26-29 as the Slave of that master, but it is getting out of sync because some “statements” that slave is receiving cannot be processed? like pxc\_strict\_mode??

Any thoughts? comments? help?

Thanks in advance.

from “show slave status”, the output:

Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 10…7  
Master\_User: slave\_user  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: server-bin.000055  
Read\_Master\_Log\_Pos: 846185777 \<— Getting increase  
Relay\_Log\_File: brm-sql5-relay-bin.000013  
Relay\_Log\_Pos: 399884790 \<— Not increasing…  
Relay\_Master\_Log\_File: server-bin.000055  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: No  
Replicate\_Do\_DB:  
Replicate\_Ignore\_DB:  
Replicate\_Do\_Table:  
Replicate\_Ignore\_Table:  
Replicate\_Wild\_Do\_Table:  
Replicate\_Wild\_Ignore\_Table:  
Last\_Errno: 1193  
Last\_Error: Error ‘Unknown system variable ‘pxc\_strict\_mode’’ on query. Default database: ‘…\_schema’. Query: 'CREATE DEFINER=…

PROCEDURE `sp_initialize_metadata_...`()  
BEGIN  
SET GLOBAL pxc\_strict\_mode = ‘DISABLED’;  
– Resolved cluster issue for ORDER BY clauses using DISTINCT  
SET SESSION sql\_mode = ‘STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE, ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ ENGINE\_SUBSTITUTION’;  
SET GLOBAL sql\_mode = ‘STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE, ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ ENGINE\_SUBSTITUTION’;

CALL …schema.sp\_create\_…  
CALL …schema.sp\_create…();

…  
Skip\_Counter: 0  
Exec\_Master\_Log\_Pos: 399884575 \<— Not increasing…  
Relay\_Log\_Space: 846186250 \<— Getting increase  
Until\_Condition: None

---

<div class="post-metadata">

**Author:** ![krunalbauskar](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@krunalbauskar](https://forums.percona.com/u/krunalbauskar)\
**Post date:** [September 9, 2019, 5:11am UTC](https://forums.percona.com/t/percona-mysql-slave-out-of-sync-because-pxc-strict-mode-from-xtradb-cluster/7188/2 "2019-09-09T05:11:50Z")

</div>

That’s right. pxc\_strict\_mode is PXC specific variable.  
If you set it directly through the terminal or my.cnf it will not replicate. SET COMMANDs are not replicated. Since it is part of SP it is being replicated.

1. You can try to move this setting outside SP.
2. It seems like flow is setting strict mode only for tenure of the SP. Ideally, we recommend setting it at the global level for complete tenure. For evaluation one can set it (non-default) but once validation passes it is better to set to ENFORCING.