InnoDB table recovery problem - not beeing able to start database server
Ivan Tomasic 30 June 2003 13:10:08
Hi.
Please help.
During long delete operation from one InnoDB table there was PC shutdown due to power supply failure. After that I was unable to start database server so I have started it with set-variable = innodb_force_recovery=4 option and I have dropped all databases with InnoDB tables. Still I am unable to start database server normally. Following is the part of Err file:
030630 11:58:36 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 0 3895685648 InnoDB: Doing recovery: scanned up to log sequence number 0 3895685632 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1777810 row operations to undo InnoDB: Trx id counter is 0 22060800 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 22060495, 1777810 rows to undo InnoDB: Progress in percents: 1InnoDB: Dump of the tablespace extent descriptor: len 40; hex 0000000000000095ffffffff0000ffffffff000000000004aaaaaaaaaaaaaaaaaa aaaaaafeffffff; asc .......ДЅ................¬¬¬¬¬¬¬¬¬¬¬¬в–...; InnoDB: Serious error! InnoDB is trying to free page 32306 InnoDB: though it is already marked as free in the tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB: You may need to dump your InnoDB tables and recreate the whole InnoDB: database! InnoDB: If the InnoDB recovery crashes here, see section 6.1 InnoDB: of http://www.innodb.com/ibman.html about forcing recovery. 030630 11:58:37 InnoDB: Assertion failure in thread 580 in file D:\mysql-4.0.12 \innobase\fsp\fsp0fsp.c line 2689 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 030630 11:58:37 mysqld-max-nt: Got signal 11. Aborting!
030630 11:58:37 Aborting
030630 11:58:37 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030630 11:58:37 mysqld-max-nt: Shutdown Complete
if you have already dumped all InnoDB tables, then there is no need to keep ibdata files and ib_logfiles. You can delete them and recreate them from scratch and reimport the tables.
But in the printout which you pasted below you do not have innodb_force_recovery set to 4?
When I test 4.0.14, I get a printout which tells the option is set to 4:
" C:\mysql-4.0.13\mysql-4.0.13\client_debug>mysqld --console 030630 16:49:40 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 16 1350394126 InnoDB: Doing recovery: scanned up to log sequence number 16 1350394126 030630 16:49:41 InnoDB: Flushing modified pages from the buffer pool... 030630 16:49:41 InnoDB: Started InnoDB: !!! innodb_force_recovery is set to 4 !!! mysqld: ready for connections "
Note that innodb_force_recovery only helps you to dump your tables. I have intentionally blocked inserts and updates if innodb_force_recovery > 0, because it is not a good idea to use a corrupt database.
If the disk or the Windows OS lies to InnoDB about when it really has written a page to disk, you can easily get this kind of corruption at a power outage. The free page bitmap can be out-of-sync with other data structures in the tablespace.
................ Subject: InnoDB table recovery problem - not beeing able to start database server From: Ivan Tomasic Date: Mon, 30 Jun 2003 12:10:08 +0200
Hi.
Please help.
During long delete operation from one InnoDB table there was PC shutdown due to power supply failure. After that I was unable to start database server so I have started it with set-variable = innodb force recovery=4 option and I have dropped all databases with InnoDB tables. Still I am unable to start database server normally. Following is the part of Err file:
030630 11:58:36 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 0 3895685648 InnoDB: Doing recovery: scanned up to log sequence number 0 3895685632 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1777810 row operations to undo InnoDB: Trx id counter is 0 22060800 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 22060495, 1777810 rows to undo InnoDB: Progress in percents: 1InnoDB: Dump of the tablespace extent descriptor: len 40; hex 0000000000000095ffffffff0000ffffffff000000000004aaaaaaaaaaaaaaaaaa aaaaaafeffffff; asc .......Ľ................¬¬¬¬¬¬¬¬¬¬¬¬â- ...; InnoDB: Serious error! InnoDB is trying to free page 32306 InnoDB: though it is already marked as free in the tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB: You may need to dump your InnoDB tables and recreate the whole InnoDB: database! InnoDB: If the InnoDB recovery crashes here, see section 6.1 InnoDB: of http://www.innodb.com/ibman.html about forcing recovery. 030630 11:58:37 InnoDB: Assertion failure in thread 580 in file D:\mysql-4.0.12 \innobase\fsp\fsp0fsp.c line 2689 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 030630 11:58:37 mysqld-max-nt: Got signal 11. Aborting!
030630 11:58:37 Aborting
030630 11:58:37 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030630 11:58:37 mysqld-max-nt: Shutdown Complete
Hi Ivan, you may try the the mssql fix program, it can be found here: http://www.recoverytoolbox.com/fix_mdf_file_sql_server.html. Recovery Toolbox for SQL Server quickly fixes database errors. It seems to be the easiest way because the root cause is not known
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
