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.
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.
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;
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:
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
Log sequence number 12300000
Log flushed up to 12300000
Last checkpoint at 12300000
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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.