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

InnoDB log sequence numberThis 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:


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 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.

Share this post

Comments (7)

  • PaulC

    Silly question, but is there a maximum LSG? Surely that number has to run out at some point.

    September 11, 2013 at 3:43 pm
  • Jeremy Cole

    PaulC: The LSN is a 64-bit field. Since it represents log bytes, if you were able to write 1 GiB per second to the log continuously (which is a LOT, way more than almost anything could achieve) it would still take ~544 years to run out of LSN space. I think that’s basically okay.

    September 11, 2013 at 7:59 pm
  • Jesus

    Pretty nice post. I just stumbled upon your blog and wanted to say that I’ve really enjoyed surfing around your blog posts. After all I’ll be subscribing to your rss feed and I hope you write again soon!

    September 15, 2013 at 10:30 am
  • Vlad Tsepelev

    Hey, thanx! Helped alot.

    Still actual problem in 2014. Just restored my broken wordperess db. Maybe my experience will help someone.

    First of all you should read mysql log to determine that error with innodb (usually /var/log/mysql/error.log).
    If there are lines like these,
    InnoDB: Error: page 0 log sequence number 6094071743825
    InnoDB: is in the future! Current system log sequence number 10000.
    it means that problem in inno db.
    Next you should stop mysql server (“> sudo service mysql stop” in my case).
    Next you should open my.cnf (usually /etc/mysql/my.cnf) and and enambe innodb_force_recovery=# as author mentioned.
    In my case innodb_force_recovery=6 will start all databases and i logged in to mysql-server via mysql-cli.
    NOTE! Sometimes you will not see all your databases by executing >show databases; Just connect with your root (like this > mysql -u root -p).
    BAd thing here that i didnt know how to determine broken db. But only one db was ran via innodb in on my server — WordPress database. So i just removed db completely and restored it from backup script.

    Conclusion: Thanx, im doing everyday backups. Via wordpress plugin, via custom plugin to backup mysql and via AWS snapshots. It can be overhead by it always saves me alot of time in case of exceptional situations.

    So, always do backups.

    July 27, 2014 at 9:52 am
  • Ulises German

    Hi Nickolay, I have a big troubles with my database and I think that you are an expert in mysql. Can you offer your services to me for fix and get me the data?

    November 9, 2018 at 11:00 am
  • Olaf Buitelaar

    For those ending here, it appears there is an alternative method for b.
    While setting up a slave from a partial backup, without the mysql database. I’ve sourced the mysql database from the master database which at that point was already ahead of time.
    From me it was enough to;
    ALTER TABLE mysql.innodb_index_stats ENGINE = InnoDB;
    ALTER TABLE mysql.innodb_table_stats ENGINE = InnoDB;

    i could check the ahead of LSN via;
    InnoDB: Page [page id: space=5359, page number=122] log sequence number 44319213732840 is in the future! Current system log sequence number 43987296276507.
    To verify the errors were only from the innodb tables in the mysql database.
    Version this work on was mariadb 10.3.15

    June 14, 2019 at 8:04 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.