Summary: The ‘Log Sequence Number (LSN) mismatch’ issue in MySQL occurs when the LSN in the ibdata file does not match the LSN in the log file. Such inconsistency can occur due to various reasons. In this blog, we’ll discuss the troubleshooting methods to resolve the log sequence number mismatch issue in MySQL. In case the database becomes inaccessible, you can use Stellar Repair for MySQL to recover the data from inaccessible database without any loss.
The LSN (Log Sequence Number) is a unique incremental value which is assigned whenever changes occur in the InnoDB storage engine. This value is stored in the ibdata file and redo logs. MySQL Server compares this value in the iddata1 and redo log for a consistent crash recovery. If the LSN mismatch happens due to any reason, MySQL Server fails to detect redo logs, leading to crash recovery failure. Sometimes, the server throws the following error:
InnoDB: The log sequence numbers 403922286 and 403922286 in ibdata files do not match the log sequence number 404982284 in the ib_logfiles.
This error can occur when the redo logs remain with uncommitted changes. This can occur due to one or more of the following reasons:
- MySQL Server is not shut down properly.
- The transaction is being processed during the server crash.
- You might be restoring the incomplete backup file.
- Corruption in MySQL database.
- Corrupted metadata in the InnoDB database files.
- One of the tables in the MySQL database is corrupted.
- Redo log files (ib_logfile0, ib_logfile1, etc.) are corrupted.
How to Detect Log Sequence Number Mismatch in MySQL Server?
You can check the MySQL error log to find the causes behind the LSN mismatch issue. It is located in the data folder in the MySQL installation directory. In the error log, check for the errors or issues related to LSN mismatch, including redo or transaction logs issues. You can also use the SHOW ENGINE INNODB STATUS command to get the detailed information on the InnoDB search engine, including LSN values.
Sometimes, the ibdata filesdo not match the log sequence number due to corruption in tables in the MySQL database. In such a case, you can use mysqlcheck utility to check the MySQL tables for corruption.
Troubleshooting Methods to Resolve Log Sequence Number Mismatch Issue in MySQL
Sometimes, simply restarting the MySQL Server can help resolve Log Sequence Number Mismatch error. If this doesn’t work for you, follow the below methods.
Method 1: Check the Backup File
Sometimes, the ‘ibdata files do not match the log sequence number’ error can occur when there is some issue in the backup file you are trying to restore. Check the backup file and ensure the LSN is in the correct sequence. To ensure the LSN values are correctly matched between successive incremental backups, you can use—-incremental-base with the—-incremental-with-redo-log-only option while using incremental backups.
Method 2: Disable Redo Log Files
Caution: This method is not recommended in the production system. It shuts down and restarts the server which can cause unexpected server stoppage, leading to data loss or instance corruption.
You can experience the ‘log sequence number (LSN) mismatch’ issue if the redo file gets corrupted or damaged. You can disable the redo log. It stops the database from writing new redo log records and helps correct any existing LSN mismatches. You can use the ALTER INSTANCE statement to disable the redo log in MySQL Server. But before this, make sure you have the INNODB REDO LOG ENABLE privilege. Here is the syntax to disable the redo log:
ALTER INSTANCE instance_action
instance_action: {
| {ENABLE|DISABLE} INNODB REDO_LOG
| ROTATE INNODB MASTER KEY
| ROTATE BINLOG MASTER KEY
| RELOAD TLS
[FOR CHANNEL {mysql_main | mysql_admin}]
[NO ROLLBACK ON ERROR]
| RELOAD KEYRING
}Here, ENABLE is to enable INNODB REDO_LOG and DISABLE is to disable INNODB REDO_LOG.
When done, re-enable the redo log.
Method 3: Repair MySQL Database using innodb_force_recovery Option
The MySQL error ‘Log Sequence Number (LSN) mismatch’ can also occur when the tables or metadata in the InnoDB database files are corrupted. In such a case, you can recover the InnoDB database by using the innodb_force_recovery option. Changing this option allows the MySQL Server to start even when it detects corruption in tables. The Innodb_force_recovery option is not enabled by default. You need to enable this option from the configuration file. Follow the below steps:
- First, go to the configuration file (my.cnf) location. The my.cnf file’s location varies based on the operating system. On Windows, the configuration file is located in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.
- Once you found the my.cnf file, go to the [mysqld] section and then insert the below statements:
[mysqld]
Innodb_force_recovery=1
service mysql restart- The default value of innodb_force_recovery is 0. However, you can increase the value to ‘1’ or more to start the MySQL InnoDB engine and dump the tables. Dumping tables with value of 4 or higher can lead to data loss. So, it is recommended to take database backup before proceeding.
Once you have enabled the innodb_force_recovery, the MySQL engine will start. Then, use the ‘Dump and Reload’ method to rebuild and repair corrupted tables. Here’s how:
- Use the mysqldump command as given below to dump the table data:
mysqldump -u user -p database_name table_name > single_dbtable_dump.sql- Next, export all the databases to the dump.sql file using the below command:
mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql- Restart the MySQL Server and then use the DROP DATABASE command to drop the database.
- If it fails to drop the database, then run the below commands to delete the database manually:
cd /var/lib/mysql
rm -rf db_name- Next, disable the InnoDB recovery mode by commenting on the below line in [mysqld]:
#innodb_force_recovery=...- After that, save the applied changes to the my.cnf file and then restart the MySQL Server.
Method 4: Use a Professional MySQL Repair Tool
Restoring the MySQL database by force recovery option can cause data loss. To prevent data loss, you can use Stellar Repair for MySQL. It is a powerful and user-friendly tool that can quickly repair your corrupt MySQL database with complete integrity. It supports the repairing of tables created in InnoDB and MyISAM storage engines. The tool also supports the recovery of specific tables, including partition tables. The tool supports all the MySQL Server versions.
Some key features of this MySQL repair tool are:
- Repairs and recovers both InnoDB and MyISAM database tables with complete integrity.
- Repairs corrupt MySQL database on both Windows and Linux systems.
- Recover all the database objects, including tables, foreign keys, tablespace, primary keys, views, and triggers.
- Repairs multiple corrupt MySQL database files in a single process.
- Previews all recoverable MySQL database objects before saving.
- Saves repaired files in multiple formats – MySQL, SQL Script, MariaDB, HTML, CSV, and XLS.
Conclusion
You may encounter the ‘ibdata files do not match the log sequence number’ error due to several reasons. You can apply the above troubleshooting methods to resolve the issue. If corruption in pages, metadata, or tables in MySQL database is the reason behind the issue, then use Stellar Repair for MySQL. It can help you recover Innodb tables and other objects from the database quickly and without any data loss. It restores all inconsistent data pages from the corrupt MySQL database. It can help you resolve all the corruption-related errors in MySQL.
