Mysql engine innodb error

InnoDB storage engine will throw error ‘Unknown table engine ‘innodb” if its disabled or due to other settings. Today we'll see how to fix it.

InnoDB is a storage engine for MySQL. Due to its speedy crash recovery, InnoDB is the preferred engine for high available databases.

But there are times when InnoDB throws error, such as ‘Unknown table engine ‘innodb”.  Today, we’ll see when and why this error happens and how to fix it.

What is ‘Unknown table engine ‘innodb” error?

The error message itself clearly says that MySQL service is unable to detect the InnoDB storage engine.

InnoDB engine error manifests itself in different situations:

1. Failure in creating InnoDB tables

Suppose you try to create a new table using the command (“CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;”).

The table creation fails giving the error – ERROR 1286 (42000) at line 1: Unknown table engine 'innodb'.

Table creation or listing using PhpMyAdmin may also not work. It may not even list the InnoDB storage engine option but instead throw up this error.

2. Using MySQL commands that involve InnoDB

When InnoDB engine is disabled, using certain commands would show this error.

For instance, listing the storage engines using ‘show engines’ command, backing up the databases using mysqldump, etc. are situations where you may end up seeing this error:

mysqldump: Got error: 1286: Unknown table engine 'InnoDB' when using LOCK TABLES

3. MySQL server can fail to start

MySQL may not start with InnoDB engine support. The error shown in the log file would be:

[ERROR] Unknown/unsupported storage engine: InnoDB

4. Error in the websites

All of a sudden, all your database-driven websites can start giving the error along with the database queries:

Notice: Error: Unknown table engine 'InnoDB'
Error No: 1286

What causes the error ‘Unknown table engine ‘innodb”

Unknown table engine error happens either because InnoDB storage engine is not enabled in the server or because it got corrupt.

The main reasons that cause the error are:

1. InnoDB not enabled

If the InnoDB engine is disabled in the MySQL database server configuration, it will give error ‘Unknown table engine ‘innodb” whenever you try to create a table with InnoDB support.

It can happen if the MySQL server is not compiled with InnoDB support or if the InnoDB support is disabled via configuration settings.

If the MySQL server was built from source and the built-in InnoDB wasn’t compiled in it, InnoDB engine will not be recognized.

You can check if InnoDB is supported in the server, using the MySQL command:

Check if InnoDB is enabled in server

Check if InnoDB is enabled in server

The value ‘DISABLED’ for ‘have_innodb’ parameter shows that InnoDB is not enabled in that server.

InnoDB engine can also get disabled as a result of a MySQL server upgrade, server migration or manual configuration changes.

If the parameter ‘skip-innodb’ is uncommented in /etc/my.cnf, it will skip loading the InnoDB database engine.

2. Memory shortage

Server not having enough memory to allocate to MySQL, can cause InnoDB to fail. Memory allocation happens based on the parameters such as ‘innodb_buffer_pool_size'.

If the value alloted for this pool size is greater than the available memory limit, InnoDB error occurs.

3. Huge or corrupt log files

In the long run, it so happens that the InnoDB log files grow in size of around GBs or they get corrupt due to some reason. The log files are available at ‘ib_logfile'.

These log files contain the changes to InnoDB data. If they get corrupt, InnoDB will fail to load and ends up giving the error ‘Unknown table engine ‘innodb”.

4. Incorrect permissions for /tmp or socket file

By default, the MySQL server creates its socket file in /tmp or other tmp folder as configured. This folder requires 1777 permissions, for the service to work.

If the /tmp doesn’t have enough permissions to create the socket file, it may end up showing the InnoDB error. InnoDB also fails to run if this folder is full and have no space left.

How to fix the error ‘Unknown table engine ‘innodb”

After identifying the reason for the InnoDB engine error from the error logs, we can apply any of the fixes here, as suited for the situation.

1. Enable InnoDB in the configuration file

If InnoDB support is disabled in the configuration file, here is how to fix it.

Check the ‘/etc/my.cnf’ file. It is the configuration file for MySQL server.

Find the ‘skip-innodb' parameter and comment it by adding # at the beginning of the line:

Enable InnoDB support in my.cnf

Enable InnoDB support in my.cnf

Restart the MySQL server and now InnoDB support will be enabled and you would be able to execute commands without the error.

InnoDB engine support enabled in server

InnoDB engine support enabled in server

2. Recreate the InnoDB log files

InnoDB usually recovers its log files when the MySQL service is started.

So, if the service does not start due to huge or corrupt log files, then rename them and restart MySQL with these steps:

a. Stop MySQL service:

/etc/init.d/mysql stop

b. Rename the log files to some other name

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak 

mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

c. Start MySQL service:

/etc/init.d/mysql start

Now the MySQL server would start fine. It will create the log files from the scratch and the InnoDB tables would be accessible without errors.

3. Edit the configuration settings

Edit the following parameters in ‘/etc/my.cnf’ and set optimal values based on the server settings, such as available memory and CPU.

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
innodb_thread_concurrency

These are the parameters that allocate memory and CPU for the InnoDB storage engine. There are no default values for these parameters, but the values can change from server to server.

Always verify the permissions of the ‘tmp’ folder and confirm that enough memory and disk space are available for the MySQL server.

At Bobcares, we also examine the server logs and monitor the MySQL performance and fine-tune the values set for each parameter.

