Log sequence number in ibdata files does not match

6
votes

Everytime when I start my Mysql database I see this in the error_log:

131015 12:07:06 [Note] Plugin 'FEDERATED' is disabled. 131015 12:07:06 InnoDB: The InnoDB memory heap is disabled 131015 12:07:06 InnoDB: Mutexes and rw_locks use Windows interlocked functions 131015 12:07:06 InnoDB: Compressed tables use zlib 1.2.3 131015 12:07:06 InnoDB: Initializing buffer pool, size = 16.0M 131015 12:07:06 InnoDB: Completed initialization of buffer pool 131015 12:07:06 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 131015 12:07:06 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 131015 12:07:07 InnoDB: Waiting for the background threads to start 131015 12:07:08 InnoDB: 5.5.32 started; log sequence number 1595695 131015 12:07:08 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 131015 12:07:08 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 131015 12:07:08 [Note] Server socket created on IP: '0.0.0.0'.

I've tried mysqlcheck -u root -p --repair -A in order to repair the database. This reports that all tables are a-ok.

I've also tried setting innodb_force_recovery to 4

I've tried SET GLOBAL innodb_fast_shutdown = 1; and shutdown the DB.

None of these make the errors go away.

How do I repair the InnoDB tables in my database?

3
mysqlrecovery
Did you ever find a solution for this? – Scott Keck-Warren
I was not able to repair the database and gave up after two days of trying. – theking2
Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See What topics can I ask about here in the Help Center. Perhaps Unix & Linux Stack Exchange or Database Administrators Stack Exchange would be a better place to ask. – jww
Maybe related, systemd not shutting down MariaDB properly? These are the reasons the question should be asked on the proper site. This answer lacks an analysis of potential root causes of the problem. All you get are anecdotes that one developer found works for them. – jww

3 Answers

1
votes

I resolve my problem by removing some files in "Xampp\mysql\data":

  • ib_logfile0
  • ib_logfile1
  • ibdata1

** Be Sure to Backup your DBs by :

[mysqld]
innodb_force_recovery = 2

(This command will help you to start Mysql in Read-only mode.)

0
votes

LSN (log sequence number) may not match if MySQL crashed. But if the crash recovery process finished you should be fine. If any transaction modifies data in a page, new LSN will be written to the page header.

I would investigate why MySQL crashes every time you restart it. To my knowledge /etc/init.d/mysql stop may take long time, so if you restart the server OS may just kill mysqld. In that case you should manually stop MySQL before reboot the whole server.

0
votes

I had the same issue.

In my case, I was running MySQL on windows via XAMPP and using the XAMPP controls to start/stop the server.

It appears the XAMPP shutdown control was killing the MySQL server, rather than requesting a shutdown.

Solution

  1. (optional) In my.ini set innodb_fast_shutdown=0 to configure mysqld to perform a full showdown
  2. Create a .bat file, with contents mysqladmin --user=[user] --password=[password] shutdown replacing [user] and [password] with your root user details

When you wish to shutdown the MySQL server, just run the .bat file. The shutdown is detected by XAMPP control panel so you can restart using the XAMPP control.

If you now take a look at your logs, you'll see the errors have gone away

Or better yet, just install mysql as a windows service.

References:

  1. innodb_fast_shutdown http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fast_shutdown
  2. mysqladmin shutdown - http://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html