TurnKey Linux Virtual Appliance Library

Why is InnoDB disabled? (SOLVED)

HoKe's picture

after installing Drupal 6 appliance i do have problem to enable InnoDB in MySQL.

can some one help me please?

Thx

Liraz Siri's picture

Innodb in enabled by default

InnoDB is enabled by default, you don't have to do anything.

If you log in to PhpMyAdmin and go to the "Storage Engines" section, you'll notice that InnoDB is not disabled.

See the MySQL manual for more information (13.2.3. InnoDB Configuration).

HoKe's picture

my.cnf

in my.cnf is skip-innodb commented out = enabled innodb in mysqld. I tried to run live system to see defaults and realised following:
mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined
mysql>
please help ;) if possible
thx in advance
Liraz Siri's picture

I can't reproduce this...

I booted the version of the appliance you are using in live mode (I.e., without installation) and the InnoDB engine is enabled, as it should be.

If you want me to try and investigate/reproduce your problem you are going to have to give me a great deal more information:

* what version of the appliance are you using?
* are you using the appliance in live mode or installed to a hard drive?
* what kind of machine or virtual machine is running the appliance? how much memory does it have?
* did you reconfigure mysql in any way?
* could you try starting over and see what it takes to reproduce this problem?

HoKe's picture

demanded answers

no problem to give you requested information
turnkey-drupal6-2008.10.28-hardy-x86
Live mode. 8GB HDD ready but not used for installation
VM - VirtualBox-2.1.0-41146-Win_x86.msi
512 MB RAM
no reconfiguration
I did only following:
mysql -u root -p
inserted password
mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because
skip-innodb is defined
mysql> show engines;
InnoDB |DISABLED
HoKe's picture

news

finaly I found a solution.
/etc/init.d/mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

/etc/init.d/mysql start
now it works! great ;)
Liraz Siri's picture

Your innodb log files were corrupt?

I did a little research on why your solution works and it seems that though InnoDB is enabled by default, MySQL seems to disable it automatically if your InnoDB log files get corrupted. When you remove them, they are recreated, allowing InnoDB to start again.

Thanks for sharing!

Guest's picture

Thanks!

Thanks for the tip! This solved the problem for me also. 

Guest's picture

THANKS!

Thank you, Hoke!

I'm not even using Drupal, and the solution you provided has saved me! Seens like a bug with the log files!

 

Thank you, again!

CaioToOn!

Guest's picture

Now, it works !

/etc/init.d/mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

/etc/init.d/mysql start
 

This is THE ULTIMATE solution !

Merci beaucoup

Guest's picture

Thank you

You saved my website. Thank you very much for sharing this solution.

You just made my day HoKe.

Guest's picture

it is happening again

This is work fine but after the some time it is happening again. so this is not perfect solution. :(

Jeremy Davis's picture

I suggest that you do some MySQL troubleshooting

Something, somewhere must be causing this if you are having recurring issues. I just did a quick google and it seems that there are a lot of InnoDB settings that can be tweaked in MySQL regarding it's behaviour. I think that they are worth further exploring...

SeLoRe's picture

I cant believe that I tried

I cant believe that I tried too much thing and the last it worked. thank you very much.

Guest's picture

Thanks - this worked

Thanks - this worked perfectly - very strange that something like this would work.

Alon Swartz's picture

setting InnoDB as default mysql storage engine

As Liraz mentioned, InnoDB is enabled by default.
# mysql -uroot
> show engines;
 MyISAM     | DEFAULT
 InnoDB     | YES
 ...
But if you want it to be your default storage engine you have to specify it, for example:
# cat /etc/mysql/conf.d/storage_engine.cnf
[mysqld]
default-storage-engine = InnoDB

# /etc/init.d/mysql restart

# mysql -uroot
> show engines;
 MyISAM     | ENABLED
 InnoDB     | DEFAULT
 ...
Guest's picture

Thank you

I ran across this same problem w/ Joomal and your solution did the trick thanks so much!

Guest's picture

This helped me, too!

Thanks so much for this information! I had the same problem here.

Guest's picture

Thanks

This worked for me.  One thing to note was that I had to add default-storage-engine=innodb directly below the commented skip-innodb statement.  This is on CentOS 5.3.

 

Guest's picture

I don't have "skip-innodb statement" in my.cnf

Hi there. I'm having trouble enabling InnoDB.

mysql> show engines;
...
| InnoDB     | DISABLED
...

mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

But I don't have "skip-innodb statement" in my.cnf! It's a very small file, only a few lines.

cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

I already tryed removing files /var/lib/mysql/ib* and restarting mysqld, but the problem remains. I've updated mysql to the latest version using YUM (BTW, I'm using CentOS 5)

