Joined: 18 Jan 2004
|Posted: 25.4.2015, 10:52 Post subject: Fix InnoDB Error page log sequence number is in the future
|This article applies to MySQL engine 5.x running InnoDB storage engine, but might be helpful also for databases with mixed type storage engines InnoDB and MyISAM.
Mostly due to hardware failure or if you have external storage for database vbe it DirectAttached, iSCSI, Fibre Optic or something similar, when connection to filesystem where MySQL log files reside is lost for a period of time beyond OS tolerance (might be for 1 second, for example), MySQL log or database open file might become corrupted in the similar way, as if hard disk would have bad sector.
The typical results are some or all of error messages:
Web site, using this MySQL server, shows something like this:
Code: Cannot connect to database. Connection to database lost.
When you look at /var/log/mysql/error.log you see errors like these:
Code: InnoDB: Error: page X log sequence number X
InnoDB: is in the future! Current system log sequence number X.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files.
When you try to run SQL query against this database, you might get:
Code: 1030: Got error -1 from storage engine
All these and some other similar errors indicate, that your MySQL database, or to be more precise, at least one of the tables in at least one of databases is corrupt beyond auto repair capability of MySQL server.
How to proceed?
First off, you must understand difference between InnoDB and MyISAM database/table engines. To cut off all technical crap:
- InnoDB is high-performance database engine, meant for transactional databases with a lot of queries, visitors etc. It has kinda roll-back logs (named ib_logfile* in /data root folder), which offer you the ability to recover from failure quite precisely. InnoDB stores data in ibdata* files in /data root folder, while privileges and table data is still in sub-folders.
- MyISAM is much simpler and still quite performer database engine, which is meant for simplicity. It does not support transactional databases, but offers quite decent and overall excellent support for all other SQL-type operations. Tables are stored in subfolders of /data/database/ folder and making a backup is as simple as stopping (or even without it) database service, copying all table *.MYD and *.MYI files and that's it.
So, if something goes wrong with MyISAM database, you must in principle just repair or restore *.MYD and *.MYI files and off you go.
While restoring InnoDB might be a nightmare, because each data must have coresponding index and transaction log in above mentioned files in /data root folder, and if log sequences do not match with data, InnoDB engine refuses to cooperate.
My usual approach to reviving InnoDB database is by rule from last good backup....but what if you do not have backup?
Well, it becomes interesting at that point :)
Last time I successfully repaired InnoDB databases after disk failure following this procedure:
First, shut down MySQL service. I assume you run Linux, which is obvious choice:
Code: service mysql stop
Then copy all content from /var/lib/mysql to another location, so to have a kind of restore point ready if you mess up too much.
Then edit my.ini and add the following parameters:
Code: vim /etc/mysql/my.cnf
port = 8881
Be careful to edit [mysqld] section!
port can be anything, but I like to have it different from CLIENT port 3306, because clients still connect to 3306, while phpMyAdmin, for example and root operations go in my case through 8881 port.
innodb_force_recovery accepts values from 0 to 6, where 0 is off (default) and numbers from 1 to 6 are turning OFF MySQL server's security checks upon startup and operation. 1 is most safe and non-dangerous, while when using 3 or more, you *might* damage data table contents if it is broken beyond repair. But this is acceptable risk, because in worst case, if you cannot rescue your data, it's practically the same as if your data is broken, so no additional harm is done by increasing value to 3, 4, 5 or 6.
If you will be using phpMyAdmin in repair process, you must adjust port setting in config.inc.php file inside root folder of phpMyAdmin web site:
Code: /* Server parameters */
$cfg['Servers'][$i]['port'] = '8881';
Start MySQL service and observe /var/log/mysql/error.log:
Code: service mysql start
Quickly check if service is running by repeating above start command, and if it says "already running", you are good to proceed.
But most probably setting innodb_force_recovery to 1 will NOT let you solve your problem, or MySQL service won't start.
So if service won't start, increase innodb_force_recovery to 2, try starting service again, and increase step by step up to 6, until MySQL service starts.
If MySQL service does not start, there's most probably a syntax error in my.ini file. Double check, what you have edited, comment out your changes to find error.
If service does not fire up, try moving all databases/files/folders from /var/lib/mysql , except of /mysql folder. Service should start up now.
then stop it, copy back 1 database by 1 and find out, which one makes your service inoperable.
When your MySQL service is running under innodb_force_recovery > 0 setting, keep in mind that you should only READ data from tables, not make any changes! The whole purpose of innodb_force_recovery parameter is to get an opportunity to export databases into SQL text file for fresh import, aka making a backup.
So, at that point you can use either phpMyAdmin, command line or any other tool to EXPORT databases into SQL text file. You can select these two additional options for easier import later:
- Add CREATE DATABASE / USE statement
- Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement
When you've got successful exports of all databases, you may proced with seizing corrupted database information:
- DROP all databases
- STOP MySQL service
- DELETE all database folders from /var/lib/mysql/database_name, except of /mysql folder; the purpose of leaving /mysql folder intact is that there's quite low probability it is corrupted, and it also enables us to log-in with existing root credentials
- DELETE all InnoDB logs (ib_logfile*) and data (ibdata*) files from /var/lib/mysql folder
- CHANGE innodb_force_recovery parameter to 0 or comment it out in my.ini file
- START MySQL service
- LOGIN either via phpMyAdmin or use other tools
- now, IMPORT databases using SQL text files, which you created earlier (or from your previous backups), one by one
If import fails with some DUPLICATES problem in phpMyAdmin, you can go into SETTINGS --> SQL QUERIES and turn ON parameter Ignore multiple statement errors
- TEST how database performs after each imported database
Most probably, you will end up with healthy and functional MySQL server, restored without having proper backup. At least I did :)