MySQL Bugs: #43665: innodb crashes when tablespace reaches maximum configured size

Bug #43665	innodb crashes when tablespace reaches maximum configured size
Submitted:	16 Mar 2009 7:15	Modified:	14 Nov 2010 0:56
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.76, 5.1.41, 5.5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is either an innodb bug or a documentation request, or both.

case1:
The testcase generates this error (which is easy to debug):

Version: '5.1.32-enterprise-gpl-advanced'  socket: ''  port
InnoDB: Fatal error 13 in rollback.
InnoDB: Error 13 means out of tablespace.
InnoDB: Consider increasing your tablespace.

BTW, I'm not sure calling exit(1) is the best way to quit.

case2:
However, when dropping temporary tables in the background we get a crash/assertion: 

090315 2:43:16InnoDB: Assertion failure in thread 11944 in file .\row\row0mysql.c line 3340

Crash is in function "row_drop_table_for_mysql" which tells innodb to drop a table:

if (err != DB_SUCCESS) {
ut_a(err == DB_OUT_OF_FILE_SPACE);

err = DB_MUST_GET_MORE_FILE_SPACE;

row_mysql_handle_errors(&err, trx, thr, NULL);

ut_error;
<cut>

So there's inconsistency in the error handling, at least in printing diagnostic messages.

docs:

For a docs request, consider documenting the exact effect running out of tablespace has (i.e. crash - requiring manual intervention!) at http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html

Notice, I'm opening this bug report because innodb can be crashed using a standard functionality option in my.cnf (:MAX:..M) for tablespace.

How to repeat:
On a fresh installation with no ibdata files, start mysqld with option: --innodb_data_file_path=ibdata1:10M:autoextend:max:10M

create temporary table t1(data longblob)engine=innodb;
start transaction;
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
commit;

Suggested fix:
workaround: buy more disks and let innodb use unlimited space.
suggested fix: make innodb more robust in dealing with these cases.

Marko, please have a look. I think it's more of a feature request.
One could also argue that InnoDB should try to take entire mysqld down
less often especially when the problem is limited to InnoDB functionality

Fixing this bug would be a major undertaking, and it might affect the performance of InnoDB as well. InnoDB assumes in many places that space is available for the undo log records. We could add a check (and a rollback) to each place where something is written to the undo log, but that would likely move the problem elsewhere, since B-tree delete operations sometimes require extra space. We could also shut down InnoDB in the event of such a failure, but that would require extra checking (possibly even locking) in the MySQL-to-InnoDB interface.

The bottom line is that the root cause (the InnoDB system tablespace being too small) cannot be fixed without a server restart. Currently, InnoDB can't be shut down and restarted independently of MySQL. The fix is too risky to be implemented in the stable releases of MySQL.

also see bug#48469.

for what it's worth, here's the stack trace of a crash on 5.1.41
I provoked it by trying to create/drop temporary tables in 1025 connections.

091130 19:47:03  InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
091130 19:47:03  InnoDB: Assertion failure in thread 7460 in file .\row\row0mysql.c line 3249
InnoDB: Failing assertion: err == DB_OUT_OF_FILE_SPACE
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 8364 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 7648 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 6924 stopped in file .\trx\trx0trx.c line 1644
091130 19:47:03 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1259
max_threads=2000
threads_connected=1027
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 183712 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3f55b760
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
InnoDB: Thread 4844 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 10768 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 8908 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 9096 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 9892 stopped in file .\trx\trx0trx.c line 1644
InnoDB: Thread 7152 stopped in file G:\mysql-5.1.41-winbuild\mysql-advanced-gpl-5.1.41-build\storage\inno
InnoDB: Thread 5172 stopped in file .\handler\ha_innodb.cc line 4700
InnoDB: Thread 7156 stopped in file G:\mysql-5.1.41-winbuild\mysql-advanced-gpl-5.1.41-build\storage\inno
000000014021D351    mysqld.exe!row_drop_table_for_mysql()[row0mysql.c:3249]
000000014020F826    mysqld.exe!ha_innobase::delete_table()[ha_innodb.cc:5789]
0000000140073C47    mysqld.exe!rm_temporary_table()[sql_base.cc:5560]
000000014007597B    mysqld.exe!close_temporary()[sql_base.cc:1916]
000000014007A9D5    mysqld.exe!drop_temporary_table()[sql_base.cc:1840]
00000001400E3FF0    mysqld.exe!mysql_rm_table_part2()[sql_table.cc:1915]
00000001400E4812    mysqld.exe!mysql_rm_table()[sql_table.cc:1800]
000000014006C083    mysqld.exe!mysql_execute_command()[sql_parse.cc:3394]
000000014006EB5E    mysqld.exe!mysql_parse()[sql_parse.cc:5974]
000000014006F6FA    mysqld.exe!dispatch_command()[sql_parse.cc:1233]
0000000140070377    mysqld.exe!do_command()[sql_parse.cc:872]
0000000140096B37    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
0000000140317635    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402E1767    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402E1835    mysqld.exe!_threadstart()[thread.c:275]
0000000077D6B6CA    kernel32.dll!BaseThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 000000003F7C2220=drop temporary table if exists t1
thd->thread_id=3679
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

This also crashes with one temporary table, if it gets unlucky and is being dropped when server is out of undo slots.

What was the failing assertion? Was it ut_a(err == DB_OUT_OF_FILE_SPACE) by any chance? I would like to see the value of err. It should be DB_TOO_MANY_CONCURRENT_TRXS, and the fix would be that row_drop_table_for_mysql() should return this code to the caller. While at it, I would make DB_OUT_OF_FILE_SPACE non-fatal as well. Currently, DB_OUT_OF_FILE_SPACE in row_drop_table_for_mysql() throws an assertion failure.

Hi Marko,

The assertion that failed is mentioned in Shane's comment:

InnoDB: Failing assertion: err == DB_OUT_OF_FILE_SPACE

So yes, it runs out of undo slots and then asserts since row_drop_table_for_mysql() returns DB_OUT_OF_FILE_SPACE rather than DB_TOO_MANY_CONCURRENT_TRXS.

I broke out this case into a different bug report, since it seems like it might have a different fix than this bug, please see Bug #49238.

Case 2 described in the bug report will be addressed in bug #49238.

For case 1, the problem has been reproduced, this is a situation when we run out of space during rollback a transaction, and at the present, the crash of server is intentional and expected. And suggest to be well documented.

Theoretically, we should reserve enough space for any possible rollback, so server rollback never fails. However, it is difficult to estimate the amount of space to reserve without possible over-reservation and wasting space. To fix this, it would require feature level work in the space management system. 

Briefly talked about this with Heikki sometime back, we would treat this behavior(crash) as expected, and should be documented for now. In the future, it will considered in a separate feature work.

Thanks
Jimmy

Assign to John for docs.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Case 2 is still verified.  
Docs:  please advise *against* limiting innodb tablespace via that :MAX option.
Then set back to 'verified' :-/

Here is the text I'm adding to the description of the max attribute in the Storage Engines chapter:

Use the max attribute only in cases where constraining disk usage is
of critical importance, because exceeding the maximum size causes a
fatal error, possibly including a crash.

Not sure why Shane reassigned this to me, since John is in charge of InnoDB docs.

Since this is no longer a Docs bug and has been set back to a Server category, I'm removing myself as assignee and Stefan as lead, and setting status back to Open.

Please set correct lead so this bug can be assigned to a developer.

Thanks!

case 1:

drop table if exists t1;
create table t1(data longblob)engine=innodb;
start transaction;
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 (data) values (repeat('a',1048576));
insert into t1 select t1.data from t1,t1 t,t1 g,t1 h,t1 j;
commit;

I:\mysql\5.5\5.5.7\mysql-5.5.7-rc-winx64\bin>mysqld-debug --console --skip-gr --skip-na --innodb_data_file_path=ibdata1:10M:autoextend:max:19M

Version: '5.5.7-rc-debug'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
InnoDB: Fatal error 14 in rollback.
InnoDB: Error 13 means out of tablespace.
InnoDB: Consider increasing your tablespace.

now a feature request for better handling and no server shutdown.

I have experienced quite a variety of crashes lately when a fixed-size main tablespace (with innodb_file_per_table=1) fills up. I think more testing needs to be done for these types of scenarios. It is not hard to make a lot of bad things happen in not only normal operation, but also replication, and crash recovery.