debugsqlMinor
Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
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
After restarting mysql on S2, I faced a problem with getting this error:
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
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: MyDBand 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
Hopefully it's there and you can redump your data and reload it on S2.
SHOW ENGINES. You can try to force recover by doing the following steps:- shutdown mysql on S2
- add
innodb_force_recovery=1to 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.