MySQL InnoDB Error: “log sequence number is in the future”

When attempting to use InnoDB after a recovery, the following error occurs.

ERROR
-----------------------
120207 13:30:29 InnoDB: Error: page 573441 log sequence number 22 697197707
InnoDB: is in the future! Current system log sequence number 5 2916730276.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-recovery.html
InnoDB: for more information.

CHANGES

This issue will usually happen after one of the following changes:

• If the InnoDB log files were deleted and one or more of these events preceded that deletion (none of the events can on their own cause the error – it takes both one of the following events and deleting the log files):
• The hardware server crashed.
• The mysqld process was terminated with kill -9 or another reason.
• The data was restored from a hot backup.MySQL was shut down using while innodb_fast_shutdown = 2.
• MySQL was restored from a backup where the InnoDB log files did not exist or is from a different backup.
• MySQL was restored from an inconsistent backup.
• MySQL was started with innodb_force_recovery = 6.

CAUSE

The underlying issue is that the InnoDB log files (the redo log) is out of sync with the data files.

When changes are made in InnoDB they are written to the log files before the changes are written to the actual data files. During a normal shutdown with innodb_fast_shutdown = 0 or 1 (default) InnoDB writes a checkpoint position that is later than all positions in every page because it flushes them all then writes a checkpoint before the server shuts down. When innodb_fast_shutdown = 2 it writes the position it’s reached as a checkpoint but doesn’t do any flushing to advance all pages to their current position, so crash recovery will be needed. This crash recovery will not directly cause this error but if the log files are removed it can cause it, just as removing them after a crash can.

InnoDB compares the position in the page with the log file position when it reads the page from disk and issues this error message then.

The pages with log positions greater than the current position will not have crash recovery used on them until the current log position is greater than the position recorded in them. This means that crash recovery cannot be relied upon to work correctly until the problem is fixed.

If the cause is related to starting MySQL with innodb_force_recovery = 6, this will be clearly visible in the MySQL error log. For more information see also Starting InnoDB on a Corrupted Database.

SOLUTION

Choose one of the following solutions:

1. If you still have them, restore the original log files from the backup or wherever you copied the data files from. For cases where no changes have been made after the issue started, this will recover all data.
2. Use mysqldump to backup the data, delete all ib_logfile*, ibdata*, *.ibd, and *.frm of all InnoDB tables; recreate the InnoDB tables from the dump. Some data loss will usually happen in this case.
3. Convert all InnoDB tables to MyISAM, delete all ib_logfile*, ibdata*, restart MySQL, and convert the tables back to InnoDB. The effect will be the same as for Solution 2, so in this case some data loss is also to be expected.
4. Create a “work table” using the InnoDB storage engine and insert data into it that has a total size that is the same as the total size of your log files. Then drop this work table. Some data loss will usually happen in this case.

Solutions 2. and 3. can be combined. The important thing is to ensure there are no InnoDB tables left. Combining Solutions 2. and 3. can for example allow you to convert InnoDB tables to MyISAM where no InnoDB specific information is lost and the rest of the tables can be backed up using mysqldump and restored after the restart.

To choose between Solutions 2., 3., and 4. you should normally choose the approach that will complete in the shortest period of time, however there is a slightly higher possibility of undetected problems if Solution 4 is used, so it may be preferred to use one of the other two methods.

1. Restoring the Original Log Files

This will also get the data from any committed and uncommitted transactions that are missing from the log files in use, so it is much preferable to later options that will cause some data loss. This option should be used first unless there have been data changes since the error started happening. In that case it may be least damaging to accept the loss of the older work and retain the new work.

2. Re-Initializing InnoDB Using Transportable Tablespaces

In MySQL 5.6 and later, you can use the following steps to re-initialize InnoDB without having to reload all of the data or convert it back from MyISAM:

This method only works if:

All tables are stored in their own tablespace (innodb_file_per_table = 1).

If MySQL 5.6 there are no partitioned tables.

Make sure no changes are made to the database!

For each InnoDB table (you can do more than one table at a time):

Flush the table for export, for example to flush all of the InnoDB tables in the mysql schema in MySQL 5.7 for export:

