MySQL Bugs: #65745: UPDATE on InnoDB table enters recursion, eats all disk space

Bug #65745	UPDATE on InnoDB table enters recursion, eats all disk space
Submitted:	27 Jun 2012 7:15	Modified:	30 Jun 2012 18:11
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.25, 5.5.26	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
This only started with 5.5.25, looking at the changelogs it may be related to the fix for bug 65111

An UPDATE that should only modify a single row runs forever and blows up table / tablespace size until there is no more disk space available

How to repeat:
The smallest test case i could come up with is:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id1 int NOT NULL,
  id2 int NOT NULL,
  a int,
  b int,
  PRIMARY KEY (id1,id2),
  KEY (id1, a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1,1,NULL,1);
INSERT INTO `t1` VALUES (2,2,1,NULL);
INSERT INTO `t1` VALUES (2,3,2,NULL);
INSERT INTO `t1` VALUES (2,4,3,NULL);
INSERT INTO `t1` VALUES (2,5,4,NULL);
INSERT INTO `t1` VALUES (2,6,NULL,2);

UPDATE t1 SET id2 = id2 + 1, b = null WHERE a is null and id1 = 2;

Removing any further row, column or index makes the problem disappear.

Suggested fix:
Hard to tell as there are no patches / commits linked to the original bug #65111 and no commit messages referring to either bug 65111 or the internal #14007649 either ...

Verified just as described with current mysql-5.5 on 64-bit FC14. SHOW ENGINE INNODB STATUS also hangs and free disk space is disappearing slowly:

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+-------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State    | Info                                                              |
+----+------+-----------+------+---------+------+----------+-------------------------------------------------------------------+
|  1 | root | localhost | test | Query   |  134 | Updating | UPDATE t1 SET id2 = id2 + 1, b = null WHERE a is null and id1 = 2 |
|  3 | root | localhost | test | Killed  |   27 | NULL     | show engine innodb status                                         |
|  6 | root | localhost | test | Query   |    0 | NULL     | show processlist                                                  |
+----+------+-----------+------+---------+------+----------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.26-debug |
+--------------+
1 row in set (0.00 sec)
...

[openxs@chief 5.5]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1            100791728  48979388  50788596  50% /
tmpfs                  1010788       436   1010352   1% /dev/shm
df: `/home/anna/.gvfs': Permission denied
[openxs@chief 5.5]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1            100791728  48987580  50780404  50% /
tmpfs                  1010788       436   1010352   1% /dev/shm
df: `/home/anna/.gvfs': Permission denied

Hit the same bug as well. Even the mysql shutdown hung, had to SIGKILL it.

Hit the same bug as well. Even the mysql shutdown hung, had to SIGKILL it.

Workaround:

SET GLOBAL optimizer_switch = 'index_merge=off';

The regression is caused by the fix for bug#65111

Yes, this function in ha_innobase.cc.

UNIV_INTERN
handler*
ha_innobase::clone(
/*===============*/
	const char*	name,		/*!< in: table name */
	MEM_ROOT*	mem_root)	/*!< in: memory context */
{
	ha_innobase* new_handler;

	DBUG_ENTER("ha_innobase::clone");

	new_handler = static_cast<ha_innobase*>(handler::clone(name,
							       mem_root));
	if (new_handler) {
		DBUG_ASSERT(new_handler->prebuilt != NULL);
		DBUG_ASSERT(new_handler->user_thd == user_thd);
		DBUG_ASSERT(new_handler->prebuilt->trx == prebuilt->trx);

		new_handler->prebuilt->select_lock_type
			= prebuilt->select_lock_type;
	}

	DBUG_RETURN(new_handler);
}

see also Bug 14226171 - EXCESSIVE ROW LOCKING WITH UPDATE IN 5.5.25 as a side effect. Although it was considered as "not a bug". Note the index merge in the bug test case.

Also see http://bugs.mysql.com/bug.php?id=65775

Noted in 5.5.25a, 5.6.6 changelogs.

A regression bug in the optimizer could cause excessive disk usage
for UPDATE statements.

@Paul .. I think 5.5.25a/5.5.26 changelogs should tell users affected how to repair this.

1) If disk space in used in temp tables, I think stopping server + emptying /tempdir + starting server should solve this. That is pretty simple.

2) But if it is the InnoDB tablespace(s) that consume the diskspace it can be hard to repair, as far as I can understand.  In my understanding OPTIMIZE TABLE <for every table> should fix this if using 'innodb_file_per_table' (provided that there is still enough free disk space to execute OPTIMIZE TABLE at all). If 'innodb_file_per_table' is not used I don't really see any other option than to dump all databases and restore to a fresh server/InnoDB instance. I hope there is a better option and version history notes should help users in this respect.

I received a customer report that mentioned a malloc error
(sorry, no exact message to quote) and subsequent crashes,
happening only on machines running MySQL 5.5.25.

After upgrading those machines to MySQL 5.5.25a, the problem is gone.

My conclusion is that (probably depending on disk and RAM sizes),
the bug may also show up as address space problem before disk space
becomes an issue.