# mysql -V
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

 

Anyone can help me? Thanks.

Guest's picture

SOLVED

I solved my problem. It seem that is was a permission issue:

cat /var/log/mysqld.log

/usr/libexec/mysqld: Can't create/write to file '/tmp/ibJpS3fB' (Errcode: 13)

All I needed was a "chmod 777 /tmp" to get InnoDB working...

Thanks anyway

Guest's picture

Wow , its nice command

Wow , its nice command

Guest's picture

Innodb Issue

I am unable to enable the innod engines support in my server, skip-innodb is not mentioned in the my.cnf file but while I execute the show engines; syntax I am getting the below result which says that InnoDB is Diasabled.

 

mysql> show engines;
+------------+------------+----------------------------------------------------------------------------------+
| Engine     | Support    | Comment                                                                          |
+------------+------------+----------------------------------------------------------------------------------+
| MyISAM     | DEFAULT    | Default engine as of MySQL 3.23 with great performance                           |
| MEMORY     | YES        | Hash based, stored in memory, useful for temporary tables                        |
| InnoDB     | DISABLED   | Supports transactions, row-level locking, and foreign keys                       |
 

And when I run the query mysql> show engine innodb status; the result was "ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined" even the skip-innodb was not mentioend in the cnf.

Skip-innodb was not mentioned in the my.cnf file by while I execute the show engine innodb status; I am getting the below result which says " Cannot call SHOW INNODB STATUS because skip-innodb is defined".

mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

opdahl's picture

InnoDB on TKL LAMP 11RC

L. Arnold mentions that Adrian set him up with a webmin-mysql install that gott InnDB working on his system. Is there something similar that I can do? I have a TKL LAMP system on to which we are currently installing vTiger. We've just entered in all of our users (around 80) and went to do a maintenance release of vTiger to 2.5.1 that requires we switch to InnoDB as our engine. It's not showing up as available at all in our image and 'enabling' it from within Webmin breaks MySQL just as it did for L. Arnold.

Thanks -- and you guys have a great overall product here!

Jeremy Davis's picture

I'm not sure whats going on & MySQL is not my strength...

But you should be able to recreate the MySQL setup in Adrian's Magenta patch by simply using the Webmin module. webmin-mysql is a Webmin module for administering MySQL (and webmin-apache is for configuring Apache2). If it contains a slightly updated MySQL install then you should be able to update that using apt eg:

apt-get install mysql-server

You also need to update the package database (apt-get update) and you can include the installation of the Webmin module too if you want. You can do it all in one line like this:

apt-get update && apt-get install webmin-mysql mysql-server

if it says that MySQL is already at the newest version then it is.

PS it can all be the one line. the "&&" basically means 'do the bit on the right once the bit on the left has finished'.

opdahl's picture

Mystery Solved...(Sadly)

Thanks for the advice and support everyone.

So, with enough poking around today I found this:

http://forums.innodb.com/read.php?3,1241,1243

Here's a succinct quote:

There is a innodb_plugin shipped with Ubuntu 10.04. The problem is it's 1.05 and they have no plans of ever updating it...In fact according to what they said they will never ship a newer version of mysql than 5.1.41 for the 10.04 LTS Which means it will be years before we can use innodb_plugin in a supported manner on Ubuntu LTS.

Ouch. Except that if you click through the actual conversation, it seems that you can compile a recent version of MySQL for yourself that would contain all the latest-n-greatest stuff. If you don't, you need to use an old version of the plugin and deal with any instabilties that may create.

I'm not nearly brave (nor competent) enough to try compiling MySQL and then recreate all the necessary configs to get it running in TKL like it is now (Stable, secure, etc.). Now if there were a script... :-)

Jeremy Davis's picture

I guess that explains it

And as for versions, the way that Ubuntu (and usually Linux in general) works is that the version of software that ships with it is the only version that will be ever be available - sort of! When I say sort of I mean that they will actually provide ongoing support for it - security patches are backported and sometimes even stability patches too, but essentially the version remains the same. While this means that you miss out on new features, the tradeoff is that overall system stability is far, far better and the likelihood of a server being secure is increased (especially with TKL auto installing security updates).

That then means that unless you wish to compile your own version of MySQL (and recompile everytime that a security patch etc is released) you are stuck with what you've got. There are a couple of other options but they are far from ideal. One being set up your own server from scratch using a later version of Ubuntu. Or try to find someone else who has done the hard work for you (ie compiled MySQL ready to go) - the major downfall of this is that you don't know who they are or how trustworthy or reliable they are.

Guest's picture

Hold on there....

From what I've read, the plugin has been available as part of the mysql distro since version 5.1.38, you just need to configure your my.cnf to use it.

