Error
Error Code:
1663
MariaDB Error 1663: Unsafe Statement Binlog
Description
This error indicates that an SQL statement could not be written to the binary log because it's deemed 'unsafe' for statement-based replication. It typically occurs when the involved storage engine only supports statement-based logging, and the `BINLOG_FORMAT` is set to `MIXED`, preventing a fallback to row-based logging.
Error Message
Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. %s
Known Causes
3 known causesUnsafe SQL Statement
The executed SQL statement contains non-deterministic functions or operations that may not produce identical results when replayed on a replica using statement-based logging.
Storage Engine Limitation
The tables affected by the statement utilize a storage engine (e.g., MyISAM) that is restricted to statement-based binary logging, lacking support for row-based logging.
Mixed Binlog Format Configuration
The `BINLOG_FORMAT` is set to `MIXED`, which attempts to use statement-based logging but cannot switch to row-based logging for unsafe statements due to the storage engine's limitations.
Solutions
3 solutions available1. Change BINLOG_FORMAT to ROW medium
Reconfigure the binary log format to ROW to avoid unsafe statements.
1
Edit the MariaDB configuration file. This is typically `my.cnf` or `my.ini`.
2
Locate the `[mysqld]` section and change the `binlog_format` parameter from `MIXED` to `ROW`.
[mysqld]
binlog_format = ROW3
Save the configuration file and restart the MariaDB service.
4
Verify the change by connecting to MariaDB and running the following command:
SHOW VARIABLES LIKE 'binlog_format';2. Disable Binary Logging Temporarily easy
Temporarily disable binary logging to allow the statement to execute, then re-enable it.
1
Connect to your MariaDB server as a user with sufficient privileges (e.g., root).
2
Execute the following SQL command to disable binary logging.
SET GLOBAL sql_log_bin = 0;3
Execute your problematic SQL statement.
YOUR_UNSAFE_STATEMENT_HERE;4
Immediately re-enable binary logging.
SET GLOBAL sql_log_bin = 1;5
Note: This is a temporary solution. For a permanent fix, consider changing `binlog_format` or rewriting the statement.
3. Rewrite the Unsafe Statement advanced
Modify the SQL statement to be compatible with statement-based logging.
1
Identify the specific SQL statement that is causing the error. The error message often includes the statement or a reference to it.
2
Analyze the statement for constructs that are not safely replicable with statement-based logging. Common culprits include:
- Functions that rely on the current time or random numbers (e.g., `NOW()`, `RAND()`).
- Statements that modify a table based on a `LIMIT` clause without an `ORDER BY` clause.
- User-defined functions (UDFs) that have side effects or are not deterministic.3
Rewrite the statement to be deterministic or to avoid unsafe constructs. For example, if using `NOW()`, fetch the current timestamp once and use that value in your statement.
Example: Instead of `UPDATE my_table SET created_at = NOW() WHERE id = 1;`
Use:
`SET @current_time = NOW();
UPDATE my_table SET created_at = @current_time WHERE id = 1;`4
Test the rewritten statement thoroughly to ensure it produces the desired results and does not trigger the error.