SST fails with steaming Repliaction due to 'binlog_format = STATEMENT'


we have found for us that streaming replication works well for our purposes. In fact, we need it for smooth operation with larger transactions.
I can turn this on at runtime with “set global wsrep_trx_fragment_size=1048576;” and it works well!

However, when I pack this the config file to persist it, SST/IST fails on restart:

2022-04-11T14:02:11.956184+02:00 0 [Note] [MY-000000] [WSREP-SST] Proceeding with SST…
2022-04-11T14:02:12.728515+02:00 0 [Note] [MY-000000] [WSREP-SST] removed ‘/var/lib/mysql-binlog/mysql-bin.009673’
2022-04-11T14:02:12.729333+02:00 0 [Note] [MY-000000] [WSREP-SST] removed ‘/var/lib/mysql-binlog/mysql-bin.009672’
2022-04-11T14:02:12.730239+02:00 0 [Note] [MY-000000] [WSREP-SST] …Waiting for SST streaming to complete!
2022-04-11T15:05:00.740375+02:00 0 [Note] [MY-000000] [Galera] 1.0 (db1-node3): State transfer to 0.0 (db1-node2) complete.
2022-04-11T15:05:00.781104+02:00 0 [Note] [MY-000000] [WSREP-SST] Preparing the backup at /var/lib/mysql//sst-xb-tmpdir
2022-04-11T15:05:54.623386+02:00 0 [Note] [MY-000000] [WSREP-SST] Moving the backup to /var/lib/mysql/
2022-04-11T15:05:55.122623+02:00 0 [Note] [MY-000000] [WSREP-SST] Running post-processing…
2022-04-11T15:05:55.129604+02:00 0 [Note] [MY-000000] [WSREP-SST] Skipping mysql_upgrade (sst): local version (8.0.25) == donor version (8.0.25)
2022-04-11T15:05:55.223228+02:00 0 [Note] [MY-000000] [WSREP-SST] Waiting for server instance to start… This may take some time
2022-04-11T15:06:02.944306+02:00 0 [Note] [MY-000000] [WSREP-SST] …post-processing done
2022-04-11T15:06:02.946201+02:00 0 [Note] [MY-000000] [WSREP-SST] Galera co-ords from recovery: d9546a12-98b8-11ec-bc1a-3f2cce0f42aa:388631595
2022-04-11T15:06:02.969118+02:00 3 [Note] [MY-000000] [Galera] Processing SST received
2022-04-11T15:06:02.970069+02:00 3 [Note] [MY-000000] [WSREP] Server status change joiner → initializing
2022-04-11T15:06:02.970916+02:00 3 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2022-04-11T15:06:02.972668+02:00 0 [Warning] [MY-013245] [Server] The SSL library function CRYPTO_set_mem_functions failed. This is typically caused by the SSL library already being used. As a result the SSL memory allocation will not be instrumented.
2022-04-11T15:06:02.979380+02:00 4 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-11T15:06:05.959433+02:00 4 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-11T15:06:06.078448+02:00 4 [Note] [MY-000000] [WSREP] wsrep_init_schema_and_SR (nil)
2022-04-11T15:06:06.088003+02:00 4 [System] [MY-000000] [WSREP] PXC upgrade completed successfully
2022-04-11T15:06:06.102714+02:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
> mysqld: Streaming replication not supported with binlog_format=STATEMENT
2022-04-11T15:06:06.119967+02:00 0 [ERROR] [MY-010971] [Server] Failed to persist resource group USR_default to Data Dictionary.
2022-04-11T15:06:06.121364+02:00 0 [ERROR] [MY-010944] [Server] Resource group post initialization failed.
2022-04-11T15:06:06.124232+02:00 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-11T15:06:06.124272+02:00 0 [Note] [MY-000000] [WSREP] Initiating SST cancellation
2022-04-11T15:06:06.124301+02:00 0 [Note] [MY-000000] [WSREP] Terminating SST process
2022-04-11T15:06:06.124335+02:00 0 [Note] [MY-000000] [WSREP] Signalling cancellation of the SST request.

Of course, binlog_format = ROW is set within the configuration file

mysql> show variables like ‘binlog_format’;
| Variable_name | Value |
| binlog_format | ROW |
1 row in set (0.00 sec)

Is this normal/desirable behavior and is there a solution for user problem?

I really appreciate your help!

1 Like

Perhaps there’s a bug with how the parameters are loaded/compared. Can you try setting this loose_ wsrep_trx_fragment_size and see if the value you want is still applied?

1 Like

Hi Matthew, sorry, I am not sure if I have understood you correctly. I set

loose_wsrep_trx_fragment_size = 1048576

in my.cnf instead of wsrep_trx_fragment_size.

is that what you meant?
Unfortunately, this leads to the same behavior.

1 Like

since we upgraded from 8.0.25 to 8.0.26. this problem does not seem to exist anymore :slight_smile:

1 Like