We help IT Professionals succeed at work.

MYSQL, what will be impact to have innodb_force_recovery is set to 1 always in MYSQL.ini

motioneye
motioneye asked
on
Medium Priority
3,319 Views
Last Modified: 2012-05-06
I have wrongly configure my multiple ibd file to wrong folder,eventually after copy the file and put in correct folder, mysql not able to start and returned me as following errors. I have managed to restart the with enabled innodb_force_recovery=1. Do I need to always have this section enabled in MYSQL?


C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090217 13:23:07  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 5627803, file name C:\MysqlbinLog\FFTSBinLog.000044
InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.
090217 13:23:08 [ERROR] Plugin 'InnoDB' init function returned error.
090217 13:23:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 13:23:08 [ERROR] Unknown/unsupported table type: INNODB
090217 13:23:08 [ERROR] Aborting

090217 13:23:08 [Note] mysqld: Shutdown complete


C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
090217 13:33:38  InnoDB: Started; log sequence number 0 530145771
InnoDB: !!! innodb_force_recovery is set to 1 !!!
090217 13:33:39 [Note] Event Scheduler: Loaded 0 events
090217 13:33:39 [Note] mysqld: ready for connections.
Version: '5.1.29-rc-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
Comment
Watch Question

Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
>>I have wrongly configure my multiple ibd file to wrong folder,eventually after copy the file and put in correct folder, mysql not able to start and returned me as following errors. I have managed to restart the with enabled innodb_force_recovery=1. Do I need to always have this section enabled in MYSQL?

The database must not otherwise be used with any non-zero value of innodb_force_recovery. As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

I suggest you to read this once..

http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hmm, how do we identiy corrupted tables/objects ?? and how to delete if innodb_force_recovery > 0
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
>>Hmm, how do we identiy corrupted tables/objects ?? and how to delete if innodb_force_recovery > 0

From error log you would notice if something goes wrong(page corrupt etc)...  if at all it states any such things... You need to take dump(SQL dump using mysqldump) when innodb_force_recovery > 0 ..   and restore ...

Author

Commented:
take dump and restore and restart db with innodb_force_recovery =0 and returned as previous error msgs



-- Dump completed on 2009-02-17  8:40:21

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.
090217 16:47:10 [ERROR] Plugin 'InnoDB' init function returned error.
090217 16:47:10 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 16:47:10 [ERROR] Unknown/unsupported table type: INNODB
090217 16:47:10 [ERROR] Aborting

090217 16:47:10 [Note] mysqld: Shutdown complete
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.

Did you see the above Errosr are very much self explanatory... it seems you have assigned more 156MB.. I guess you need to set it 156 MB..

Author

Commented:
see the sequence of msgs return on each restart with difft size changed in config file.



C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: Error: data file E:\MySQLData\DataFFTS\ibdata1 is of a different size
InnoDB: 9344 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 9600 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
090217 17:28:49 [ERROR] Plugin 'InnoDB' init function returned error.
090217 17:28:49 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 17:28:49 [ERROR] Unknown/unsupported table type: INNODB
090217 17:28:49 [ERROR] Aborting

090217 17:28:49 [Note] mysqld: Shutdown complete


C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: Error: data file E:\MySQLData\DataFFTS\ibdata1 is of a different size
InnoDB: 9344 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 9408 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
090217 17:29:27 [ERROR] Plugin 'InnoDB' init function returned error.
090217 17:29:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 17:29:27 [ERROR] Unknown/unsupported table type: INNODB
090217 17:29:27 [ERROR] Aborting

090217 17:29:28 [Note] mysqld: Shutdown complete


C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld  --console
InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.
090217 17:29:54 [ERROR] Plugin 'InnoDB' init function returned error.
090217 17:29:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 17:29:54 [ERROR] Unknown/unsupported table type: INNODB
090217 17:29:54 [ERROR] Aborting

090217 17:29:54 [Note] mysqld: Shutdown complete


C:\Program Files\MySQL\MySQL Server 5.1\bin>

UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
Pls post these...

