mysql> select * from tab_with_index where id=1 for update;
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 limited
to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

mysql> show variables like '%bin%format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

解决方法：
更改日志的格式
mysql> set global binlog_format='mixed';
Query OK, 0 rows affected (0.00 sec)
mysql> exit

mysql> show variables like '%bin%format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select * from tab_with_index where id=1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
2 rows in set (0.00 sec)

ITPub博客

Percona MySQL 5.6 语句加锁报错"ERROR 1665 (HY000): Cannot execute statement"

最新文章