mysql> FLUSH TABLES mysql.engine_cost, mysql.gtid_executed, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.help_topic, mysql.innodb_index_stats, mysql.innodb_table_stats, mysql.plugin, mysql.server_cost, mysql.servers, mysql.slave_master_info, mysql.slave_relay_log_info, mysql.slave_worker_info, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type FOR EXPORT;
Query OK, 0 rows affected (0.17 sec)

Copy the .cfg and .ibd files for the table to a safe location.

If you have any tablespaces encrypted using Transparent Data Encryption (TDE) there will also be a .cfp file for each encrypted table; it is very important that the .cfp file is also copied as otherwise it will not be possible to import the table again!

Unlock the table:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.02 sec)

If necessary, get all of the CREATE TABLE, CREATE TRIGGER, etc. statements for the InnoDB tables. You can for example export all of the required CREATE statements for the world sample database using mysqldump like:

shell$ mysqldump –no-data –triggers –set-gtid-purged=OFF world > export/world.sql

The above assumes all tables in the specified schema are InnoDB. If not, you need to use the –tables option and explicitly list the tables to include. For example for the mysql schema (the list of tables is from MySQL 5.7):

shell$ mysqldump –defaults-file=etc/my.cnf –no-data –triggers –set-gtid-purged=OFF –tables mysql \
   engine_cost gtid_executed help_category help_keyword help_relation help_topic \
   innodb_index_stats innodb_table_stats plugin server_cost servers \
   slave_master_info slave_relay_log_info slave_worker_info \
   time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type > export/mysql.sql

Shut down MySQL.

Remove all of the InnoDB specific files. The location and name of the files will depend on your MySQL configuration; the default is that all of the files are located under the MySQL data directory as configured with the datadir option. You must remove the following files:

1. The shared tablespace files (per default named ibdata*).
2. The InnoDB log files (the redo log) – ib_logfile*.
3. The table definition files, *.frm, for the InnoDB tables.
4. The trigger files (*.TRN and *.TRG) for the InnoDB tables.
5. The InnoDB data files, *.ibd.

If this is a replication slave, make sure the skip-slave-start option is enabled.

Start MySQL.

This will cause some errors, particularly in MySQL 5.7 and later, due to missing tables in the mysql schema. Those can be ignored for now. The important thing is that no errors are reported when restarting and using MySQL after the whole repair has been completed.

Re-create the tables using the mysqldump files from step 3. or an existing backup. It is recommended to start with the tables from the mysql schema as this will reduce the number of warnings generated in the error log for the remaining schemata.

Import the tablespace files from the copy made in step 2. The steps for each table are:

If the table is either the child or parent of one or more foreign keys, disable foreign keys for the session:

mysql> SET foreign_key_checks = OFF;
Query OK, 0 rows affected (0.00 sec)

Discard the existing tablespace, for example:

mysql> ALTER TABLE world.City DISCARD TABLESPACE;
Query OK, 0 rows affected (0.02 sec)

Copy the .cfg, .ibd, and if it exists (for encrypted tablespaces) the .cfp files for the table into the @@datadir/<schema_name>/ directory. For example for the world.City table if backup files from step 2. are located in /data/export/world/ and MySQL’s datadir is /var/lib/mysql:

shell$ cp /data/export/world/City.* /var/lib/mysql/world/

Import the tablespace:

mysql> ALTER TABLE world.City IMPORT TABLESPACE;
Query OK, 0 rows affected (0.09 sec)

If necessary enable foreign key checks again:

mysql> SET foreign_key_checks = ON;
Query OK, 0 rows affected (0.00 sec)

For an example how to automate the step, see also Note 2176971.1.

At this point the LSN numbers are consistent again. It is however recommended to restart MySQL and verify no errors are present in the MySQL error log after the import was completed in step 9. For details how to locate the MySQL error log see also Note 1478045.1.

Reloading the Data

Reloading the data after re-initializing all of the InnoDB files ensure everything is back to a consistent state.

The steps are:

Dump all the InnoDB data. You can do this with mysqldump or SELECT … INTO OUTFILE. Be sure that you also include the schema information such as the table definition and triggers. You do not need to include tables using other storage engines.

Shutdown MySQL.

Remove all of the InnoDB specific files. The location and name of the files will depend on your MySQL configuration; the default is that all of the files are located under the MySQL data directory as configured with the datadir option. You must remove the following files:

