Bug #3600 innodb corruption
Submitted: 29 Apr 2004 14:13 Modified: 6 May 2004 16:18
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.18 OS:Sparc/Linux(2.4.26)
Assigned to: CPU Architecture:Any

[29 Apr 2004 14:13] [ name withheld ]
Description:
We have an curruption of a busy DB after 1 year of operation, and there seems no way to recover the data with mysqldump. Do you have any ideas how can we recover the data without restoring the backup?

when innodb_force_recovery=3

mysqldump -v  -u root -p dbmail messageblks |gzip -6 > dump.messageblks.gz
Enter password:
-- Connecting to localhost...
-- Retrieving table structure for table messageblks...
-- Warning: Couldn't get status information for table messageblks (Lost connection to MySQL server during query)
-- Sending SELECT query...
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (61) when retrieving data from server

An I have this in the log:

040429 13:37:37  mysqld started
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line
040429 13:37:40  InnoDB: Error: page 7 log sequence number 2 1975703523
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 1 log sequence number 135 3666841560
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 3 log sequence number 113 4209379016
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 2 log sequence number 132 2983105838
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 4 log sequence number 136 1479071360
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 11 log sequence number 2 1981415583
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 6 log sequence number 136 1479895146
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 53 log sequence number 136 1479895171
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 55 log sequence number 135 4283156025
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 163850 log sequence number 136 1463484648
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 163841 log sequence number 136 1310447232
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 22017 log sequence number 136 1482605869
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 16385 log sequence number 133 135953529
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 196612 log sequence number 114 264119230
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 196609 log sequence number 136 1481497621
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 16393 log sequence number 52 4216318440
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 16396 log sequence number 50 119242866
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 196616 log sequence number 49 1191797743
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:40  InnoDB: Error: page 196618 log sequence number 50 81216135
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196615 log sequence number 49 1191797773
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196617 log sequence number 50 81217510
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196619 log sequence number 50 81215859
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196620 log sequence number 50 171263273
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 163845 log sequence number 136 1463484349
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 163852 log sequence number 136 1463484249
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196624 log sequence number 37 3306664546
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196625 log sequence number 38 3286485673
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196626 log sequence number 40 4120011691
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 196627 log sequence number 40 2460566455
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 163842 log sequence number 136 1476657049
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 0 164208128
040429 13:37:41  InnoDB: Error: page 0 log sequence number 136 1480439313
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 164197876, 1 rows to undo040429 13:37:41  InnoDB: Error: page 9 log sequence number 2 1981415608
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 8 log sequence number 2 1981415613
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 10 log sequence number 2 1981415603
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 12 log sequence number 2 1981415578
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 13 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 14 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 15 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 16 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 17 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 18 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 19 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 20 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 21 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 22 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 23 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 24 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 25 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 26 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 27 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 28 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 29 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 30 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 31 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 32 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 33 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 34 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 35 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 36 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 37 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 38 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 39 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 40 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 41 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 42 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 43 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 44 log sequence number 0 37082
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 45 log sequence number 28 604478497
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 46 log sequence number 0 39530
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 47 log sequence number 0 40800
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 48 log sequence number 0 42031
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 49 log sequence number 0 43886
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 50 log sequence number 0 1683982539
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 51 log sequence number 84 2553025154
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 52 log sequence number 135 1069710115
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 54 log sequence number 0 1683982330
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 56 log sequence number 0 1683983118
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 57 log sequence number 109 1109936051
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 58 log sequence number 136 1480439313
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 59 log sequence number 136 1415296799
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 60 log sequence number 0 1683982435
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 61 log sequence number 136 1415296961
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 62 log sequence number 136 1470789070
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 63 log sequence number 135 1244689934
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 1170007 log sequence number 136 1483134604
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
040429 13:37:41  InnoDB: Error: page 1163265 log sequence number 136 1457883384
InnoDB: is in the future! Current system log sequence number 0 8866.
InnoDB: Your database may be corrupt.
InnoDB: Error: trying to access page number 832 in space 0
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
040429 13:37:41  InnoDB: Assertion failure in thread 16384 in file fil0fil.c line 1204
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=67108864
read_buffer_size=131072
max_used_connections=0
max_connections=150
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 391934 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

040429 13:37:41  mysqld ended

when innodb_force_recovery=4

same error but other entries in the log 
040429 14:02:53  mysqld started
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' ear
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Error: tablespace size stored in header is 1331200 pages, but
InnoDB: the sum of data file sizes is 1280000 pages
040429 14:02:54  InnoDB: Started
InnoDB: !!! innodb_force_recovery is set to 6 !!!
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
InnoDB: Error: trying to access a stray pointer 8d638000
InnoDB: buf pool start is at 75638000, number of pages 24576
040429 14:03:14  InnoDB: Assertion failure in thread 196618 in file ../../innoba
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=67108864
read_buffer_size=131072
max_used_connections=0
max_connections=2
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 69887
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Number of processes running now: 0
040429 14:03:15  mysqld restarted
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' ear
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Error: tablespace size stored in header is 1331200 pages, but
InnoDB: the sum of data file sizes is 1280000 pages
040429 14:03:16  InnoDB: Started
InnoDB: !!! innodb_force_recovery is set to 6 !!!
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
InnoDB: Error: trying to access a stray pointer 8d638000
InnoDB: buf pool start is at 75638000, number of pages 24576
040429 14:03:37  InnoDB: Assertion failure in thread 180234 in file ../../innoba
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=67108864
read_buffer_size=131072
max_used_connections=0
max_connections=2
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 69887
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Number of processes running now: 0
040429 14:03:37  mysqld restarted
Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' ear
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
InnoDB: Error: tablespace size stored in header is 1331200 pages, but
InnoDB: the sum of data file sizes is 1280000 pages
040429 14:03:38  InnoDB: Started
InnoDB: !!! innodb_force_recovery is set to 6 !!!
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down

TIA

Ming-Wei

How to repeat:
always
[6 May 2004 16:18] Timothy Smith
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Dear Ming-Wei,

If you have found a bug that regularly causes your database to become corrupt, then I misunderstood your report.

If you have, please submit a test case that shows exactly how to repeat this corruption.  Then we will be able to look into it for you.
[6 May 2004 16:50] [ name withheld ]
ok, I am not suer if this is a bug or not, finnaly I was able to recover most data with SELECT INTO file then insert them with LOAD ....

I was just mad because mysqldump won't dump anything but the I could do a seclect, this is a bug according to me, a dump should dump what is still dumpable.

As for innodb, the server hadn't crashed nor had mysqld, so I am suspecting the some kind of mal-formated INSERT corrupted the DB is this possible? I do no have a way to reproduce this because it is used as a mail storage.

The only unusual thing we had was maybe the the DB is getting almost full +/- 19GB of 20GB configured and suddenly there were a lot of mails arriving causing the corruption (or myabe DB was already to fragmented), is this possible?

Sorry to bother you if you do not consider this as a bug, but IMHO mysqldump should dump all the data which are not corrupted hence the report

regards

Ming-Wei