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:
Tags: regression
Triage: Needs Triage: D2 (Serious)

[27 Jun 2012 7:15] Hartmut Holzgraefe
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 ...
[27 Jun 2012 10:34] Valerii Kravchuk
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
[27 Jun 2012 16:30] Raghavendra Prabhu
Hit the same bug as well. Even the mysql shutdown hung, had to SIGKILL it.
[27 Jun 2012 16:34] Raghavendra Prabhu
Hit the same bug as well. Even the mysql shutdown hung, had to SIGKILL it.
[27 Jun 2012 18:05] Davi Arnaut
Workaround:

SET GLOBAL optimizer_switch = 'index_merge=off';
[28 Jun 2012 8:57] Evgeny Potemkin
The regression is caused by the fix for bug#65111
[28 Jun 2012 9:38] Arnaud Adant
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.
[30 Jun 2012 14:55] Peter Laursen
Also see http://bugs.mysql.com/bug.php?id=65775
[30 Jun 2012 18:11] Paul Dubois
Noted in 5.5.25a, 5.6.6 changelogs.

A regression bug in the optimizer could cause excessive disk usage
for UPDATE statements.
[30 Jun 2012 18:22] Peter Laursen
@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.
[9 Aug 2012 15:04] Joerg Bruehe
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.