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

[Robert P. J. Day]

On Wed, 28 Jul 2010, Khalid Baheyeldin wrote:

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

 just to be clear, all i was given as the database contents was a
directory loaded with .frm files, and a single "db.opt" file.  i'm
assuming that that's precisely what i should have expected, yes?

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

  as in, while leaving the /var/lib/mysql/<dbname> directory in
place, drop the database and bring it back??  you're suggesting that
that "recreation" will automagically pick up the db directory that's
still there, but possibly do some repair to make it accessible?  sure,
i can try that.

> 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 can do that, too, but that's just going to get me back to where i
started, no?

rday

p.s.  rather than burning up mailing list bandwidth and typing time,
i'm more than happy to just put this aside if, as i mentioned, someone
who has some ideas is willing to stop by ubuntu hour this eve and see
if they can help.

-- 

========================================================================
Robert P. J. Day                               Waterloo, Ontario, CANADA

        Top-notch, inexpensive online Linux/OSS/kernel courses
                        http://crashcourse.ca

Twitter:                                       http://twitter.com/rpjday
LinkedIn:                               http://ca.linkedin.com/in/rpjday
========================================================================