MySQL 5.6 InnoDB doesn’t work

innodbMySQLmysql-5.6

We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing the my.cnf file and removing the innodb-flush-method, which seems to change the error we get from an invalid flush method (we had 0-Direct) to this (after using the force recovery option):

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro in the InnoDB data dictionary has tablespace id 6, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-07-19 18:11:00 3416 [Note] InnoDB: 128 rollback segment(s) are active.
2016-07-19 18:11:00 3416 [Note] InnoDB: 5.6.27 started; log sequence number 1656382
2016-07-19 18:11:00 3416 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
2016-07-19 18:11:00 3416 [Note] Server hostname (bind-address): '*'; port: 3306
2016-07-19 18:11:00 3416 [Note] IPv6 is available.
2016-07-19 18:11:00 3416 [Note]   - '::' resolves to '::';
2016-07-19 18:11:00 3416 [Note] Server socket created on IP: '::'.
2016-07-19 18:11:00 3416 [Note] Event Scheduler: Loaded 0 events
2016-07-19 18:11:00 3416 [Note] D:\mysql\bin\mysqld: ready for connections.
Version: '5.6.27'  socket: ''  port: 3306  MySQL Community Server (GPL)
2016-07-19 18:11:17 50c InnoDB: Error: page 3 log sequence number 4853983
InnoDB: is in the future! Current system log sequence number 1656392.
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.
2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate memory of size 4294967016.

This is the my.cnf:

[mysql]

# CLIENT #
port                           = 3306
#socket                         = /tmp/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = MyISAM
#socket                         = /tmp/mysql.sock

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# CACHES AND LIMITS #
default_tmp_storage_engine     = MyISAM
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 100

# INNODB #
innodb-flush-method            = async_unbuffered
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1G
innodb_lock_wait_timeout       = 3600

# LOGGING #
#log-error                      = mysql-error.log
#log-queries-not-using-indexes  = 1
#slow-query-log                 = 1
#slow-query-log-file            = mysql-slow.log

# REPLICATION #
#log-bin
#binlog_format                 = MIXED
#expire_logs_days              = 7
#server-id                     = 1  

How do we start InnoDB? Note we cannot change the default storage engine. Thanks in advance!

Best Answer

Clearly the data you are trying to start does not belong to the configuration you have in the my.cnf. If you look at the beginning of the log, you will see that there is a mismatch between the files in the system and the InnoDB catalog, meaning that the engine was running before:

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro 
in the InnoDB data dictionary has tablespace id 6, but tablespace with    
that id or name does not exist. Have you deleted or moved .ibd files? 
This may also be a table created with CREATE TEMPORARY TABLE whose .ibd 
and .frm files MySQL automatically removed, but the table still exists in 
the InnoDB internal data dictionary.

The innodb-buffer-pool-size is set to 1G, which does not match with the amount of memory is trying to allocate:

2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate 
memory of size 4294967016.

Check which configuration is the MySQL loading, inspecting the init file that you are currently using.