MySQL Bugs: #1716: InnoDB: Database page corruption on disk or a failed

Bug #1716	InnoDB: Database page corruption on disk or a failed
Submitted:	30 Oct 2003 13:08	Modified:	4 Nov 2003 7:34
Reporter:	Vlad S	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.13	OS:	FreeBSD (FreeBSD 4.7-RELEASE)
Assigned to:		CPU Architecture:	Any

Description:
We have a large growing database (2,5GB gzipped backup). We got the following error:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1294396.
InnoDB: You may have to recover from a backup.
031030  5:33:50  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 000000000013c03c0000 ......
....
....
InnoDB: End of page dump
031030  5:33:50  InnoDB: Page checksum 593011384 stored checksum 4247314682
InnoDB: Page lsn 4 923013937, low 4 bytes of lsn at page end 926290737
InnoDB: Page may be an index page where index id is 0 40
InnoDB: and table search2/site index site_ind6
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1294396.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: Look also at section 6.1 of
InnoDB: http://www.innodb.com/ibman.html about
InnoDB: forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
031030 05:33:50  mysqld restarted
031030  5:33:51  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 4 1665633931
InnoDB: Doing recovery: scanned up to log sequence number 4 1666487177
InnoDB: Error: trying to access page number 4286563839 in space 0
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
031030  5:33:51  InnoDB: Assertion failure in thread 138440704 in file fil0fil.c line 1176
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to mysql@lists.mysql.com
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=8388608
read_buffer_size=2093056
sort_buffer_size=0
max_used_connections=0
max_connections=50
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 110392 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

031030 05:33:51  mysqld ended

I need to delete all INNODB files and recover database from a backup. This process lasts about 3 hours.
Everything was going fine during last 5 days (5 days ago i tried to install the 4.0.16, but after some period of time it started restarting every 10 minutes - also because of INNODB errors, the 4.0.15 has the same problem; and I turned back to 4.0.13 which works fine).

Our mysqld section from the config file (note, that i have to start the second mysqld on the different port to work only with this large database):

[mysqld]
skip-name-resolve
port            = 3307
socket          = /tmp/mysql2.sock
datadir=/data/db2
default-character-set=cp1251
myisam-recover=BACKUP,FORCE
##skip-locking

# look out for innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + read_b
uffer_size) + max_connections * 2 MB < 2G or total mem !!!

##############MyISAM settings only
set-variable    = key_buffer=8M
set-variable    = myisam_sort_buffer_size=64M

##############common with InnoDB-related
set-variable    = read_buffer_size=2M
set-variable    = read_rnd_buffer_size=512k
set-variable    = join_buffer_size=1M
set-variable    = sort_buffer=3M

set-variable    = max_connection=50
set-variable    = max_allowed_packet=16M
set-variable    = tmp_table_size=16M
set-variable    = table_cache=999
set-variable    = thread_cache=16
# Try number of CPU's*2 for thread_concurrency
set-variable    = thread_concurrency=2
##log-bin
server-id       = 2
max_connect_errors = 100

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

#Turning on query caching
query_cache_size = 32M
query_cache_type = 1

# Uncomment the following if you are using BDB tables
skip-bdb
#set-variable   = bdb_cache_size=384M
#set-variable   = bdb_max_lock=100000

# Uncomment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = /data/innodb2/db/
innodb_data_file_path = ibdata1:1500M;ibdata2:1500M;ibdata3:1500M;ibdata4:1500M;ibdata5:1500M
;ibdata6:1500M;ibdata7:1500M;ibdata8:1500M;ibdata9:1500M;ibdata10:1500M;ibdata11:1500M;ibdata
12:1500M;ibdata13:1500M;ibdata14:1500M;ibdata15:1500M;ibdata16:1500M;ibdata17:1500M;ibdata18:
1500M;ibdata19:1500M;ibdata20:1500M;ibdata21:1500M;ibdata22:1500M
innodb_log_group_home_dir = /data/innodb2/log/
innodb_log_arch_dir = /data/innodb2/log/
innodb_log_archive=0
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=300M
set-variable = innodb_additional_mem_pool_size=10M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
set-variable = innodb_file_io_threads=4
#for recovery
#set-variable = innodb_force_recovery=6

Our system hardware: Pentium 4 2,53/1 GB DDR RAM/2x120GB RAID

FreeBSD kernel recompiled with the following options:
options         MAXDSIZ="(512*1024*1024)"
options         MAXSSIZ="(512*1024*1024)"
options         DFLDSIZ="(256*1024*1024)"

How to repeat:
Create a large database with indexes and try insertions and selections from 5-10 clients concurently. It seems to me heavy I/O load can occur this error.

Suggested fix:
I don't believe that upgrading mysql to the last version will help, there is a  faultiness in INNODB.

