Percona MySQL 5.6 語句加鎖報錯"ERROR 1665 (HY000): Cannot execute statement"

feelpurple發表於2016-09-30
MySqlError
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章