[kwlug-disc] how to recover a mysql database when getting "Unknown table engine 'InnoDB???" err

[Khalid Baheyeldin]

On Wed, Jul 28, 2010 at 10:17 AM, Robert P. J. Day <rpjday at crashcourse.ca>wrote:

>
>  as a followup to something i asked once upon a time, i'm trying to
> recover the tables and data for a mysql database given what is alleged
> to be the ibdata1, ib_logfile[12] files and the database directory
> itself with all of the .frm files, and i *think* i'm getting close.
>
>  on my current ubuntu 10.04 system, i wasn't using mysql for anything
> critical so i just popped into the /var/lib/mysql directory, totally
> overwrote those files and added that database directory,


You should never do that.

For MyISAM tables, you may be able to get away with this, since
each MYD/MYI paid form one discrete table. The .frm is the table
definition.

But InnoDB is far more complex and the ibdata file contains lots of data
structures inside it.


> restarted mysql, popped in and "show databases;" shows the db in question.
>
>  i can then "use" the database, and "show tables;" does, in fact,
> show me the tables.  and then ... not good.
>
>  doing a "describe <tablename>;" gives me:
>
> ERROR 1286 (42000): Unknown table engine 'InnoDB...
>
> where the string "InnoDB" has a small number of unprintable characters
> after it.
>
>  i have no idea if the ibdata1 or log files have been corrupted, but
> the fact that i got as far as i did was promising -- i could see the
> database, i could select it and i could list its tables.  but i can't
> get any further than that.
>

You can try DROP DATABASE yourdb; followed my mysqladmin create yourdb;
and see if that gets you anywhere.

Your cleanest option is to restart from scratch.

Do aptitude purge mysql-server, then remove the /var/lib/mysql directory,
and then do an aptitude install mysql-server cleanly.

  i'm open to suggestions, and i also plan on going to the ubuntu hour
> this evening so if anyone wanted to sit down and give me a hand to
> solve this at ubuntu hour, i'm willing to compensate them for their
> time.
>
--
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
Simplicity is prerequisite for reliability. --  Edsger W.Dijkstra
Simplicity is the ultimate sophistication. --   Leonardo da Vinci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://kwlug.org/pipermail/kwlug-disc_kwlug.org/attachments/20100728/169fe547/attachment.htm>