Percona MySQL Slave out of sync because pxc_strict_mode from XtraDB Cluster...

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.