MySQL Binlog Mixed mode is recorded as row format

Concept:

binlog format has three forms: Statement, Mixed, Row, specific information can be self-search online.

Analysis (cases encountered in this article):

View MySQL binlog format

[Email protected]: dba_test 02:33:39>show variables like ' binlog_format% '; +---------------+-------+| variable_name | Value |+---------------+-------+| Binlog_format | MIXED |+---------------+-------+

Test statement:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

[Email protected]: dba_test 02:24:14>create table tmp_test (ID int,name varchar (), Age int,primary key (ID)) engine = I Nnodb;
Query OK, 0 rows affected (0.05 sec)

[Email protected]192.168.111.4:dba_test 02:24:23>insert into tmp_test values (1, ' AAA ', 11);
Query OK, 1 row affected (0.02 sec)

[Email protected]: dba_test 02:25:17>insert into tmp_test values (2, ' BBB ', 22);
Query OK, 1 row affected (0.02 sec)

[Email protected]: dba_test 02:25:23>insert into tmp_test values (3, ' CCC ', 33);
Query OK, 1 row affected (0.01 sec)

[Email protected]: dba_test 02:25:28>insert into tmp_test values (4, ' ddd ', 44);
Query OK, 1 row affected (0.01 sec)

[Email protected]: dba_test 02:25:34>insert into tmp_test values (5, ' Eee ', 55);
Query OK, 1 row affected (0.01 sec)

[Email protected]: dba_test 02:25:42>select * FROM tmp_test;+----+------+------+| ID | name |  Age |+----+------+------+| 1 |   AAA |  11 | | 2 |   BBB |  22 | | 3 |   CCC |  33 | | 4 |   DDD |  44 | | 5 |   eee | |+----+------+------+5 rows in Set (0.01 sec)

[Email protected]: dba_test 02:25:50>create table Tmp_test_bak (ID int,name varchar (), Age int,primary key (ID)) Engin e = InnoDB;
Query OK, 0 rows affected (0.03 sec)

[Email protected]: dba_test 02:26:31>insert to Tmp_test_bak select * from Tmp_test; # # #记录成了Row模式Query OK, 5 rows affected (0.03 sec)
Records:5 duplicates:0 warnings:0

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Binlog Record Chart:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/163084/201604/163084-20160426154047580-100253863. PNG "width=" 454 "height=" 351 "style=" margin:0px;padding:0px;border:0px; "/>

The problem comes, I want to come out of binlog format is statement, not row. A simple statement that inserts into the TB select * from TA records the binlog of row mode in mixed mode. What is the reason?

First, it is true that in some specific cases, themixed is converted to the row mode :

. When a DML statement updates a NDB table;
. When the function contains a UUID ();
. 2 or more tables containing the Auto_increment field are updated;
. When executing the INSERT DELAYED statement;
. When using UDFs;
. The view must require the use of the row, such as the UUID () function when establishing the view;

Above from the network, interested can test their own tests. For SQL in this article, the above conditions are not met, but the Binlog is also recorded as row format. So it's still weird. Why Binlog format is converted, daily work has encountered the execution of a SQL, will report a warning:

Warning:unsafe statement written to the binary log using statement format since Binlog_format = statement

Is it because this leads to conversion? Because the above SQL can reproduce, there is no reported warning, so this situation is excluded. According to the experience of a parameter: Innodb_locks_unsafe_for_binlog, see the transaction isolation level, then look at the isolation level of the situation:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

[Email protected]: dba_test 05:46:56>select @ @global. tx_isolation;+-----------------------+| @ @global. tx_isolation |+-----------------------+| read-committed |+-----------------------+1 row in Set (0.01 sec)

[Email protected]: dba_test 06:36:45>select @ @session. tx_isolation;+------------------------+| @ @session. tx_isolation |+------------------------+| read-committed |+------------------------+1 row in Set (0.01 sec)

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

You see that the isolation level is read-committed, which is non-repeatable. Set the transaction isolation level to the default repeatable READ:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

[email protected]: Dba_test 06:41:02>set Session transaction ISOLATION level repeatable READ;                          ,         &NB Sp                          ,         &NB Sp       
 Query OK, 0 rows affected (0.14 sec) 
 
 [email protected]: dba_test 06:41:42>select @ @session. tx_isolation;+------------------------+| @ @session. tx_isolation |+------------------------+| Repeatable-read        |+------------------------+1 row in Set (0.00 sec) 
  

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Then execute the test in SQL, found this time mixed Binlog recorded the statement format, normal, in line with expectations. Is this a matter of the isolation level of the transaction caused ? Found this in the handbook:

Rows is released after MySQL have evaluated the WHERE condition.

If you use the READ COMMITTED or enable Innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

Expand to see an example:

650) this.width=650; "id=" Code_img_closed_2e2ea09e-19aa-4e10-97b6-2df37d08ea76 "class=" code_img_closed "src="/img/ Jia.gif "style=" margin:0px;padding:0px 5px 0px 0px;border:0px;vertical-align:middle; "/> View Code

After testing, in 5.1, 5.5, 5.6 have this situation, perhaps this itself is not a problem. :)

MySQL Binlog Mixed mode is recorded as row format