Planet MySQL :: Planet MySQL - Archives - Resolving “Unknown storage engine ‘InnoDB’�...

I know, what, MyISAM-only? Yes, it’s true, and it occurs more than you might think.

If you do run MyISAM-only, it’s common to have disabled InnoDB altogether (–skip-innodb) to avoid allocating any RAM to it, and to avoid unnecessary files required for backups, and so forth. (See this post if interested in disabling InnoDB in MySQL 5.6 and/or MariaDB 10.0.)

One improvement in MySQL 5.6 and MariaDB 10.0 is the addition of some system tables. The 2 that are in both are `innodb_table_stats` and `innodb_index_stats`, both of which have an engine type of InnoDB. (The 1 additional system table in MariaDB 10.0 is `gtid_slave_pos`, and the 3 additional system tables in MySQL 5.6 are `slave_master_info`, `slave_relay_log_info`, and `slave_worker_info`.)

The issue, with regards to running MyISAM-only, is that these new tables are of type InnoDB by default. So if you’re running MyISAM only, you’ll see some errors and warnings when running mysql_upgrade, and/or examining your error log.

When running mysql_upgrade, you’ll see entries like (in both MySQL and MariaDB):

mysql.innodb_index_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt
mysql.innodb_table_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt

On the other hand, if looking in your error log, you might see something like this in MySQL:

[Warning] Info table is not ready to be used.
Table 'mysql.slave_master_info' cannot be opened.
[Warning] Info table is not ready to be used.
Table 'mysql.slave_relay_log_info' cannot be opened.

Or this in MariaDB:

[Warning] Failed to load slave replication state from
table mysql.gtid_slave_pos: 1286: Unknown storage engine 'InnoDB'

So what to do if you want to fix this?

If you do not want the new tables at all, you can simply delete the .frm and .ibd files. This is probably fine for the 2 innodb_*_stats tables, since you’re not using InnoDB. However, you may want the other tables, depending on your replication needs, so this might not be an option for them.

Thus if you want one or more of these tables, then you can re-create them as MyISAM tables using CREATE TABLE statements (I’ll provide current ones at the end). Alternatively, you can temporarily enable InnoDB, ALTER the tables from InnoDB to MyISAM using “ALTER TABLE `table_name` ENGINE=MyISAM”, then disable InnoDB again.

MariaDB 10.0.14 SHOW CREATE outputs:

CREATE TABLE `gtid_slave_pos` (
  `domain_id` int(10) unsigned NOT NULL,
  `sub_id` bigint(20) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `seq_no` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySQL 5.6.21 SHOW CREATE outputs:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `slave_master_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Host` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Port` int(10) unsigned NOT NULL,
  `Connect_retry` int(10) unsigned NOT NULL,
  `Enabled_ssl` tinyint(1) NOT NULL,
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_verify_servert_cert` tinyint(1) NOT NULL,
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Retry_count` bigint(20) unsigned NOT NULL,
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE `slave_relay_log_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Sql_delay` int(11) NOT NULL,
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

Hope this helps.

MySQL Links