Since moving from 5.1.36 xtradb 5 to 5.1.43, xtradb 9.1, we have had regular mysql segfaults during index builds. This doesn't happen every time, but has happened 3 different times on 2 different servers. (identical server builds).
Each time, rebuilding the table via dump from a replica is required to fix the problem.
100409 9:20:56 [ERROR] Table la_management_console/type1_session_memberships contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
100409 9:24:12 InnoDB: Assertion failure in thread 1269074256 in file handler/ha_innodb.cc line 7113
InnoDB: Failing assertion: index
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.
100409 9:24:12 - 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=134217728
read_buffer_size=10485760
max_used_connections=29
max_threads=2000
threads_connected=19
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 53400306 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0xa6051f0
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...
stack_bottom = 0x4ba48100 thread_stack 0x20000
/usr/local/mysql/libexec/mysqld(my_print_stacktrace+0x35)[0x8a2315]
/usr/local/mysql/libexec/mysqld(handle_segfault+0x322)[0x5ca022]
/lib64/libpthread.so.0[0x7f38937057d0]
/lib64/libc.so.6(gsignal+0x35)[0x7f3892af1095]
/lib64/libc.so.6(abort+0x110)[0x7f3892af2af0]
/usr/local/mysql/libexec/mysqld[0x76af9c]
/usr/local/mysql/libexec/mysqld[0x6ae323]
/usr/local/mysql/libexec/mysqld[0x6aec30]
/usr/local/mysql/libexec/mysqld[0x6aef07]
/usr/local/mysql/libexec/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyyb+0xdfe)[0x6b803e]
/usr/local/mysql/libexec/mysqld[0x647bc0]
/usr/local/mysql/libexec/mysqld(_ZN4JOIN8optimizeEv+0x5a1)[0x648601]
/usr/local/mysql/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xab)[0x651a2b]
/usr/local/mysql/libexec/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x169)[0x652459]
/usr/local/mysql/libexec/mysqld[0x5d53fc]
/usr/local/mysql/libexec/mysqld(_Z21mysql_execute_commandP3THD+0xcd3)[0x5d9f73]
/usr/local/mysql/libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x39b)[0x5e131b]
/usr/local/mysql/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x403)[0x5e1723]
/usr/local/mysql/libexec/mysqld(_Z10do_commandP3THD+0x120)[0x5e2970]
/usr/local/mysql/libexec/mysqld(handle_one_connection+0x690)[0x5d2ba0]
/lib64/libpthread.so.0[0x7f38936fd3f7]
/lib64/libc.so.6(clone+0x6d)[0x7f3892b96b4d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x7f36f13488b0 is an invalid pointer
thd->thread_id=2628
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
100409 09:24:13 mysqld_safe Number of processes running now: 0
Linux 2.6.24-25-generic #1 SMP Tue Oct 20 06:49:12 UTC 2009 x86_64 GNU/Linux
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=8.04
DISTRIB_CODENAME=hardy
DISTRIB_DESCRIPTION="Ubuntu 8.04.3 LTS"
Server version: 5.1.43-log Percona SQL Server (GPL), XtraDB 9.1, Revision 59
[mysqld]
basedir=/usr/local/mysql
key_buffer = 128M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 2048
table_cache = 4096
sort_buffer = 16M
join_buffer_size = 2M
read_buffer_size = 10M
read_rnd_buffer_size = 16M
max_connections = 2000
max_allowed_packet = 32M
max_connect_errors = 99999999
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
log_slave_updates
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 5G
innodb_file_per_table = 1
innodb_thread_concurrency = 0
innodb_commit_concurrency = 1
innodb_thread_sleep_delay = 0
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 12M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 25
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
innodb_ibuf_accel_rate = 200
innodb_overwrite_relay_log_info=true
sync_binlog=1
innodb_file_format = barracuda
innodb_io_capacity = 4000
innodb_support_xa = false
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_ibuf_active_contract = 1
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = estimate
server-id = 116
auto_increment_increment = 2
auto_increment_offset = 1
user=mysql
group=mysql
[mysql.server]
user=mysql
basedir=/usr/local/mysql
datadir=/var/lib/mysql
It seems that simply the index which mysqld wants to use by "its name"(not id) is not found in InnoDB.
There seems to be some inconsistency between .frm file and InnoDB files.
From only information here, I cannot specify where the inconsistency come from.
(I cannot judge whether it is bug or not..)