During a nightly OPTIMIZE TABLE mysql seemed to have got hung up while optimizing the table.
Someone kill -9 mysqld and restarted the server. Now we have this stuck tablespace(?) that we
can't seem to get rid of. We have tried following instructions here:
http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshootin g-datadict.html
but nothing seems to work. I also tried dropping the database which caused MySQL to crash
and restart. It generated a back trace and some other stuff in the logs:
Thread pointer: 0x92c3278
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 = ffffffffa84ec36c thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x33)[0x83f2923]
/usr/sbin/mysqld(handle_fatal_signal+0x48c)[0x82bf4fc]
[0x23b420]
/lib/libc.so.6(abort+0x101)[0x2b9701]
/usr/sbin/mysqld[0x84855ea]
/usr/sbin/mysqld[0x846ca30]
/usr/sbin/mysqld[0x82bf585]
/usr/sbin/mysqld(_Z24plugin_foreach_with_maskP3THDPFcS0_P13s t_plugin_intPvEijS3_+0x19a)[0x81bde0a]
/usr/sbin/mysqld(_Z16ha_drop_databasePc+0x3f)[0x82c06cf]
/usr/sbin/mysqld(_Z11mysql_rm_dbP3THDPcbb+0x9bf)[0x819193f]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3468)[0x81 b19e8]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x36 9)[0x81b5059]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0x1968)[0x81b6a58]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x131)[0x81b6f31]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xe5)[0x8 25b195]
/usr/sbin/mysqld(handle_one_connection+0x50)[0x825b2f0]
/lib/libpthread.so.0[0x3f8852]
/lib/libc.so.6(clone+0x5e)[0x362a8e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (92c5758): drop database voicemail
Connection ID (thread ID): 3
Status: NOT_KILLED
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name like '#%';
+----------+-----------------+-------------+------+--------+ -------+
| TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+----------+-----------------+-------------+------+--------+ -------+
| 47 | voicemail | #sql2-af6-2 | 1 | 11 | 44 |
+----------+-----------------+-------------+------+--------+ -------+
This seems like a bug in some regards. The only think I can think of at this point to get
rid of this orphan is to completly blow out mysql and restore from backups. We are using
one file per table. Any help would be appreciated.
Running Percona-Server-server-55-5.5.27-rel28.0.291.rhel5