MySQL raises ER_PREVENTS_VARIABLE_WITHOUT_RBR (SQLSTATE HY000, error 3099) when you try to set transaction_write_set_extraction or other row-based settings while binary logging is disabled or binlog_format is not ROW.
ER_PREVENTS_VARIABLE_WITHOUT_RBR occurs in MySQL when you change transaction_write_set_extraction or similar variables while binary logging is off or binlog_format is STATEMENT/MIXED. Enable the binary log and set binlog_format='ROW', or disable the write-set variable, to clear the error.
ER_PREVENTS_VARIABLE_WITHOUT_RBR
MySQL throws ER_PREVENTS_VARIABLE_WITHOUT_RBR when you try to enable features that depend on row-based binary logging while the server is not running with binlog_format=ROW. The most common trigger is setting transaction_write_set_extraction to a non-OFF value.
The server refuses the change to prevent replication inconsistencies. Row-based events are required so write sets can be extracted and conflict detection remains reliable in Group Replication and GTID setups.
The error appears if you run SET GLOBAL or SET PERSIST for transaction_write_set_extraction, binlog_transaction_dependency_tracking, or similar variables when binary logging is disabled with --skip-log-bin or when binlog_format is MIXED or STATEMENT.
It also surfaces during server startup if configuration files request those variables without the proper binary log settings. Any attempt inside a restricted session, such as stored procedures, will fail the same way.
Fix the error by turning on the binary log and switching to row-based logging, then reissue the variable change. Alternatively, keep the binary log off but leave transaction_write_set_extraction=OFF.
If binary logging must stay disabled in production, add or modify the configuration so the conflicting variable is not set. This lets the server start without errors.
On replicas running Group Replication, DBAs often enable transaction_write_set_extraction on existing servers that still use STATEMENT logging. Switching binlog_format to ROW first resolves the conflict.
Cloud services that default to MIXED logging throw the error when an application issues SET SESSION transaction_write_set_extraction=XXHASH64. Adjust instance parameters or ask support to enable row-based replication.
Standardize on binlog_format=ROW across all MySQL 5.7.6+ servers that plan to use Group Replication, write-set extraction, or similar features. Document the dependency in your configuration management.
Use a modern SQL editor like Galaxy to store vetted startup scripts. Galaxy highlights server variables and can lint your scripts to ensure binlog_format is set correctly before deployment.
ER_BINLOG_UNSAFE_STATEMENT warns about unsafe statement logging and often precedes ER_PREVENTS_VARIABLE_WITHOUT_RBR. Switching to ROW format fixes both.
ER_CANT_SET_GTID_PURGED appears when GTID_PURGED is set without enabling GTID mode. As with the RBR error, ensure prerequisite settings are configured first.
When transaction_write_set_extraction=XXHASH64 or MURMUR32 is requested, MySQL demands row-based events. If binlog_format is STATEMENT or MIXED the server blocks the change.
Disabling the binary log removes the row-event stream entirely. Any variable that needs the stream will trigger ER_PREVENTS_VARIABLE_WITHOUT_RBR.
Placing transaction_write_set_extraction=XXHASH64 in my.cnf without matching binlog_format=ROW makes the instance fail to start, surfacing the error in the error log.
Applications or maintenance scripts that issue SET GLOBAL transaction_write_set_extraction during runtime cause the error if the server is still in STATEMENT logging mode.
Warns that the current statement cannot be safely replicated under STATEMENT logging. Switching to ROW often eliminates the warning.
Occurs when GTID_PURGED is set while GTID mode is OFF. Sequence of configuration changes matters as with ER_PREVENTS_VARIABLE_WITHOUT_RBR.
Raised when an operation requires the binary log but log_bin is disabled. Enabling the binlog resolves it.
No. The variable relies on row events in the binary log. MySQL blocks the change if binary logging is disabled.
No. MIXED still allows statement events. You must set binlog_format=ROW for complete compatibility.
Yes. Any MySQL instance that changes the variable without row logging will raise the error, regardless of its replication role.
Galaxy flags mismatched server variables in connection scripts and lets you share vetted my.cnf snippets, ensuring consistent row-based logging across environments.
.avif)
.avif){kind=logo}
