【问题记录】ERROR 1665 (HY000): Cannot execute statement

刚创建了个表，想执行insert插入1条记录时，报这个错，不能插入：

mysql> desc test
    -> ;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 

mysql> insert into test(id,name) values (1,'yuanqk');
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>

　　MOS了一下，大概意思是说mysql从5.1开始，在InnoDB引擎中，使用READ COMMITTED或READ UNCOMMITTED隔离级别，就不允许使用基于statement的binlog，不懂，那先查查自己环境的隔离级别和binlog格式吧。

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |            <===binlog格式是statement
+---------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |                      <===隔离级别是read committed
+----------------+
1 row in set (0.00 sec)

mysql> 

这就是我想不通的地方，既然从5.1开始就有这个标准了，为啥我安装的5.5的版本里面，这两个参数还是互斥的？不明白，先按官方给的方法解决吧。

mysql> SET GLOBAL binlog_format = MIXED;
Query OK, 0 rows affected (0.00 sec)

mysql> 

　　该命令只针对新的连接生效，因此，需要重新连接才行，我也测试了，旧的连接缺失不行。。。官方还是靠谱的。。。

mysql> insert into test(id,name) values (1,'yuanqk');
Query OK, 1 row affected (0.00 sec)

mysql> 

　　

下面附上MOS的文章：

MySQL Server: "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"; Error 1665 (HY000) (文档 ID 1433907.1)

APPLIES TO:
MySQL Server - Version 5.1 and later
Information in this document applies to any platform.
SYMPTOMS
When attempting to execute a statement, the following error occurs.

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.
CHANGES
This error is most likely to happen after performing one of the following changes:

MySQL was upgraded from version 5.0 or earlier.
The storage engine was changed to InnoDB for one or more tables.
The default transaction level was changed to READ COMMITTED or READ UNCOMMITTED from REPEATABLE READ or SERIALIZABLE for the MySQL instance.
The transaction level for a the transaction giving the error was changed (see previous line).
The binary log format was changed to STATEMENT from either ROW or MIXED.
CAUSE
The error occurs because MySQL 5.1 and later will not allow you to use the statement-based binary log format with the transactions levels READ COMMITTED and READ UNCOMMITTED with InnoDB. The reason for this is that those transaction levels are not safe with the statement-based format as it cannot be guaranteed replaying the binary log will give the same data, so for example a slave can become out of sync.

See also Document 1023678.1 for more about using transactions with InnoDB.

SOLUTION
There are two solutions:

Change the binlog_format to MIXED or ROW.
Change the transaction isolation level to REPEATABLE READ.
 

1. Change the binlog_format
The MIXED format will cause all statements using the READ COMMITTED or READ UNCOMMITTED transaction levels to be logged using the row-based format.

If you do not have a PRIMARY KEY on all the tables, using the row-based format can make replaying the binary log (e.g. on the slave) very slow.

Changing the binary log format to MIXED can be done by updating the binlog_format variable:

SET GLOBAL binlog_format = MIXED;
 

Note the change will only take effect for new connections.

and update the MySQL configuration file to ensure the change persists after a restart of MySQL:

[mysqld]
binlog_format = MIXED

See also Mixed Binary Logging Format in the Reference Manual for a complete list of rules used to determine whether an update will be logged using the statement-based or row-based format.

2. Change the Transaction Isolation Level
Using REPEATABLE-READ (or SERIALIZABLE) as the transaction isolation level makes InnoDB take additional locks which ensure that using statement-based replication is safe - provided the query itself is deterministic. 

You can change the transaction isolation level using the SET TRANSACTION statement. This can be done both for all new connections or just the current connection.

To change for all new connections use:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

You will also have to update the MySQL configuration file setting the transaction-isolation option to ensure the change persists when restarting MySQL:

[mysqld]
transaction-isolation = REPEATABLE-READ

In order to change the transaction isolation level just for the session. This can be done in different ways depending on the scope intended for the change:

For the session:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Just for the next (unstarted) transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

You are not allowed to use SET TRANSACTION without the SESSION or GLOBAL modifier if you are already inside a transaction.
You can verify the current transaction isolation level using the tx_isolation variable:

mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| REPEATABLE-READ        | READ-COMMITTED        |
+------------------------+-----------------------+
1 row in set (0.00 sec)
 

You can also change the transaction isolation levels by updating the tx_isolation variable directly, however that is a MySQL specific feature and does not work in other databases.