HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
afterengineerrorunknowninnodbquerymysqlrestartingtable

Problem

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log).

I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).

The DB on S1 was using one data file (ibdata).

After dumping the DB to S2, I set innodb_file_per_table=1. This made every table to have its own ibd file. now everything went fine and smoothly.

After restarting mysql on S2, I faced a problem with getting this error:

Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB
and when I try to show engines, I get the following:

show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB is not listed.

In error log, I can see

Solution

The error log indicates you have some data corruption. When that happens, InnoDB does not become available in SHOW ENGINES. You can try to force recover by doing the following steps:

  • shutdown mysql on S2



  • add innodb_force_recovery=1 to your my.cnf file



  • start mysql and see if you have your data listed.



Hopefully it's there and you can redump your data and reload it on S2.

Context

StackExchange Database Administrators Q#6859, answer score: 6

Revisions (0)

No revisions yet.