Error
Error Code:
1670
MariaDB Error 1670: Replication Unsafe System Table Use
Description
This error indicates that a SQL statement is deemed unsafe for replication because it interacts with specific system tables (general_log, slow_query_log, or performance_schema). It typically occurs in a replicated environment, especially with statement-based replication, where operations on these server-specific tables could lead to inconsistencies between master and slave servers.
Error Message
The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves.
Known Causes
3 known causesDirect System Table Interaction
A SQL statement attempts to read from or write to MariaDB's general_log, slow_query_log, or performance_schema tables directly.
Statement-Based Replication (SBR)
Using statement-based replication (binlog_format=STATEMENT) where operations on system tables are inherently non-deterministic or prone to inconsistencies between master and slave servers.
Potential Master-Slave Divergence
The data in system tables like general_log, slow_query_log, or performance_schema is often server-specific and can naturally differ between master and slave servers, making direct replication of changes unsafe.
Solutions
3 solutions available1. Disable Logging for Replication easy
Temporarily disable logging features that are causing the replication issue.
1
Connect to your MariaDB master server using a client like `mysql`.
2
Check the current status of the general log, slow query log, and performance schema.
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'performance_schema';
3
If any of these are enabled, disable them. For example, to disable the general log:
SET GLOBAL general_log = 'OFF';
4
Repeat the `SET GLOBAL` command for `slow_query_log` and `performance_schema` if they are enabled and causing the issue.
SET GLOBAL slow_query_log = 'OFF';
SET GLOBAL performance_schema = OFF;
5
Re-run the statement that was failing on the master. Once successful, you can re-enable logging if necessary, but be mindful of the replication implications.
2. Configure Replication to Ignore System Tables medium
Instruct the slave to ignore statements that access specific system tables.
1
Connect to your MariaDB slave server using a client like `mysql`.
2
Stop the replication threads on the slave.
STOP SLAVE;
3
Configure the slave to ignore statements that write to or read from the general log, slow query log, or performance schema tables. This is done by adding the table names to the `slave_skip_errors` or `slave_exec_mode` configuration. A more direct approach for this specific error is to use `slave_skip_errors` with specific error codes, or by setting `slave_exec_mode` to `IDEMPOTENT` if appropriate for your scenario. For this particular error (1670), directly skipping the error is the most straightforward, but it requires careful monitoring.
SET GLOBAL slave_skip_errors = '1670';
4
Start the replication threads again.
START SLAVE;
5
Monitor replication status closely to ensure it's catching up and no other critical errors occur.
SHOW SLAVE STATUS\G
3. Ensure Consistent Configuration Across Servers medium
Verify that logging and performance schema settings are aligned between master and slave.
1
On both your MariaDB master and slave servers, connect using a client like `mysql`.
2
Retrieve the configuration for relevant logging and performance schema variables on the master.
SHOW GLOBAL VARIABLES LIKE 'general_log';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'performance_schema';
3
Retrieve the same configuration variables on the slave.
SHOW GLOBAL VARIABLES LIKE 'general_log';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'performance_schema';
4
If there are discrepancies (e.g., logging is enabled on the master but disabled on the slave, or vice-versa, and the statement involves those logs), adjust the configuration on the slave to match the master's intent for replication. For example, if the statement on the master is attempting to query the general log, and that log is enabled on the master, ensure that the slave is configured to handle such operations or that the feature is disabled on the master if it's not intended for replication.
# Example: If general_log is ON on master and you want it OFF on slave for replication:
SET GLOBAL general_log = 'OFF';
5
Alternatively, ensure that if these features are used on the master, they are also enabled on the slave in a compatible manner. This might involve setting `slave_exec_mode = 'IDEMPOTENT'` or carefully managing `slave_skip_errors` if you cannot achieve perfect parity.