5

I am dealing with a very large set of databases that are all innodb.

I've enountered this on mysql restart too many times for my comfort:

ibdata files do not match the log sequence number

But I've clearly watched mysql shutdown properly just before the restart when that message happens.

Then it "repairs" right up to the original sequence number with nothing lost.

What is the best approach to deal with and fix this permanently?

Using Percona with innodb_file_per_table=1

Example log:

InnoDB: Initializing buffer pool, size = 80.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 475575972691 and 475575972691 in ibdata files do not match the log sequence number 925369860131 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: Percona XtraDB started; log sequence number 925369860131

Note how the final log sequence number now matches what it thought was wrong in the first place, so there was 100% recovery?

So why is the log sequence not being properly written to ibd?

Is it possible shutdown is incomplete somehow?

Thank you for any advice.

ps. I always wonder if I should be asking this on serverfault or here? Is it okay I asked here?

2
  • 1
    What version of Percona Server? Does it do this every restart or only occasionally? Can you include the log entries from the prior graceful shutdown? Aug 30, 2013 at 20:57
  • What is the value of innodb_fast_shutdown? Jan 9, 2019 at 17:24

1 Answer 1

0

I have faced the same problem: MySQL restarts too many times and ibdata files do not match the log sequence number.

This is happening because one of the tables is corrupted. In order to fix this issue, take a backup of the database and perform the mysqlcheck operation:

1.

mysqldump -u root -p dbname > /tmp/dbname.sql  

2.

mysqlcheck -u root -p dbname 

If you find the corrupted table then you can remove it, I know corrupted tables can't be deleted but we can restore the database from the corresponding production database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.