Bug #79786 unnecessarily generate undo logs while update nothing
Submitted: 28 Dec 2015 14:29 Modified: 8 Jan 2016 11:37
Reporter: zhai weixiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7,5.6, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[28 Dec 2015 14:29] zhai weixiang
Description:
If binlog is disabled or binlog_row_image is set to MINIMAL,  it will generate undo logs for statements that changes nothing.

How to repeat:
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    3 |
+---+------+
2 rows in set (0.00 sec)

mysql> set session binlog_row_image = FULL;                                                                                                                                                                 Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> pager grep 'undo log entries'
PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set session binlog_row_image = minimal;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 3
1 row in set (0.00 sec)

disable binary log
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    3 |
+---+------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> pager grep 'undo log entries'                                                                                                                                                                        PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)

Suggested fix:
From the case above,  if  table->write_set is not a subset of table->read_set, it will skip comparing records and always run into engine layer to try to update something. 

Since I am not an expert of server layer,  just file the bug to confirm if this is a designed behaviour or not ?
[8 Jan 2016 11:37] MySQL Verification Team
Hello Zhai,

Thank you for the report and test case.
Observed this with 5.7.10 build.

Thanks,
Umesh
[8 Jan 2016 11:37] MySQL Verification Team
// 5.7.10

[root@cluster-repo jemalloc-4.0.3]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11026
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  CREATE TABLE `t1` (
    ->   `a` int(11) NOT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`a`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1,2),(2,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    3 |
+---+------+
2 rows in set (0.00 sec)

mysql> set session binlog_row_image = FULL;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> pager grep 'undo log entries'
PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  set session binlog_row_image = minimal;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>  show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>  show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)

mysql> update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>  show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 3
1 row in set (0.00 sec)

// restart mysqld iwth log_bin truned off

[root@cluster-repo jemalloc-4.0.3]# mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    3 |
+---+------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> pager grep 'undo log entries'
PAGER set to 'grep 'undo log entries''
mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
1 row in set (0.00 sec)

mysql>  update t1 set b = 2 where a  =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
1 row in set (0.00 sec)