[Err] [Bak] 1286 – Unknown table engine ‘InnoDB 00:00:00’

Last night I had an error show up a couple times while using Navicat to backup database on Weblogs.us. This was the error:

[Err] [Bak] 1286 – Unknown table engine ‘InnoDB 00:00:00’

On this server MyISAM was preferred so I had specifically disabled InnoDB, yet something was trying to deal with InnoDB and it was causing the backup error . To determine which table(s) were causing the problem, I used the following SQL statement:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘wp_yourdatabase’;

As expected, this listed a variety of tables all of which were MyISAM. However, it also gave me a starting point to cross reference with the table listing that Navicat shows (or the list of files in the appropriate MySQL data directory).

By comparing the SQL output showing good valid tables, versus the table listing in Navicat’s GUI, I could see which old abandoned InnoDB tables were messing up my backups.

This worked great for a small database with 24 tables (21 valid MyISAM, 3 invalid InnoDB) where I could just visually glance at the listings and see the discrepancies.

However, another other database had 228 tables (223 MyISAM, 5 InnoDB) and that made it tough to pinpoint the bad tables. Therefore, I went directly to the command prompt (on Windows) and used the following directory command:

dir *.frm > listing.txt -l

This output 228 filename listings to a text file which I copy/pasted into Excel next to the SQL output from above. The end result was a side-by-side listing that made it easy to pickout any invalid tables. To verify the table was ‘bad’ I tried opening it in Navicat (double clicking on the table name) and it gave me the same Error 1286 message 🙂

I hope this info has been helpful to you!

PS Here is a screenshot showing some of the windows open during this troubleshooting process:

PSS You can even automate the comparison of the tables listings in Excel, here are the relevant links:

How to find unique values between two columns in excel?

How to find unique values between two columns in excel?http://www.extendoffice.com/documents/excel/770-excel-find-unique-values-between-two-columns.htmlFor example, I have two columns of different length filled with student names, and now I want to compare these two columns to select all values in column A but not in column B,…
worksheet function – How to compare two columns and find differences in excel – Super Userhttp://superuser.com/questions/289650/how-to-compare-two-columns-and-find-differences-in-excelHighlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:

Leave a Reply

Your email address will not be published. Required fields are marked *