MySql missing from Services - could not open innodb_index_stats.ibd

Question

I have Ampps 3.7 installed on Windows 10, and whilst Apache runs ok Mysql will no longer start up; It use to run ok but now has an innidb error. My issue is similar to the question here, except that I've not upgraded mysql, and the suggested solution is to delete and reinstate several inno_db tables. However how can I do that when I can't connect to mysql

I've read this and this which both say add the following to my.ini

innodb_force_recovery = 1

The Ampps Mysql error log says:

2018-01-05 12:17:46 11096 [Warning] You need to use --log-bin to make --binlog-format work.
2018-01-05 12:17:46 11096 [Note] Plugin 'FEDERATED' is disabled.
2018-01-05 12:17:46 11096 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-01-05 12:17:46 11096 [Note] InnoDB: The InnoDB memory heap is disabled
2018-01-05 12:17:46 11096 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-01-05 12:17:46 11096 [Note] InnoDB: Memory barrier is not used
2018-01-05 12:17:46 11096 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-01-05 12:17:46 11096 [Note] InnoDB: Not using CPU crc32 instructions
2018-01-05 12:17:46 11096 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-01-05 12:17:46 11096 [Note] InnoDB: Completed initialization of buffer pool
2018-01-05 12:17:46 11096 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-05 12:17:46 11096 [Note] InnoDB: The log sequence numbers 1600637 and 1600637 in ibdata files do not match the log sequence number 3531342 in the ib_logfiles!
2018-01-05 12:17:46 11096 [Note] InnoDB: Database was not shutdown normally!
2018-01-05 12:17:46 11096 [Note] InnoDB: Starting crash recovery.
2018-01-05 12:17:46 11096 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-01-05 12:17:46 11096 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace invoiceplane/ip_invoice_custom uses space ID: 2 at filepath: .\invoiceplane\ip_invoice_custom.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd
InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

Any ideas please?

UPDATE:

Deleting the 2 ib_logfile's initially work, but then they reappeared (perhaps after a reboot). By again deleting the 2 files I was able to start mysql, but the following errors are listed:

... Many similar errors precede with different page numbers
2018-01-10 13:24:50 10a8 InnoDB: Error: page 329 log sequence number 2371974
InnoDB: is in the future! Current system log sequence number 1601046.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2018-01-10 13:24:50 980 InnoDB: Error: page 0 log sequence number 3484405
InnoDB: is in the future! Current system log sequence number 1601046.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2018-01-10 13:24:50 3032 [Note] InnoDB: 5.6.35 started; log sequence number 1601036
2018-01-10 13:24:50 3032 [Note] Server hostname (bind-address): '*'; port: 3306
2018-01-10 13:24:50 3032 [Note] IPv6 is available.
2018-01-10 13:24:50 3032 [Note]   - '::' resolves to '::';
2018-01-10 13:24:50 3032 [Note] Server socket created on IP: '::'.
2018-01-10 13:24:50 3032 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2018-01-10 13:24:50 3032 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2018-01-10 13:24:50 3032 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2018-01-10 13:24:50 3032 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2018-01-10 13:24:50 3032 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2018-01-10 13:24:50 3032 [Note] Event Scheduler: Loaded 0 events
2018-01-10 13:24:50 3032 [Note] mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.35'  socket: ''  port: 3306  MySQL Community Server (GPL)

Deleting the log files is obviously not solving the root cause.

Seems I'm not the only one having issues with MySql on Ampps.

Answer 1

I had the exact same problem and fixed it by adding innodb_force_recovery = 1 to my.ini as you pointed out. Don't forget to:

1. Save configuration and restart MySQL.
2. Now Stop MySQL, Remove the line just added and start MySQL again.

NOTE: you could also simply change the value of innodb_force_recovery to 0 instead of deleting it

as the link you pointed out here suggests, it is very important

This was the best solution for me unlike those solutions that require you to run sql queries when mysql cannot even start O.o

Answer 2

The answer here worked for me:

open the following folder
Ampps/mysql/data/
delete all files like ib_logfile** and mysql-bin.index,mysql-bin.****,
then start or restart MySQL via the AMPPS pannel

For me there were only 2 ib_logfile's.