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

小编给大家分享一下Percona MySQL 5.6 语句加锁报错"ERROR 1665 (HY000): Cannot execute statement"怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

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)

看完了这篇文章，相信你对“Percona MySQL 5.6 语句加锁报错"ERROR 1665 (HY000): Cannot execute statement"怎么办”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>