By jmilotaylor on

Hi everyone - my site is has fallen over

http://apo33.info/drupalsound/

I'm getting this -

Error

The website encountered an unexpected error. Please try again later.

Error message PDOException: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB': SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 167 of /var/www/drupalsound/includes/lock.inc).
Uncaught exception thrown in shutdown function.
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB': DELETE FROM {semaphore} WHERE (value = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 508681914ec570d29df719.64280521 ) in lock_release_all() (line 269 of /var/www/drupalsound/includes/lock.inc).

I'm a newbie and have no clue what has caused this - can anyone help?

Best regards
J MIlo Taylor

⋅ Categories: Drupal 7.x

Comments

john_b’s picture

john_b commented

What happened before this error appeared? It is not clear what causes it. It may be that InnoDB engine is not enabled for your database. Have you updated or changed your MySQL configuration? That is maybe the first thing to look at.

If there has been no change or updating to the server, then maybe the problem is in Drupal after all. But something must have been changed if the site was working before.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

jmilotaylor’s picture

jmilotaylor commented

hi john,

thanks for the comments - i had been trying to get audiofield working - then updated the home page with a post - then everything fell over.

i did not update or change mysql config - at least not intentionally. i'm lost here, how might i track down the problem? any thoughts would be most welcomed.

best regards,

milo

john_b’s picture

john_b commented

Ah. You do not say whether anyone else could have changed the server setup. Do you run your own server, or is it shared hosting (where the hosting company would have access to change the setup)?

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

jmilotaylor’s picture

jmilotaylor commented

hi there,

the site is hosted with some colleagues - i'll ask them if they made any change.

thanks' once again,

jmt

alviSic’s picture

alviSic commented

Hi!

I have now the same error message as described here, and I didn't do anything that could cause it.

How did you fix the error?

Thanks,
Alvis

WriteCo’s picture

WriteCo commented

I'm getting the error now, too. I called my host, AN Hosting, and they said they are doing something with MySQL. I had no notice that they would be doing this during the day, so I guess there must be some sort of database urgency.

metality’s picture

metality commented

Drupal root folder -> includes -> database -> mysql -> schema.inc
i changed 'mysql_engine' => 'INNODB', to 'mysql_engine' => 'MyISAM'.
restarted mysql service.

this fixed my problem.

hadi farnoud’s picture

hadi farnoud commented

so INNODB engine is to blame then? curious why Drupal made the db with that engine anyway

dfludd’s picture

dfludd commented

I just finished and went live with a website a day ago only to find this error the next day. I tried the fix mentioned below Posted by metality on January 4, 2012 at 7:26pm new

Drupal root folder -> includes -> database -> mysql -> schema.inc
i changed 'mysql_engine' => 'INNODB', to 'mysql_engine' => 'MyISAM'.
restarted mysql service.

But this did not work I ran a check on the database it seems the entire structure has become corrupt!!!!! What is this? The site was fine a day ago??
Any thoughts would be very appreciated. PS: I also made a backup of the site in Drupal 7 and the same thing has happened to the backup????????????

_drupal.access
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.actions
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.authmap
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.batch
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.block
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.block_class
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.block_custom
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.block_node_type
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.block_role
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.blocked_ips
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.cache
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.cache_block
Error : Unknown table engine 'InnoDB'
error : Corrupt
_drupal.cache_bootstrap
Error : Unknown table engine 'InnoDB'
error : Corrupt

john_b’s picture

john_b commented

Not sure why it worked for metality. Your best bet is to undo your change. If that does not fix it, you may then need to restore the database from the most recent pre-change backup. Then debug the probem properly....

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

dfludd’s picture

dfludd commented

IN MY CASE I GOT IT BACK. THERE WAS A SCHEDULED CRON JOB RUNNING ON THE SERVER THAT I DELETED AND I SEEMED TO RESTORE EVERYTHING.
THANKS FOR YOUR QUICK REPLY!!!

john_b’s picture

john_b commented

If you have not done so you are still better off reversing the change in db engine you made, though it may not stop your site working. Anyway it will be overwritten next time you update Drupal core.

Almost always when I see these errors they are down to insufficient server resources for the running processes, even though all my sites are all on a linode with 1.5GB RAM.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

