?
Solved

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

Posted on 2009-02-16
13
Medium Priority
?
3,129 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)
0
Comment
Question by:motioneye
  • 8
  • 5
13 Comments
 
LVL 26

Accepted Solution

by:
Umesh earned 1500 total points
ID: 23656618
>>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
 

Author Comment

by:motioneye
ID: 23656952
Hmm, how do we identiy corrupted tables/objects ?? and how to delete if innodb_force_recovery > 0
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23656974
>>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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:motioneye
ID: 23657323
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
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23657513
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
 

Author Comment

by:motioneye
ID: 23657612
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
 
LVL 26

Expert Comment

by:Umesh
ID: 23658023
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
 

Author Comment

by:motioneye
ID: 23658173
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
 
LVL 26

Expert Comment

by:Umesh
ID: 23658244
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
 

Author Comment

by:motioneye
ID: 23658340
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
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23678135
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
 
LVL 26

Expert Comment

by:Umesh
ID: 23709452
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
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1500 total points
ID: 23718427
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question