Description:
While running a very large 'alter table drop foreign key xxxxx' on a 750,000 row InnoDB table, mysqld crashed. It seems that InnoDB forced the segfault. Below are the relevant pieces of the log file, including the resolved stack dump:
=====================================
041124 15:08:00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8648, signal count 8619
--Thread 262161 has waited at btr0cur.c line 402 for 0.00 seconds the semaphore:
X-lock on RW-latch at 0x418df238 created in file buf0buf.c line 469
a writer (thread id 49156) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file buf0flu.c line 620
Last time write locked in file ibuf0ibuf.c line 3042
Mutex spin waits 64674, rounds 311553, OS waits 797
RW-shared spins 6079, OS waits 3037; RW-excl spins 4389, OS waits 4277
------------
TRANSACTIONS
------------
Trx id counter 0 16230940
Purge done for trx's n:o < 0 16229468 undo n:o < 0 0
Total number of lock structs in row lock hash table 155902
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 16230939, ACTIVE 362 sec, process no 10858, OS thread id 262161 inserting, thread declared inside InnoDB 366
mysql tables in use 2, locked 3
155905 lock struct(s), heap size 7515456, undo log entries 412535
MySQL thread id 68, query id 165 localhost ccast copy to tmp table
alter table properties drop foreign key properties_ibfk_1
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: complete io for buf page (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
29014 OS file reads, 30333 OS file writes, 5401 OS fsyncs
117.49 reads/s, 22998 avg bytes/read, 118.18 writes/s, 21.06 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 15, free list len 15, seg size 31, is not empty
185044 inserts, 182349 merged recs, 2543 merges
Hash table size 34679, used cells 3, node heap has 1 buffer(s)
668.96 hash searches/s, 2516.72 non-hash searches/s
---
LOG
---
Log sequence number 3 3679963863
Log flushed up to 3 3679963784
Last checkpoint at 3 3676948288
0 pending log writes, 0 pending chkp writes
3711 log i/o's done, 7.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 16871304; in additional pool allocated 1046912
Buffer pool size 512
Free buffers 0
Database pages 53
Modified db pages 5
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 117976, created 55435, written 117584
164.86 reads/s, 36.12 creates/s, 162.43 writes/s
Buffer pool hit rate 986 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread process no. 10848, id 114696, state: sleeping
Number of rows inserted 412534, updated 0, deleted 0, read 412561
276.73 inserts/s, 0.00 updates/s, 0.00 deletes/s, 276.80 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
041124 15:08:08 InnoDB: ERROR: over 9 / 10 of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 8 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: We intentionally generate a seg fault to print a stack trace
InnoDB: on Linux!
041124 15:08:08InnoDB: Assertion failure in thread 262161 in file buf0lru.c line 324
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/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
=========== resolved portion below =============
mysqld got signal 11;
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=536870912
read_buffer_size=4190208
max_used_connections=18
max_connections=750
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2470978 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8a45220
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=0x41fc7808, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8131043 handle_segfault + 423
0x400387ce _end + 933196358
0x83165a0 buf_LRU_get_free_block + 536
0x830dce6 buf_page_create + 14
0x833360b fseg_alloc_free_page_low + 5291
0x8333bfb fseg_alloc_free_page_general + 1175
0x82ba31e btr_page_alloc + 86
0x82c2467 btr_page_split_and_insert + 895
0x82d20d6 btr_cur_pessimistic_insert + 1686
0x82913dd row_ins_index_entry_low + 3405
0x829155c row_ins_index_entry + 96
0x829163f row_ins_index_entry_step + 55
0x8291900 row_ins + 696
0x8291a3c row_ins_step + 252
0x8293180 row_insert_for_mysql + 456
0x81a951a write_row__11ha_innobasePc + 1158
0x81bb576 copy_data_between_tables__FP8st_tableT0Rt4List1Z12create_field15enum_duplicatesUiP8st_orderPUxT6 + 1102
0x81ba8af mysql_alter_table__FP3THDPcT1P24st_ha_create_informationP13st_table_listRt4List1Z12create_fieldRt4List1Z3KeyUiP8st_order15enum_ + 5695
0x81416df mysql_execute_command__FP3THD + 5771
0x8144b89 mysql_parse__FP3THDPcUi + 169
0x813f13f dispatch_command__F19enum_server_commandP3THDPcUi + 1643
0x813eac8 do_command__FP3THD + 188
0x813e1f8 handle_one_connection + 616
0x40033881 _end + 933176057
0x420e40c7 _end + 967453503
New value of fp=(nil) failed sanity check, terminating stack trace!
====== end of resolved portion ======
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 0x64f00528 is invalid pointer
thd->thread_id=68
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.
Number of processes running now: 0
041124 15:08:08 mysqld restarted
How to repeat:
Leave innodb_buffer_pool_size at the default of 8M, and run something that fills it up.
Suggested fix:
This should not crash the whole server. It should abort the transaction to free the buffer pool memory.
The workaround is of course to increase innodb_buffer_pool_size. In my case, increasing it to 512M was sufficient. I did not try values between 8M and 512M. I also set innodb_additional_mem_pool_size to 16M. After this the alter table completed.
