Error
Error Code:
1666
MySQL Error 1666: Row Statement in Statement Log
Description
This error occurs when MySQL attempts to write an operation to the binary log using a row-based format, but the server's or session's `binlog_format` is currently set to `STATEMENT`. MySQL requires consistency in its binary log, and this format mismatch prevents the statement from being executed and logged.
Error Message
Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.
Known Causes
3 known causesExplicit Statement-Based Logging
The `binlog_format` system variable is explicitly configured to `STATEMENT` globally or for the current session, which conflicts with a row-based operation.
Using Incompatible SQL Features
The executed SQL statement (e.g., `LOAD DATA INFILE`, specific user-defined functions, or certain `INSERT ... ON DUPLICATE KEY UPDATE` operations) inherently requires row-based logging.
Replication Mode Requirements
A specific replication setup or older MySQL version might implicitly enforce a `binlog_format` of `STATEMENT` that is incompatible with the executed row-based operation.
Solutions
3 solutions available1. Change Binary Log Format to ROW or MIXED medium
Modify the binary log format to ROW or MIXED to allow row-based statements.
1
Connect to your MySQL server as a user with sufficient privileges (e.g., root).
2
Check the current binary log format.
SHOW VARIABLES LIKE 'binlog_format';3
If the output is 'STATEMENT', you need to change it. This requires modifying the MySQL configuration file (`my.cnf` or `my.ini`). Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or within the MySQL installation directory.
4
Edit the configuration file and change the `binlog_format` setting under the `[mysqld]` section. You can set it to `ROW` or `MIXED`.
[mysqld]
binlog_format = ROW
# or
# binlog_format = MIXED5
Save the configuration file and restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql6
After restarting, verify that the `binlog_format` has been updated.
SHOW VARIABLES LIKE 'binlog_format';2. Temporarily Disable Binary Logging easy
Disable binary logging for specific operations that trigger the error.
1
Connect to your MySQL server.
2
Temporarily disable binary logging for the current session.
SET SQL_LOG_BIN = 0;3
Execute the statement that was causing the error.
YOUR_STATEMENT_HERE;4
Re-enable binary logging for subsequent operations.
SET SQL_LOG_BIN = 1;3. Update Statements to be Compatible with STATEMENT Format advanced
Rewrite problematic statements to be compatible with the STATEMENT binary log format.
1
Identify the specific statement that is causing the error. This often involves looking at the MySQL error logs or the client application logs.
2
Analyze the statement to understand why it might be considered row-based by MySQL. This can include functions or constructs that are not deterministic or have side effects that are best logged at the row level.
3
Rewrite the statement to be more deterministic and less prone to issues with statement-based replication. For example, avoid using functions like `NOW()` or `UUID()` in `WHERE` clauses if possible, or ensure they are used in a way that doesn't lead to replication divergence.
Example: Instead of `DELETE FROM my_table WHERE created_at < NOW() - INTERVAL 1 DAY;` consider a more explicit approach if possible, or ensure the replication slave's clock is synchronized. If the statement involves subqueries or complex joins, simplify them if feasible.4
Test the rewritten statement thoroughly in a non-production environment to ensure it functions correctly and does not trigger the error.