MySQL Bugs: #26608: InnoDB: Assertion failure in thread * in file row0mysql.c line 3228

Bug #26608	InnoDB: Assertion failure in thread * in file row0mysql.c line 3228
Submitted:	23 Feb 2007 19:55	Modified:	24 May 2007 12:13
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.36BK, 5.1.16BK, 5.2.4BK	OS:	Linux (suse9.3 x86)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crash, innodb assertion

Description:
after trying to open many transactions and hitting the 1024 limit, any attempt to drop the innodb table results in an assertion failure.  Below is the error log:

Version: '5.2.4-falcon-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
070223 21:47:40InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
070223 21:47:40  InnoDB: Assertion failure in thread 1655241648 in file row0mysql.c line 3228
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.
mysqld got signal 6;
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=8388572
read_buffer_size=131072
max_used_connections=1
max_connections=2000
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 165817 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8e406e8
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...
Cannot determine thread, fp=0x62a8cb8c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81f57a7 handle_segfault + 417
0xffffe410 _end + -142428192
0x40155b75 _end + 932720453
0x84585cf row_drop_table_for_mysql + 1880
0x8422032 _ZN11ha_innobase12delete_tableEPKc + 390
0x82d64f9 _Z15ha_delete_tableP3THDP10handlertonPKcS4_S4_b + 301
0x82e1ef3 _Z20mysql_rm_table_part2P3THDP13st_table_listbbbb + 1881
0x82e1577 _Z14mysql_rm_tableP3THDP13st_table_listcc + 297
0x821260d _Z21mysql_execute_commandP3THD + 12391
0x8218c01 _Z11mysql_parseP3THDPcj + 353
0x820e0c1 _Z16dispatch_command19enum_server_commandP3THDPcj + 2077
0x820d899 _Z10do_commandP3THD + 603
0x820c986 handle_one_connection + 900
0x40250aa7 _end + 933748343
0x401e6c2e _end + 933314558
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e4a288 = DROP TABLE IF EXISTS `yt`
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
on a clean installation, run the server like this:

 ./bin/mysqld_safe --skip-grant-tables --open_files_limit=16384 --skip-name-resolve --innodb_buffer_pool_size=500M &

Then, run the attached testcase, which will fail as expected like this:

<cut>
1023
1024
The table 'yt' is full

Now, run it again.  The DROP TABLE crashes.

Suggested fix:
Don't crash

php/mysqli testcase

Attachment: bug26608.php (application/octet-stream, text), 987 bytes.

crashed 5.0.32-enterprise release binary also, without the assertion in the error log.  In fact, this bug seems serious, as I could no long drop table of my innodb tables, even after restarting the mysqld_safe.

http://bugs.mysql.com/bug.php?id=26590 is associated with this.

Ideally, we should have an infinite number of slots available for active transactions.

Sunny should look if there is some easy fix to this (besides creating many rollback segment pages).
--Heikki

Ok, Sunny told me that the little fix of http://bugs.mysql.com/bug.php?id=18828 will fix also this crash. The fix is already in the pipeline. I am marking this as a duplicate.

Heikki, the fix for 18828 is for 5.1 only, this affects 5.0 also. Any plans to do something that would help 5.0 customers who already have this problem and can't drop tables without crashing? Fortunately this is rare. I've a report that limiting max_connections seems to help? It seems that today the best they can do is a full dump and reload, or alter table to MyISAM then shut down the server, rename the InnoDB tablespace and log files, restart, alter table back to InnoDB?

Hi Heikki,

You made this bug a duplicate of bug #26590 however bug #26590 is only going to be fixed in 6.x.  Is there a way to just fix this crashing in 5.x ??

If you are affected by this bug and the problem persists on restart then
see Bug#35352 for a possible workaround.