4. Setup MySQL server with InnoDB support

All the above fixes are relevant on servers that already have InnoDB engine compiled with it. MySQL 5.5 versions and above have InnoDB engine enabled by default.

If your MySQL server doesn’t have InnoDB support compiled in, you need to recompile it with InnoDB. If you are running older versions of MySQL, it is best to upgrade your server.

To install the latest version of MySQL server in Ubuntu, use the command:

apt-get install mysql-server-5.6

This will install the MySQL server 5.6 with InnoDB support, provided all dependencies are installed.

Points to note..

The file ‘ibdata1’ contains the data of the InnoDB databases and should never be deleted.

So, before doing any changes to MySQL server, we always make a backup of these:

a. MySQL data directory (usually at /var/lib/mysql)

b. MySQL configuration file (default at /etc/my.cnf )

Today we saw the major causes for the error ‘Unknown table engine ‘innodb” and how to fix them. In addition to these, some configuration errors can also lead to errors.

Examining the MySQL error logs, permissions, resources, etc. may be vital to resolve InnoDB related errors. In critical situations, restoring from the backups would also be required.

At Bobcares, we perform crash rescue services with minimal downtime, even for servers with fully corrupt data and no backups.

Read: Database crash rescue – How we re-built an InnoDB MySQL database when ibdata1 file was corrupted

Get a FREE consultation

Do you spend all day answering technical support queries?

Wish you had more time to focus on your business? Let us help you.

We free up your time by taking care of your customers and servers. Our engineers monitor your servers 24/7, and support your customers over help desk, live chat and phone.

Talk to our technical support specialist today to know how we can keep your service top notch!

TALK TO AN EXPERT NOW!

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Bobcares provides Outsourced Hosting Support and Outsourced Server Management for online businesses. Our services include Hosting Support Services, server support, help desk support, live chat support and phone support.

Источник

Источник: http://kb.odin.com/ru/6586

Проблема

  1. MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
  2. При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL): 
    mysqlcheck -uadmin -p****** db_example
db_example.BackupTasks
error : Can't find file: 'BackupTasks.MYD' (errno: 2)
  3. Невозможно выполнить запрос таблицы с оператором «SELECT»: 
    mysql> select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
  4. Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление. 
    mysql> repair table misc;
+-------------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+--------+----------+---------------------------------------------------------+
| psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
+-------------------------+--------+----------+---------------------------------------------------------+

Причина

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 4

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

/etc/init.d/mysqld start

Создайте дамп всех баз данных:

mysqldump -uadmin -p****** -A > /root/dumpall.sql

Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

/etc/init.d/mysqld stop
rm -rf /var/lib/mysql/*

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

/etc/init.d/mysqld start

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:

mysql -uadmin -p****** > dumpall.sql

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Попробуйте создать копию:

CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>

Или при попытке сделать дамп через mysqldump

[red@hellsrv ~]$ mysqldump -uroot -p databasename > databasename.sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES
[red@hellsrv ~]$

ВниманиеДо начала любых действий рекомендуем создать резервную копию файлов базы

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

service mysqld stop
cp -R /var/lib/mysql/databasename /home/USERNAME/backup

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

  • узнать структуру таблиц
  • иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

  • файл .frm хранит в себе структуру таблицы;
  • файл .ibd собственно данные

План восстановления:

  • выяснить структуру поврежденной таблицы;
  • создать новую базу;
  • создать в новой базе таблицу нужной структуры;
  • скопировать данные в новую таблицу из старой;
  • если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

mysqlfrm --diagnostic table1.frm
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Также желательно узнать кодировку старой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251                     | cp1251_general_ci      |
+----------------------------+------------------------+
1 ROW IN SET (0.00 sec)

Создаем новую базу:

mysql> CREATE DATABASE helldb CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

mysql> USE databasename;
mysql> CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Далее копируем данные:

  • Очищаем автоматически созданный файл 
    mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
Query OK, 0 ROWS affected (0.04 sec)
  • Копируем файл с данными с поврежденной таблицы 
    cp /home/USERNAME/tables1.ibd tables1.ibd
chown mysql:mysql tables1.ibd
  • Импортируем данные 
    mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
Query OK, 0 ROWS affected, 1 warning (0.50 sec)
  • Проверяем корректность чтения данных 
    mysql> SELECT * FROM tables1 LIMIT 10;
+-----+-----------+
| id  | title     |
+-----+-----------+
|  1  | Title 1   |
|  2  | Title 2   |
|  3  | Title 3   |
|  4  | Title 4   |
+-----+-----------+
4 ROWS IN SET (0.00 sec)

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.

Похожее

Источник


Исправление ошибки “Unknown storage engine InnoDB” в MySQL

2015-05-13

6 минут read (About 930 words)

Исправление ошибки “Unknown storage engine InnoDB” в MySQL при работе только с MyISAM или после обновления на MySQL 5.6 или MariaDB 10.0

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

Для исправления ошибки, создадим недостающие таблицы в базе mysql.

MariaDB 10.0.хх

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

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';

Комментарии



Источник

Понравилась статья? Поделить с друзьями:
  • Mysql duplicate entry error 1062 duplicate entry
  • Mysql duplicate column error
  • Mysql data mysqlclient mysqlexception fatal error encountered during command execution
  • Mysql create table error 1064
  • Mysql connector python error