Jeremy Davis's picture

My 2c

Technically Jed meister's line is a script:

Only just! Its only 2 lines of code condensed with &&, the first part updates the package database (a list of what packages are available) and the second part installs a MySQL Webmin module (which is basically just a WebUI for changing the MySQL conf file) and MySQL server itself. If MySQL is installed already then it will update it if a newer version is available, although it shouldn't do anything as the most recent update (about a week ago) was a security update so should have been applied automatically already,

Unfortunately TKL joomla 11rc is not taking the mysql-server part (though there is a server built in).

In other words its already at the latest version. It is the same version as is included in Adrian's Magento patch.

I spent a lot of time  (you are welcome to read the lower part of this, but my conclusion is tht this must have something to do with the debian.cnf file (directly above my.cnf) as calling InnoDB as the default, no matter what, kills this version, and any thing apt-get will call, in MySQL. 

Not 100% sure how you reached this conclusion (even after reading below) but I strongly suspect that whatever the difference is, you will find it in the overlay files of Adrian's Magento patch. Either that or there is something specific that the Magento install does.

This line is in System Variables of the two systems. 

ignore_builtin_innodb OFF

Not sure what this means, but assuming that innodb works in Adrian's Magento patch despite this, but not elsewhere then it would seem to me that this is not relevant and is being ignored or overridden somewhere.

The only variable I believe there is left is that Adrian's Magento Build was built from Core rather than perhaps a LAMP build as I expect Joomla was built (because it has a PostFix server also attached).

But the Devs base all appliances on Core so other than extra bits (which I doubt would make this difference) they are in effect the same.

I will peer a bit into the Magento Patch to see if there isn't some other call that was made when installing MySQL.  There is probably an extra line somewhere in the "sources" setup which would tie into why the mysql-server command is not taking.  (I can't find the sources just this minute)

As I said I'd have a good look in the overlay folder and see if there is something in a conf file somewhere there. Not quite sure what you mean by "sources" here?

I just did a call to "php5-mysql" and got the response in my SSH box:

  • "the following packages were automatically installed and are no longer required.
  • webmin-mount
  • use 'apt-get autoremove' to remove them."

php5-mysql is a mysql plugin for php and I doubt it is relevant to your issue. The other remark you make about webmin-mount is also completely irrelevant. This is basically a minor bug in my opinion. It is just another Webmin module which is completely unrelated. You can either remove the webmin-mount package using apt-get autoremove as suggested (or apt-get remove webmin-mount) or mark it as manually installed (ie not automatically installed) by running apt-get install webmin-mount.

Lets get this fixed somehow.

I strongly suggest you do some background reading on Linux, package management, etc so you can get a clearer idea of what you are looking for and what may or may not be relevant. Also posting on the Ubuntu forums may be worth a shot. Perhaps someone else over there has a solution.

Bottom line: if this is really important to you and its not working with TKL/Ubuntu 10.04 then perhaps you need to consider building a server from scratch based on the newer Ubuntu 10.10 server (which AFAIK includes MySQL 5.1.49 & InnoDB 1.07). Adrian's Magento patch may work on that also although I couldn't be sure.

Jeremy Davis's picture

To uninstall mysql-server and remove config info

apt-get remove --purge mysql-server

I am certainly no expert in these things but I don't think the install routine in TKLPatch would make much difference. If you think it will then test by simply creating a script like this (from SSH)

nano install-mysql.sh

then in the empty file that will open copy-paste (or type):

#!/bin/bash -ex
DEBIAN_FRONTEND=noninteractive apt-get -y \
        -o DPkg::Options::=--force-confdef \
        -o DPkg::Options::=--force-confold \
        install mysql-server 

<Ctrl><x> to save and exit. Then make the script executable (first line) and run it (secondline):

chmod +x mysql-install.sh
./mysql-install.sh

The script should run ok (I haven't tested it) but TBH I don't think it will solve your issues - but I hope for your sake it does!

InnoDB failure on LAMP 11RC

 

This thread seemed to finish off  with some rather negative conclusions.

Here is what I found:

1) InnoDB failed to start after install of the TKL LAMP 11RC.

2) The innoDB config in /etc/mysql/my.cnf is basically blank so is of little help.  This initially lead me to wonder if I needed to add in some reasonable defaults to "fix" the problem.  Apparently the defaults are stored elsewhere.

3) If the innoDB engine fails to start the default settings do not show up in webmin, but do appear when the engine starts successfully.

4) I found deleting the /var/lib/mysql/ib_logfile0 and /var/lib/mysql/logfile1 as suggested by the devs above then a mysql restart and I was good to go.  Note that THEN I was able use the Webmin MySQL Server Config to change the default storage engine without issue.  Reversing that order might not be as successful as the default engine would fail. 

