6

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?

theking2
  • 2,174
  • 1
  • 27
  • 36
  • I was not able to repair the database and gave up after two days of trying. – theking2 Dec 03 '13 at 12:25
  • 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](http://stackoverflow.com/help/on-topic) in the Help Center. Perhaps [Unix & Linux Stack Exchange](http://unix.stackexchange.com/) or [Database Administrators Stack Exchange](http://dba.stackexchange.com/) would be a better place to ask. – jww Sep 02 '18 at 02:53
  • Maybe related, [systemd not shutting down MariaDB properly?](https://serverfault.com/q/929030/145545) 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 Sep 02 '18 at 03:49

3 Answers3

2

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

Hessy SharpSabre
  • 162
  • 12
0

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.

akuzminsky
  • 2,190
  • 15
  • 21
  • Is it not possible that the mismatch in log sequence number could be causing the crash? – Vincent Aug 18 '18 at 00:53
  • Maybe related, [systemd not shutting down MariaDB properly?](https://serverfault.com/q/929030/145545). It seems to be a chronic problem. – jww Sep 02 '18 at 04:18
0

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
zero-day
  • 362
  • 2
  • 13
  • Yes the xampp team is getting messier these days so I stay clear of obnoxiousness and use WampServer or MAMP. Having said renaming the data folder, copying the backup folder to a new data folder and copying user defined folders from the data backup to the new data _and_ copy the ibdata1, ib_logfile0 and ib_logfile1 from the data backup to the new data folder does solve the harm. Normally – theking2 Dec 03 '22 at 14:57