• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3174
  • Last Modified:

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

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)
0
motioneye
Asked:
motioneye
  • 8
  • 5
5 Solutions
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>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
0
 
motioneyeAuthor Commented:
Hmm, how do we identiy corrupted tables/objects ?? and how to delete if innodb_force_recovery > 0
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>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 ...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
motioneyeAuthor 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
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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..

0
 
motioneyeAuthor 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>

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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)
0
 
motioneyeAuthor 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
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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?
0
 
motioneyeAuthor 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>
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now