Using mysql 5.0.18.
We have a corrupted InnoDB table in our wordpressmu database (verified through logs). Right now we have the server running in recovery mode (tried levels 2-6) and dumped the database successfully but cannot drop the database. Basically anytime I try to do anything with the corrupted table (or drop the database), mysql crashes and restarts.
Any ideas on how to drop this database so I can import my dump file and get back up and running?
Thanks!
Henry
Below are a portion of the log showing the corruption and our /etc/my.cnf file. Please let me know if I need to post any other information.
what we are seeing in the log:
080910 17:00:15 mysqld started
080910 17:00:16 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use ‘–log-bin=mysqldev-bin’ to avoid this problem.
080910 17:00:25 InnoDB: Started; log sequence number 294 2697819285
080910 17:00:26InnoDB: Error: Insert buffer insert fails; page free 32, dtuple size 36
InnoDB: Cannot insert index record DATA TUPLE: 2 fields;
0: len 8; hex 0000000000445696; asc DV ;; 1: len 20; hex 574f524450524553534d552f6372656174696f6e; asc WORDPRESSMU/creation;;
InnoDB: The table where where this index record belongs
InnoDB: is now probably corrupt. Please run CHECK TABLE on
InnoDB: that table.
Bitmap bits 0
InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: Dump of the child page:
080910 17:00:26 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
;InnoDB: End of page dump
080910 17:00:26 InnoDB: Page checksum 1484120561, prior-to-4.0.14-form checksum 1004440779
InnoDB: stored checksum 974160416, prior-to-4.0.14-form stored checksum 1004440779
InnoDB: Page lsn 294 2355518105, low 4 bytes of lsn at page end 2355518105
InnoDB: Page number (if stored to page already) 84888,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 4294967295 0
InnoDB: (index CLUST_IND of table SYS_IBUF_TABLE_0)
InnoDB: Dump of the parent page:
080910 17:00:26 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
;InnoDB: End of page dump
080910 17:00:26 InnoDB: Page checksum 3195563833, prior-to-4.0.14-form checksum 3166042183
InnoDB: stored checksum 3195563833, prior-to-4.0.14-form stored checksum 3166042183
InnoDB: Page lsn 294 2350375591, low 4 bytes of lsn at page end 2350375591
InnoDB: Page number (if stored to page already) 4,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 4294967295 0
InnoDB: (index CLUST_IND of table SYS_IBUF_TABLE_0)
InnoDB: Corruption of an index tree: table SYS_IBUF_TABLE_0, index CLUST_IND,
InnoDB: father ptr page no 45860, child page no 84888
PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
0: len 4; hex 00000000; asc ;; 1: len 1; hex 00; asc ;; 2: len 4; hex 00000e73; asc s;; 3: len 13; hex 00860300048000860800088000; asc ;; 4: len 4; hex d44204f7; asc B ;; 5: len 8; hex 8000011b524b0a1c; asc RK ;;
n_owned: 0; heap_no: 2; next rec: 183
PHYSICAL RECORD: n_fields 7; 1-byte offsets; info bits 16
0: len 4; hex 00000000; asc ;; 1: len 1; hex 00; asc ;; 2: len 4; hex 000020ec; asc ;; 3: len 13; hex 00860300048000860800088000; asc ;; 4: len 4; hex db3f487b; asc ?H{;; 5: len 8; hex 8000011bcb51eae4; asc Q ;; 6: len 4; hex 0000b324; asc $;;
n_owned: 0; heap_no: 2; next rec: 189
InnoDB: You should dump + drop + reimport the table to fix the
InnoDB: corruption. If the crash happens at the database startup, see
InnoDB: [URL]http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html[/URL] about
InnoDB: forcing recovery. Then dump + drop + reimport.
080910 17:00:26InnoDB: Assertion failure in thread 2358115248 in file btr0btr.c line 624
InnoDB: Failing assertion: btr_node_ptr_get_child_page_no(node_ptr, offsets) == buf_frame_get_page_no(page)
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: [URL]http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html[/URL]
InnoDB: about forcing recovery.
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=268435456
read_buffer_size=1044480
max_used_connections=0
max_connections=1000
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2306136 K
bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.
thd=(nil)
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=0x8c8de24c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815bbb0
0xb75bcd28
0x82b9be4
0x82b9be4
0x82c4ab7
0x82c5266
0x828a9c1
0x828b181
0x82ec760
0x82f27bd
0x82f2c3e
0x82ea23c
0x82bf1c8
0x82bff34
0x829cdd7
0x829a9b2
0x829b93b
0x829ae89
0x829b776
0x829b79b
0x8282e67
0x8282bfb
0x82d4632
0x826a232
0xb75b6dec
0xb74ede8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read [URL]http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html[/URL] 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
The manual page at [URL]MySQL :: MySQL Documentation contains
information that should help you find out what is causing the crash.
080910 17:00:27 mysqld ended
/etc/my.cnf
[mysqld]
datadir=/mysqldata/datadir
socket=/var/lib/mysql/mysql.sock
user=mysql
pid-file=/mysqldata/datadir/mysqld.pid
log-error=/home/mysql/errorlog/mysqld.log
default-table-type=innodb
innodb_data_home_dir=/mysqldata/datadir
innodb_data_file_path=ibdata1:1200M;ibdata2:100M:autoextend
innodb_log_file_size=128MB
innodb_buffer_pool_size=512MB
innodb_force_recovery=6
max_connections=1000
skip-locking
key_buffer = 256M
max_allowed_packet = 64M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
thread_cache = 8
query_cache_size= 64M
myisam_sort_buffer_size = 64M
thread_concurrency = 8
open_files_limit=1024
Look for slow queries and other query problems
log_slow_queries=/home/mysql/errorlog/mysql_slow_query.log
long_query_time=2
Option for locking out users from the network
#skip-networking
Next, replication items:
log-bin
server-id=1
[client]
socket=/var/lib/mysql/mysql.sock