What to do when MySQL says skip-innodb is defined
Are you seeing a MySQL error that says InnoDB support isn’t enabled, even though it is? This article explains why it happens and how to fix it.
The symptom
Suppose you call SHOW INNODB STATUS or another InnoDB-specific command and MySQL reports the following error:
“ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined”
Yet you search the MySQL configuration files and find that’s not true. And you know you have InnoDB tables, too. What’s going on?
As it turns out, the error message is a bit misleading. Many problems will cause this error message.
Dig deeper
If InnoDB says it’s disabled, it probably is. Look at a couple other things. Does SHOW ENGINES report InnoDB is disabled? How about SHOW VARIABLES LIKE 'have_innodb'? Try SHOW TABLE STATUS on an InnoDB table — are most columns NULL?
If so, you most likely have an InnoDB configuration error. Not that you’ve disabled it with skip-innodb, but there’s something wrong. If so, MySQL will still start, but the InnoDB storage engine, and tables that use it, will be disabled.
One possible solution
When this happened to me, it was a config file upgrade that I didn’t check carefully. The old directive for the InnoDB data file was as follows:
innodb_data_file_path = ibdata1:10M:autoextend
When I upgraded the file, I changed it to
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
That wouldn’t have been a problem, except the file was already larger than 128MB. This is a slightly hard error to catch sometimes, because it may not show up in your MySQL error log (it doesn’t on my Ubuntu laptop when I deliberately force the error to happen).
A note of general caution
For those of you who are new to InnoDB configuration and administration, be careful. InnoDB has to be told exactly what to do. If you do anything wrong, such as set the permissions wrong on InnoDB’s log or data files or directories, change the file sizes, or any of a bunch of other mistakes, it will be very unforgiving. It may even wipe your existing log and data files and replace them with new ones full of zeroes (yes, this will delete all your data).
The MySQL error log is your friend, but in many cases InnoDB doesn’t flush any output to it for a long time, so you might for example start MySQL and see “MySQL NOT started.” It might just be that InnoDB wasn’t shut down nicely and has to roll back transactions to get to a consistent state. If so, that information will show up in the log files, but it might take a Very Long Time.
If you need help
You can get help on the #mysql IRC channel, mailing lists, or just read the MySQL manual. I like the IRC channel best myself. It’s friendly and there are a lot of smart people there to answer your questions.
Good page, mang. We ran into this problem on one of the slaves I built at Digg, and I was tearing my hair out trying to figure out where I’d defined skip-innodb. As soon as I read this page, it became clear, so I fixed my problem and moved on.
timeless
27 Jul 07 at 5:06 pm
Hopefully, this will help someone. I found that, stopping the server, removing (or renaming, if you need them) the ib_logfileX files in the mysql data directory, and then starting the server again solved my problem and allowed InnoDB to be enabled again.
Marker
5 Sep 07 at 1:28 am
thanks, I was pulling what’s left of my hair too.
short story, my log files are of a different size than the one defined in my.cnf… –skip-innodb!! that’s what I call misleading errors…
great site for mysql stuff in general
Fierro
20 Sep 07 at 4:43 pm
This may seem stupidly obvious, but MySQL will not magically change config for you when loading InnoDB data.
I was setting up a new slave in a single-master replication setup, and started the server with the default my.cnf in order to get shit setup as fast as possible. Then forgot to restart it with the master-matching config for the innodb logfile sizes before loading in said data.
Needless to say, I had to load in all the data… again.
I guess: be sure the config your server is running is the one you plan on having it run before you start loading in InnoDB data. Or suffer the same stoopid fate as me. :)
Another guy
18 Oct 07 at 1:54 pm
FYI, Markers solution above worked great for me. my ib_logfiles were all set to 10M each, where in my my.cnf file they were supposed to be 128M. I deleted the files restarted mysql and the files came back the correct size…InnoDB works now! thanks alot man
Ray
30 Oct 07 at 12:12 pm
Thanks for the tidbit of knowledge you deigned to impart on through this webpage.
It probably saved me and a co-worker an entire weekend of moving databases, rebuilding a server or god knows what else in trying to solve this problem.
I bow to your greatness. :)
Omo
3 Nov 07 at 12:32 am
Perfect – you made my day.
I really got crazy about this error – and in the end it was simply me copying in a my.cnf from a different server.
Muchos gracias!
CoKo
13 Dec 07 at 5:25 pm
Great!! THANKS!!!
Wing[RU]
27 Jun 08 at 10:07 pm
Hey, this saved my day (and a lot of irritation for our users).
Indeed: remove the ib_logfiles from /var/lib/mysql after changing this logfile size and you’re set!
Thanks!
joe
22 Oct 08 at 10:59 am
[...] What to do when MySQL says skip-innodb is defined [...]
Tunning MySQL InnoDB performance
30 Jun 09 at 11:04 am