MySQL Bugs: #22728: Handler_rollback value is growing

Bug #22728	Handler_rollback value is growing
Submitted:	27 Sep 2006 8:43	Modified:	13 Nov 2006 19:39
Reporter:	Tonci Grgin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.22	OS:	Linux (Suse10.0 32bit)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
Handler_rollback value is growing when looking at "show status" with no apparent reason.

How to repeat:
MySQL server 4.1.22 BK, ChangeSet@1.2563, 2006-09-04
SQL:
DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
`c1` int(11) NOT NULL default '0',
`c2` char(100) default NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `my_pk` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

HANDLER mytable OPEN;
HANDLER mytable READ my_pk = (29);
HANDLER mytable CLOSE;
quit;

show status like '%rollback%';

C:\mysql\bin>mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `mytable` (
    -> `c1` int(11) NOT NULL default '0',
    -> `c2` char(100) default NULL,
    -> PRIMARY KEY (`c1`),
    -> UNIQUE KEY `my_pk` (`c1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> HANDLER mytable OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER mytable READ mytable_pk = (29);
ERROR 1176 (HY000): Key 'mytable_pk' doesn't exist in table 'mytable'
mysql> HANDLER mytable CLOSE;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

C:\mysql\bin>mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show status like '%rollback%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_rollback     | 0     |
| Handler_rollback | 1     |
+------------------+-------+
2 rows in set (0.02 sec)

mysql> quit
Bye

C:\mysql\bin>mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `mytable`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `mytable` (
    -> `c1` int(11) NOT NULL default '0',
    -> `c2` char(100) default NULL,
    -> PRIMARY KEY (`c1`),
    -> UNIQUE KEY `my_pk` (`c1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> HANDLER mytable OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER mytable READ my_pk = (29);
Empty set (0.00 sec)

mysql> HANDLER mytable CLOSE;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

C:\mysql\bin>mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show status like '%rollback%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Com_rollback     | 0     |
| Handler_rollback | 2     |
+------------------+-------+
2 rows in set (0.00 sec)

mysql>

Suggested fix:
-

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13317

ChangeSet@1.2541, 2006-10-08 17:03:56+04:00, kaa@polly.local +3 -0
  Fix for bug #22728 "Handler_rollback value is growing".
  
  For InnoDB, check value of thd->transaction.all.innodb_active_trans instead of thd->transaction.stmt.innobase_tid to see if we really need to rollback.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13454

ChangeSet@1.2541, 2006-10-11 12:44:03+04:00, kaa@polly.local +3 -0
  Fix for bug #22728 "Handler_rollback value is growing".
  
  The bug is present only in 4.1, will be null-merged to 5.0
  
  For InnoDB, check value of thd->transaction.all.innodb_active_trans instead of thd->transaction.stmt.innobase_tid to see if we really need to rollback.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13916

ChangeSet@1.2312, 2006-10-18 23:34:30+02:00, msvensson@neptunus.(none) +2 -0
  Bug#22728 Handler_rollback value is growing
   - The handler_rollback value is not growing, but the testcase
  does not work as handler_rollback is not cleared by "flush status"
  Thus we need to record before and after value and do a compare

Noted in 4.1.23, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

The Handler_rollback status variable sometimes was incremented when
no rollback had taken place.