0

I'm trying to use mysqldump to dump a database, but I'm getting an error:

mysqldump: Couldn't execute 'show create table wp_commentmeta': Unknown storage engine 'InnoDB' (1286)

No worries; I'll just go into MySQL and change the storage engine for the table:

mysql> show create table wp_commentmeta;
ERROR 1286 (42000): Unknown storage engine 'InnoDB'
mysql> Alter table wp_commentmeta ENGINE = myisam;
ERROR 1286 (42000): Unknown storage engine 'InnoDB'

What am doing wrong? (and/or) How can I fix it?

Ben McCormack
  • 32,086
  • 48
  • 148
  • 223
  • What MySQL version is it? What does `show engines;` output in mysql? Did you try starting the server by command line adding `--innodb` in the command? – JScoobyCed Aug 18 '12 at 02:27
  • 8 lines. I see stuff for MyISAM, but not for InnoDB. InnoDB is pretty messed up on this box (hence trying to convert table to myIsam) – Ben McCormack Aug 18 '12 at 02:30
  • @JScoobyCed I get errors when trying to start with `--innodb`. This was a manual upgrade to 5.5 to 5.1. Why the upgrade? I think there might be a bug in `mysqldump` that was causing character encoding issues in 5.1. Why `mysqldump`? I'm trying to move my wordpress blog from windows to linux. Why? So I don't have to pay to VPS fees each month. That's the whole story! – Ben McCormack Aug 18 '12 at 02:33
  • Ok... There is the `mysql_upgrade` command that needs to be run after an...well.. upgrade :). But if innodb isn't found anyway there is a few chance it works. If you can `select` from those tables maybe you have a chance to do the dump manually. – JScoobyCed Aug 18 '12 at 02:34
  • 1
    Actually, if it is wordpress, you don't need the structure. Did you try to do a data only dump with mysqldump `--no-create-info` option – JScoobyCed Aug 18 '12 at 02:39
  • Good workaround! Which gets me thinking, I can just ignore this table! (i think) – Ben McCormack Aug 18 '12 at 02:47
  • Well, that got me to the point where I recognize that, no, the mysqldump character encoding issue persists. – Ben McCormack Aug 18 '12 at 02:54
  • 1
    It would probably be a good idea to find a way to enable InnoDB. Some builds of MySQL do not include it by default, but there's usually a way to add it as a plug-in, or reinstall a full version. MyISAM is pronounced "crazypants" because it isn't journaled and can be completely scrambled if your server crashes, losing all your data. I really hope you have a very aggressive backup schedule. – tadman Aug 18 '12 at 05:37
  • @tadman If by "agressive backup strategy" you mean "not at all" then Yes! That's exactly what I have! (sigh). Ok, Ok, I have a dump file from the other day, which will suffice until I can get it up on linux and figure out backup there. /me puts his crazypants on one leg at a time. – Ben McCormack Aug 18 '12 at 12:47
  • 1
    It's not too hard to schedule `mysqldump` to run once in a while as a first step. It's the responsible thing to do. – tadman Aug 18 '12 at 17:16

1 Answers1

1

There are many ways to recover InnnoDB tables.

If you meet these goals, give this page a try.

  1. You got backup of your ibdata1, ib_logfile0 and ib_logfile1
  2. You also got backup of your database folder with .frm files
  3. You would like to restore this backup into an MySQL server that’s already in production.

http://egil.biz/how-to-recover-mysql-data-from-innodb/

If that doesn't solve your problem, give this other InnoDB recovery walkthrough a try: http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/

Andy
  • 11,215
  • 5
  • 31
  • 33
  • If the actual MySQL install is messed up, backup your data files (listed above) and try them on a fresh MySQL install. – Andy Aug 18 '12 at 08:01