How to move the InnoDB log sequence number (LSN) forward

This post focuses on the problem of the InnoDB log sequence number being in the future.

Preface: What is an InnoDB log sequence number?

The log sequence number (LSN) is an important database parameter used by InnoDB in many places. The most important use is for crash recovery and buffer pool purge control.

Internally, the InnoDB LSN counter never goes backward. And, when InnoDB writes 50 bytes to the redo logs, the LSN increases by 50 bytes. As such, we can count LSN in megabytes, gigabytes and etc.

Now for the problem: LSN being in the future!

When you have set innodb_force_recovery like this:

innodb_force_recovery=6

and then issue a data affecting query.

For example, if you are dropping a corrupted table after doing a mysqldump for backup purposes, InnoDB will save an incorrect LSN to ibdata1 and you will have an error message in the mysqld error log after each server restart:

120323 4:38:52 InnoDB: Error: page 0 log sequence number 6094071743825
InnoDB: is in the future! Current system log sequence number 10000.


The solution: some methods to change the LSN

Usually the safest method to fix the LSN is to insert/delete the required amount of data.

But what if an old LSN was several TB? Several options are available;

1. Use your backup
2. Convert all tables to myisam, remove ibdata1 & ib_logfile*, after server restart, convert all tables back to InnoDB
3. mysqldump/restore
4. Black magic if you have a huge database

If you can’t use methods a-c the only way to get correct LSN is make some unsafe step,
like change innodb files or modify mysqld memory:

1. Make sure that you have the debuginfo package installed
2. No queries should be executed (at all!) during operation, otherwise the LSN may be updated
3. gdb -p pgrep -x mysqld
gdb) p log_sys->lsn
$1 = 12300
(gdb) set log_sys->lsn = 12300000;
Invalid character ';' in expression.
(gdb) set log_sys->lsn = 12300000
(gdb) c

4. Shutdown mysqld, this should be a clean normal shutdown
5. Check if the correct LSN us shown in the error log
6. Start mysqld and check if the correct LSN is shown
   
LOG
---
Log sequence number 12300000
Log flushed up to 12300000
Last checkpoint at 12300000

7. Insert something, check that the LSN is changing

Possible issues: How to avoid database corruption after you change the LSN

• Of course, because this insider method relies on the internal mysql structure it could fail with future versions of InnoDB.
  I have modified LSN in memory for 5.5.32-rel31.0-549.precise during preparations for this blog post.
  Please check the working of this method on the version you are using on a staging system first.
• Also it’s a really bad idea to update the LSN online on a production server, because it will mean recovery will fail if your server ends up crashing.
• Server should be completely idle with the same LSN value for a while.
  ‘Log sequence number’, ‘Log flushed up to’, ‘Last checkpoint at’ are all the same on idle server.
  If the server is not idle enough, and you don’t see changes in SHOW MASTER STATUSG output,
  try to SET GLOBAL innodb_fast_shutdown=0 and restart the server.
• A server restart is required to write the LSN changes to transaction log.
• The system should be stable before change: A mysqld crash during server restart could cause data corruption. Please check that restart procedure is fast, and that “recovery” is not in progress in the mysqld error log

How could corruption happen to start with?

If mysqld crashes, InnoDB will do a crash recovery on mysqld restart.
InnoDB crash recovery applies the transaction log from the last on-disk checkpoint until the ‘Log flushed up to’ position.
If the ‘Log flushed up to’ position is equal to a non-existing position, InnoDB will try to apply old events, because transaction logs are organized in a ring buffer manner. There you can additionally enforce the change if you will re-create transaction logs right before the change. At maximum you will have a mysqld server crash without significant data corruption.

Last warning: ALWAYS have a backup before modifying memory with gdb, especially if you are doing something untested with your particular version of MySQL for the first time.