• The shared tablespace files (per default named ibdata*).
• The InnoDB log files (the redo log) – ib_logfile*.
• The table definition files, *.frm, for the InnoDB tables.
• The trigger files (*.TRN and *.TRG) for the InnoDB tables.
• The InnoDB data files, *.ibd.

Start MySQL and wait for InnoDB to re-initialize. You can see in the MySQL error log when InnoDB has finished creating the new shared table space and the log files.

Reload the data and schema for the InnoDB files.

Convert to MyISAM

Converting the InnoDB tables to MyISAM will allow you to remove the InnoDB shared table space and log files as in Solution 2 while avoiding to mysqldump the data and restore it. Particularly with MySQL 5.1 using the InnoDB Plugin and in MySQL 5.5 and later, you can take advantage of the fast index creation to speed up this process.

The steps are:

For each InnoDB table convert the table to MyISAM – Options: If you are using InnoDB Plugin in 5.1 or MySQL 5.5 and later, remove all the secondary indexes from the table as well.

Removing the secondary indexes is a performance optimization, so it is optional to perform that part of the step. The most significant gain will be for large tables.

For example:
ALTER TABLE t1
DROP INDEX val1,
DROP INDEX val2,
ENGINE=MyISAM;

Stop MySQL.

Remove all of the InnoDB specific files. The location and name of the files will depend on your MySQL configuration; the default is that all of the files are located under the MySQL data directory as configured with the datadir option. You must remove the following files:

The shared tablespace files (per default named ibdata*).

The InnoDB log files (the redo log) – ib_logfile*.

Start MySQL and wait for InnoDB to re-initialize. You can see in the MySQL error log when InnoDB has finished creating the new shared table space and the log files.

For each table:

Convert the table back to InnoDB and add back any unique indexes that was removed in step 1. You should do this at this stage as unique indexes cannot take advantage of the fast index creation, so adding them later will cause a full table rebuild:

ALTER TABLE t1
ADD UNIQUE (val1),
ENGINE=InnoDB;

Add the secondary indexes back on the table:

ALTER TABLE t1
ADD INDEX (val2);

Work Table

This workaround will bring the current log sequence back above the sequence number that is reported in the future. You can estimate the amount of data you need to insert by using the difference between the two sequence numbers:

120207 13:30:29 InnoDB: Error: page 573441 log sequence number 22 697197707
InnoDB: is in the future! Current system log sequence number 5 2916730276.

The numbers are in the same format as for the LOG section in SHOW ENGINE INNODB STATUS (see Document 1326051.1 for an example of how to use the log sequence numbers in calculations), so the difference between the two becomes:

data required = ((22 – 5) * 4 * 1024 * 1024 * 1024) + (697197707 – 2916730276) = 70794911463 bytes

which is around 66GB.

Note: When using MySQL 5.1 with the InnoDB Plugin or MySQL 5.5 and later, the log sequence numbers will consist of one 64-bit number instead of two 32-bit numbers.

To implement the solution, create a table and keep inserting data until the system log sequence numbers had caught up with the log sequence number. When you are done, you can drop the table again.

Here is an example script for linux which will advance the log sequence number by approximately 20M:

#!/bin/sh

mysql -uroot test <<EOF
DROP TABLE IF EXISTS temp_advance_lsn;
CREATE TABLE temp_advance_lsn (col1 TEXT) ENGINE=InnoDB;
INSERT INTO temp_advance_lsn VALUES (REPEAT(‘0123456789’, 1000));
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
INSERT INTO temp_advance_lsn SELECT * FROM temp_advance_lsn;
DROP TABLE temp_advance_lsn;
EOF

mysql -uroot -e ‘SHOW ENGINE INNODB STATUS;’ \
| sed ‘s/\\n/\n/g’ \
| grep ‘^Log sequence’

If you need to advance the log sequence number (LSN) by more than 20M, then do not drop the table but TRUNCATE it instead. Then repeat the process of adding data and truncation until you reach your LSN target value.  You can do more or less than 20M in each pass, this was just an example of how to quickly create a significant quantity of data in relatively few commands.  Create then destroy any size block of data that works most efficiently for your situation.

6. Advanced Methods

If none of the above solutions can be used, for example because the LSN numbers are several terabytes out of sync and there is too much data to re-initialize MySQL, there are some advanced options available that in some cases can be used. However even greater care than above should be used, so please create a Service Request to discuss the options.