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

深碍√TFBOYSˉ_ 2024-04-18 00:08 57阅读 0赞

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博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2125783/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2125783/