5) There are several issues published elsewhere regarding apparmor, plugins etc that proved to be great distractions but were not ultimately helpful.

For me the only issue has been finding the cause of the initial corruption with the innoDB logs during the install.  I have had no further log problems.  Has anyone reported log problems over time?

BTW, just to be explicit.... none of the above is a criticism of TKL but I wanted to change the direction of this thread so others do not give up on InnoDB prematurely.  I suspect the problem is related to an install error and InnoDB's particular nature.

Great product!  Switched from CentOS because of TKL.

Thanks,

 

Richard

Guest's picture

Thanks!

This thread made me sad, confused, and happy.  But long story short, I deleted the two log files you mentioned, changed the default engine to innodb and restarted and all is well. PMA reflects the change and I can start working.

Guest's picture

Problem resolved

I went through SFTP and deleted the log files. Restarted MySQL and everything worked perfectly!

Guest's picture

CentOs

Thanks so much for that tip...I had basic CentOs 5.6 LAMP server installation running locally when a power outtage occured. Upon reboot I had to run fschk -y manually so the O.S. would start.

I guess it must have damaged the innodb logs and the mysqld service wouldn't start without using the skip-innodb option.

I tried every way possible to recover my innodb databases but nothing worked. Sometimes the simplest solutions are the best. Simply deleting them (the innodb logs) did the trick for me.

Guest's picture

I forgot to say kudos to

I forgot to say kudos to Philippe for the tip.

Guest's picture

I lost my previous databases when i installed php with mysql

Hi,

      I already  installed mysql server in my local when in installed php with mysql option my old databases are overrided by the php how to recover my old databases.

plz help me

do send me reply to my email id thank you

Guest's picture

Thanks, plus a tip

Thanks, this fixed the issue for me too. 

Just a tip. It seems that on my installation of Ubuntu there was no error logging for mysql to syslog, a log file or anywhere else. I grepped the whole /var/log directory.

So if you want to catch this error in future, make sure you have error logging set in your /etc/mysql/my.cnf file.

Under the [mysqld] section add:

log-error = /var/log/mysql/mysql-error.log

Assuming your mysql log files live in /var/log/mysql/

 

Thanks again,

 

Mark.

Jeremy Davis's picture

Good suggestion Mark

Perhaps someone who has a TKL appliance in front of them (which comes installed with MySQL) could have a quick look and see if TKL uses the Ubuntu default (I assume that it would). If so perhaps we could suggest this improvement to the devs via a blueprint?

Guest's picture

Drupal7 forum

I have site in drupal 7. The site is very very slow. It consumes almost 100% CPU resources even if only one user is surfing the site.

The site has multiple content types. I have to migrate data from other site so I am using few modules for migrating data.

I am using few more modules like Image cropping, Autosave, Page title, Module for Meta tags etc

I dont think I am using any module which can slow down the site.

Pls help to figure the root cause of slowness and improve the performance of the site.

Jeremy Davis's picture

Is this on a TKL Drupal 7 appliance?

Assuming that it is, then seeing as your question isn't really related to this thread, please start a new thread.

If you are not using a Turnkey Linux Drupal 7 appliance, then you are better off getting help from one of the many official and unofficial sites which specialised in supporting Drupal...

Guest's picture

Right said: delete/rename innodb log to let it restart

After 4 years this page is still useful!

I changed the Innodb log file configuration, to match 25% of the updated innodb buffer pool size that I had just set. Upon restarting Mysql, have_innodb remained set to "NO".

Gone to the innodb log directory, renamed the logs to .bak as pointed out in this comment: Hoke - Tue, 2009/01/20 - 00:22.

It is worth mentioning that Phpmyadmin's "Advisor" page specifies that innodb log files will need to be manually removed if the variable value is changed. What it does not tell, is that if you do not do this, you will not just lose this change: the entire storage engine will not start.   :-)

Guest's picture

InnoDB Issue

Liraz Siri ,

Thanks dude.....

Guest's picture

rm -rf

rm -rf /var/lib/mysql/ib_log*

/etc/init.d/mysql restart

 

 

works best

Guest's picture

innod error after upgrade

rm -rf /var/lib/mysql/ib_log*

/etc/init.d/mysql restart

 

it should work perfectly

Guest's picture

InnoDB

Hi,

You may check this link to diable INNODB in MySQL.

http://cpanelplesk.com/how-to-turn-off-innodb-engine-in-mysql/

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <strike> <caption>

More information about formatting options

Leave this field empty. It's part of a security mechanism.
(Dear spammers: moderators are notified of all new posts. Spam is deleted immediately)