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!