theking2
theking2

Reputation: 2813

Log sequence number in ibdata files does not match

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?

Upvotes: 6

Views: 33856

Answers (3)

Hessy SharpSabre
Hessy SharpSabre

Reputation: 152

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

Upvotes: 2

zero-day
zero-day

Reputation: 392

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

Upvotes: 0

akuzminsky
akuzminsky

Reputation: 2258

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.

Upvotes: 1

Related Questions