Due to the table corruption bugs which have been fixed in subsequent versions, you should probably upgrade to 4.0.16 and attempt to resolve (or identify) the issues you were having with it in order to determine whether or not you are experiencing one of the bugs which have in fact been corrected.

I tried to upgrade to 4.0.16. I deleted all old INNODB files and logs and started to recover database from backup, but after some time it failed with the following error:

031026 14:25:00  mysqld started
031026 14:25:00  InnoDB: Started
/usr/local/mysql-max-4.0.16-unknown-freebsd4.7-i386/bin/mysqld: ready for connections.
Version: '4.0.16-max'  socket: '/tmp/mysql2.sock'  port: 3307
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
031026 15:33:49  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 215c ....
....
....
InnoDB: End of page dump
031026 15:33:49  InnoDB: Page checksum 3084444227, prior-to-4.0.14-form checksum 4254121785
InnoDB: stored checksum 559691427, prior-to-4.0.14-form stored checksum 4254121785
InnoDB: Page lsn 2 1867019429, low 4 bytes of lsn at page end 1867019429
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: Look also at section 6.1 of
InnoDB: http://www.innodb.com/ibman.html about
InnoDB: forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

Number of processes running now: 1
mysqld process hanging, pid 21425 - killed
031026 15:33:50  mysqld restarted
031026 15:33:51  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 2 2121101759
InnoDB: Doing recovery: scanned up to log sequence number 2 2126344192
InnoDB: Doing recovery: scanned up to log sequence number 2 2131587072
InnoDB: Doing recovery: scanned up to log sequence number 2 2136829952
InnoDB: Doing recovery: scanned up to log sequence number 2 2142072832
InnoDB: Doing recovery: scanned up to log sequence number 2 2147315712
InnoDB: Doing recovery: scanned up to log sequence number 2 2152558592
InnoDB: Doing recovery: scanned up to log sequence number 2 2157801472
InnoDB: Doing recovery: scanned up to log sequence number 2 2163044352
InnoDB: Doing recovery: scanned up to log sequence number 2 2168287232
InnoDB: Doing recovery: scanned up to log sequence number 2 2173530112
InnoDB: Doing recovery: scanned up to log sequence number 2 2178772992
InnoDB: Doing recovery: scanned up to log sequence number 2 2184015872
InnoDB: Doing recovery: scanned up to log sequence number 2 2189258752
InnoDB: Doing recovery: scanned up to log sequence number 2 2194501632
InnoDB: Doing recovery: scanned up to log sequence number 2 2199744512
InnoDB: Doing recovery: scanned up to log sequence number 2 2204987392
InnoDB: Doing recovery: scanned up to log sequence number 2 2210230272
InnoDB: Doing recovery: scanned up to log sequence number 2 2215473152
InnoDB: Doing recovery: scanned up to log sequence number 2 2220716032
InnoDB: Doing recovery: scanned up to log sequence number 2 2225958912
031026 15:33:57  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 2
8 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 6
3 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 9
8 99
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 2 2231201792
InnoDB: Doing recovery: scanned up to log sequence number 2 2234060819
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
031026 15:34:32  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex ....
....
....InnoDB: End of page dump
031026 15:34:32  InnoDB: Page checksum 3084444227, prior-to-4.0.14-form checksum 4254121785
InnoDB: stored checksum 559691427, prior-to-4.0.14-form stored checksum 4254121785
InnoDB: Page lsn 2 1867019429, low 4 bytes of lsn at page end 1867019429
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: Look also at section 6.1 of
InnoDB: http://www.innodb.com/ibman.html about
InnoDB: forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
031026 15:34:32  mysqld ended

There was no connections and no additional operations to the mysqld daemon during recovery. Mysqld worked only with recovery script which was recovering all tables in a consecutive order.

What is your recovery script using?  The output of mysqldump for your tables, or are you copying older InnODB tablespace files into your data directory?

I deleted old innodb files (tablespace) and created new ones. After I run my recovery script.
The recovery script is quite simple - it takes every dumped (by mysqldump) table and insert it to the new database:

#!/usr/bin/perl

$DIR=$ARGV[0];

while ($nn=<$DIR/*>) {
system ("gzip -cd $nn | mysql -S /tmp/mysql2.sock -uroot -p##### storage")
}

I started it with the next syntax:
perl restore_many.pl /data/db_backup/2003-10-25/storage

where /data/db_backup/2003-10-25/storage contains 220 mysqldump gzipped tables.

I get 4.0.13 back. It's working for a 24 hours with no errors, but unfortunately I expect them in the nearest future:(

Hi!

The printout shows that the page header differs from the page end. This is probably a problem in the OS/drivers/hardware, not a mysqld bug.

Please test on another computer.

Regards,

Heikki