chrisrikli’s picture

chrisrikli commented

I've seen this error frequently when doing database tuning.

It's a result of having changed the innodb_log_file_size parameter after having already set up the site. MySQL will load but will fail to start up the InnoDB engine because it's already created log files based on a different parameter.

The solution is to find the log files and delete them. The log files are located in the data directory for your MySQL install and are usually like "ib_logfile0" "ib_logfile1" etc. Back up everything first, delete these files, and restart MySQL. You should be good to go.

Don't hack core, and don't change the storage engine Drupal is using to MyISAM unless you absolutely have to; InnoDB was chosen as the storage engine for a number of reasons. One big one is that MyISAM locks an entire table during a read/write, therefore incoming requests get queued up waiting for tables to unlock. InnoDB locks at the row level, meaning that simultaneous queries can be run against the same table. Given Drupal's heavy db I/O InnoDB is the better choice.

wolf29’s picture

wolf29 commented

same error
I did not change the password of the drupal db user,
but after I did that password reset and run a data-dump successfully, the site fell over.
I did not see any way to change the database type as shown above, so I didn't hack core (yet).

Drupal 7.12
Ubuntu 10.04
Mysql 5.1.61

j2b’s picture

j2b commented

Thank you!

I've run into similar issues after trying to de-fragment InnoDB tables, in addition changing and introducing innodb_log_file_size parameter after the server (MySQL and Drupal) was working ok. The outcome of the problem resulted in your mentioned issue. Removing this parameter from my.cnf and restarting MySQL recovered operations without any other intrusions in database content.

Guys, playing around with working MySQL and tunings!!! Please read manuals and descriptions for parameters before any changes to your configuration!!! Do not rely on blogposts saying it's easy! Check out anything you change, and understand the reason for such. And only then make appropriate changes :)

As well, do not forget about backups, backups... Haven't I mentioned backups yet? It's a shame on me, that I have to learn from my own mistakes!

biteuljiouce’s picture

biteuljiouce commented

thanks Chrisrikli, it works for me.

I deleted ib_logfile0 and ib_logfile1 and restarted mysql.
They were in /usr/local/zend/mysql/data

The strange thing is that I didn't change any parameter. But I just installed Symfony 2 ... maybe it tried to ?

jeffpurtle’s picture

jeffpurtle commented

I am having the similar error and it is effecting more than one site on different databases that were never touched for months. Why did this happen?

john_b’s picture

john_b commented

If you did not change the site you should talk to the people who run the server. Often servers are set to update mysql automatically, and I have seen that cause database problems. If you are running the server, post more details. If someone else is managing the server it is their job to fix it IMO.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

john_b’s picture

john_b commented

If the database were never touched manually for months, check the server to see whether an automated software update, of the kind which cPanel and Plesk may initiate, has updated to a more recent version of mySQL. This can crash sites in my experience, not because of a fault with the new version of mySQL but because the process of updating the databases can go wrong and lead to corruption (especially if the database server is short on resources). If mysql has been running only since the date the problems started (which you can check if you manage the server, or ask the hosting company to check if you hire someone else such as a hosting company to manage your server), that is a clue that there may have been a software update.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Joram’s picture

Joram commented

In my case i couldn't query any of the tables in any of my drupal databases.
Using command "show engines" in mysql, i could see that innodb was turned "Off".

In my MySQL "my.ini" file, the line "skip-innodb" was uncommented because of another project i was working on.
Commenting this again fixed the problem for me.

vali hutchison’s picture

vali hutchison commented

This worked for me when I had this issue on a Centos VPS using WHM and cPanel - http://brettmillsap.blogspot.co.uk/2013/03/cpanel-innodb-engine-fails-to...

scottbaetz’s picture

scottbaetz commented

When the table types are InnoDB, there is a relationship between the database and apache. It can happen that you have potentially restarted mysql, but not have enough memory to accommodate the size of the InnoDB and apache.

So, take a moment and a breath. Stop Apache, restart Apache, and then restart Mysql. Could solve everything. No need to delete log files... I've had to do this a few times.

Best regards,

Scott Baetz
Web Developer
sbaetz@admininternet.net
AdminInternet
http://www.admininternet.net

avig’s picture

avig commented

Hopefully it will last :-)