CODEWITHSUNDEEP
mysqlmariadb
zloctb
zloctb

Reputation: 11184

READ COMMITTED ERROR 1665 impossible to write to binary log

THis example works : 

MariaDB [test]> SET TRANSACTION  ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO test(id, value) VALUES (1, 'test'), (2, 'test 2');
Query OK, 2 rows affected (0.37 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM test;
+------+--------+
| id   | value  |
+------+--------+
|    1 | test   |
|    2 | test 2 |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [test]> COMMIT;

but when i changed transaction level :

MariaDB [test]> SET TRANSACTION  ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO test(id, value) VALUES (1, 'test'), (2, 'test 2');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log
since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine lim
ited to row-based logging. InnoDB is limited to row-logging when transaction iso
lation level is READ COMMITTED or READ UNCOMMITTED.
MariaDB [test]> SELECT * FROM test;
Empty set (0.00 sec)

MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

How to fix this problem ?

Upvotes: 0

Views: 2406

Answers (1)

Shadow
Shadow

Reputation: 34285

The error message is quite detailed in this case. According to mysql documentation on binlog_format:

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

So, you have to set the binlog_format to ROW:

SET SESSION binlog_format = 'ROW';

Upvotes: 1

Related Questions