导航：首页 > 数据库 >

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

发表于：2024-11-14 作者：千家信息网编辑

千家信息网最后更新 2024年11月14日，小编给大家分享一下Percona MySQL 5.6 语句加锁报错"ERROR 1665 (HY000): Cannot execute statement"怎么办，希望大家阅读完这篇文章之后都有所收

千家信息网最后更新 2024年11月14日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)