October 25, 2014

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

How to move the InnoDB log sequence number (LSN) forwardThis 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.

About Nickolay Ihalainen

Nickolay joined Percona in December 2010, after working for several years at what is now the most popular cinema site in Russia. During the time he was there, Nickolay and a small team of developers were responsible for scaling the site into one which now serves over a million unique visitors per day. Prior to that, he worked for several other companies, including NetUp, which provides ISP billing and IPTV solutions, and eHouse, the oldest Russian e-commerce company. Nickolay has a great deal of experience in both systems administration and programming. His experience includes extensive hands-on work with a broad range of technologies, including SQL, MySQL, PHP, C, C++, Python, Java, XML, OS parameter tuning (Linux, Solaris), caching techniques (e.g., memcached), RAID, file systems, SMTP, POP3, Apache, networking and network data formats, and many others. He is an expert in scalability, performance, and system reliability.

Comments

  1. PaulC says:

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

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

  3. Jesus says:

    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!

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

Speak Your Mind

*