CD to MySQL data dir and send the otput of below command (I'm intesretd to know InnoDB datafile size)

dir


and my.ini details(related to innodb specific settings)

Author

Commented:
see this one


E:\MySQLData\DataFFTS\>dir
 Volume in drive C is PC COE
 Volume Serial Number is 8483-D1E1

 Directory of E:\MySQLData\DataFFTS\

02/17/2009  01:18 PM    <DIR>          .
02/17/2009  01:18 PM    <DIR>          ..
02/17/2009  05:27 PM       153,092,096 ibdata1
02/17/2009  05:27 PM        10,485,760 ibdata2
               2 File(s)    163,577,856 bytes
               2 Dir(s)  43,838,808,064 bytes free






#*** INNODB Specific options ***


# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=2M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=5M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=47M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=24M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=8
#Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
innodb_mirrored_log_groups=1
#Number of log files in the log group. InnoDB writes to the files in a circular fashion. Value 2 is recommended here. The default is 2.
innodb_log_files_in_group=2
#File that holds the names for last binary log files.
log-bin=C:/MysqlbinLog/FFTSBinLog
#Binary log will be rotated automatically when the size exceeds this value. Will also apply to relay logs if max_relay_log_size is 0.
#The minimum value for this variable is 4096.
max_binlog_size=10M
#Enter a name for the error log file. Otherwise a default name will be used.
#log-error=c:/Program Files/MySQL/MySQL Server 5.0/data/errors.log
#Enter a name for the slow query log. Otherwise a default name will be used.
#log-slow-queries=c:/Program Files/MySQL/MySQL Server 5.0/data/slow.log
#Log some not critical warnings to the log file.
log-warnings
#Logs will be rotated after expire-log-days days
#expire_logs_days=3
#Directory path to InnoDB log files.
innodb_log_group_home_dir=C:/Program Files/MySQL/MySQL Server 5.1/Log
#This option makes InnoDB to store each created table into its own .ibd file.
innodb_file_per_table
#This value should currently be disabled.
#innodb_log_archive=0
#Directory path where log archives should be stored.
#innodb_log_arch_dir=C:/MySQLBinLOg/FFTSArchive
#Tells the master that updates to the given database should not be logged to the binary log.
#binlog-ignore-db=FDES
innodb_data_home_dir =
innodb_data_file_path=E:/MySQLData/DataFFTS/ibdata1:146M;F:/MySQLData/DataFFTS/ibdata2:10M:autoextend
#innodb_data_file_path=ibdata1:10M:autoextend
innodb_force_recovery=0
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
My observations ...


E:\MySQLData\DataFFTS\>dir
 Volume in drive C is PC COE
 Volume Serial Number is 8483-D1E1

 Directory of E:\MySQLData\DataFFTS\

02/17/2009  01:18 PM    <DIR>          .
02/17/2009  01:18 PM    <DIR>          ..
02/17/2009  05:27 PM       153,092,096 ibdata1
02/17/2009  05:27 PM        10,485,760 ibdata2
               2 File(s)    163,577,856 bytes
               2 Dir(s)  43,838,808,064 bytes free


### E:/MySQLData/DataFFTS/ibdata1:146M;F:/MySQLData/DataFFTS/ibdata2:10M:autoextend

153,092,096 ibdata1 => 146MB correct

10,485,760  ibdata1 => 10MB correct

But from config its pointing to F: drive and you are showing it from E:/ is this right?

Author

Commented:
Hi
I have below files in disk as per configured in config file, its multiple data files, the initial IBD file before moved was ibdata1.

<innodb_data_file_path=E:/MySQLData/DataFFTS/ibdata1:146M;F:/MySQLData/DataFFTS/ibdata2:10M:autoextend>
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
Donno how I missed to check this question/comment...

InnoDB errors says

InnoDB: Error: tablespace size stored in header is 10624 pages, but
InnoDB: the sum of data file sizes is only 9984 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.
090217 17:29:54 [ERROR] Plugin 'InnoDB' init function returned error.
090217 17:29:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
090217 17:29:54 [ERROR] Unknown/unsupported table type: INNODB

Pls start MySQL with set innodb_force_recovery=1 and see if you can recover the problem noted in error log.
UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
BTW, Did you manually created the second data file? MySQL creates on its own..and if you ahve created then it would create problems..

F:/MySQLData/DataFFTS/ibdata2:10M:autoextend
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
I tried this at home and its just worked for me.. donno why you are having problem.. Pls let me know I want to know why its creating problem.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.