InnoDb in Drupal

Posted by shyamala on August 9, 2008 at 4:31pm

How do you convert a MyIsam table to Innodb tables in Drupal? Though we have carried out the configuration changes, the changes have not taken effect.

Shyamala

Comments

ALTER TABLE tablename ENGINE = InnoDB;

Posted by markus_petrux on August 9, 2008 at 5:47pm

You have to issue ALTER TABLE for those tables that you wish to convert from one engine to another. It would look somthing like this:

ALTER TABLE tablename ENGINE = InnoDB;

Just make sure you have working backups, just in case, and properly dimensioned InnoDB tablespaces.

InnoDB isn't always better than MyIsam

Posted by Etanol on August 9, 2008 at 6:54pm

You should choose InnoDB over MyIsam for a table in two situations only:

you need transactions (not really the case for a casual Drupal user)
there are a lot of inserts or the inserts are very long - myisam locks the table, innodb only row

Also innodb is a bit slower on Select and massively slower on count

Don't forget

Posted by webappl on August 9, 2008 at 11:23pm

Here is a very good note from "Pro Drupal Development" book, CHAPTER 22 - OPTIMIZING DRUPAL, p.347:

Note Since Drupal still uses the LOCK TABLE command with InnoDB tables, be sure to disable autocommit mode within MySQL, or MySQL and InnoDB will both take on table locks.
See http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html for more information.

Good Luck,
Alex Pandre
Custom Drupal modules and themes development, Optimization, Version Control.
Join MySQL group and post your suggestions in related groups in the same time.

Alex Pandre
Custom Drupal modules and themes development, Optimization, Version Control.
Join MySQL group and post your suggestions in related groups in the same time.

I read this : Since Drupal

Posted by shyamala on August 10, 2008 at 12:26am

I read this : Since Drupal still uses the LOCK TABLE command with InnoDB tables, be sure to disable autocommit mode within MySQL, or MySQL and InnoDB will both take on table locks.

but still figuring our how to fix it?? How do we release the lock or disable autocommit mode?? is it just a mysql setting?

Netlink Technologies Ltd
http://shyamala-drupal.blogspot.com/

Shyamala
Unimity Solutions

http://www.oreillynet.com/dat

Posted by sinmao on September 19, 2009 at 7:33pm

I know it's kinda late, but for future users ...

http://www.oreillynet.com/databases/blog/2007/02/mysql_transactions_and_...
(go to the section on disabling autocommit)

It involves editing your my.cnf file and including this bit of code:
[mysqld]
init_connect='SET autocommit=0'

...

Posted by calebgilbert on January 13, 2010 at 4:00am

init_connect does some really bad things with sites - can't login, can add nodes, etc. I'm sure there is some way to use this setting sensibly but I haven't been able to find anything that addresses the shortcoming/problems. Fyi

UPDATE: Looking at this page, sounds like the way to control autocommit, at least in Drupal, is on a per query basis rather than globally within my.cnf.

Yeah, you're right. For some

Posted by sinmao on June 4, 2010 at 1:15am

Yeah, you're right. For some reason, it ended up causing a bunch of problems with my site. I'll try out the solution you mentioned.

better to apply the no-lock patch

Posted by oliver soell on August 11, 2008 at 11:15pm

so core won't lock tables:

drupal.org/node/55516

and you'll also want to leave node_revisions as myisam (has a fulltext index on it which is not supported by innodb) and search_index (which gets lots of select *) as myisam.

cheers,
-o

Drupal 7 is gonna have InnoDB

Posted by everyz on July 15, 2010 at 3:57pm

Drupal 7 is gonna have InnoDB as default table engine!

force queries to retry X times

Posted by doublejosh on November 11, 2011 at 7:13pm

BTW: Here's a patch to force queries to retry X times if they fail due to table deadlocks, etc.
http://drupal.org/node/476048

Looks like patches for both D6 and D7.