Without MySQL engine InnoDB, is Drupal 6 Concurrent-User (Transaction) safe?
jeff00seattle - March 13, 2009 - 14:38
Hi
I have just discovered that Drupal 6 uses MySQL engine MyISAM; and MyISAM does not support transactions.
That concerns me greatly because I am development site whereby multiple people will be performing concurrent updates.
By default:
- Are Drupal 6 sites transaction (ACID) safe?
- Can Drupal 6 sites be configured to be transaction safe?
- Can Drupal 6 sites converted to use MySQL InnoDB because it supports both transactions and foreign keys?
Thanks
Jeff in Seattle
a. NO, not out of the box.
a. NO, not out of the box.
b. Yes
You can convert your database to innodb to support transactions
You can use http://drupal.org/project/transaction to enable transactions on your website.
See also http://drupal.org/node/51263 , http://drupal.org/node/355875.
c. see b
BobbyMods
Concern about converting MySQL database engine to InnoDB
Hi EC
Thanks for your reply.
I have a concern about modifying the MySQL database engine from MyISAM to InnoDB behind the back of Drupal.
Thanks
Jeff in Seattle
Jeff in Seattle
a. Database engine change is
a. Database engine change is a MySQL database server tuning issue, and should not be recorded in Drupal(my 2 cents)
b. It should not cause a conflict, the innoDB vs myISAM is on a different level (dbserver) than the drupal db update (application).
However you should be careful which tables to set to innoDB and MyISAM and also what to put into a transaction.
exmpl.
A users table is not updated that much compared to how much it is read, so innoDB would give little gain here(only gain in size depending on how you build indexes). But If your transaction requires it....
A heavily used blog can have locking problems with the comments file on MyISAM, switching this table to innoDB and using record locking may give some speed to the application, because now the whole table is not locked with every insert, AND there is a high level of inserts.
I can't say anything about your transaction requirements. Those are requirements specific to the application you are building.
BobbyMods
Transaction Module: Will Enabling make site Transaction Safe?
Hi again EC,
If I install and enable Transaction module and not modify MySQL engine (remain as MyISAM), then will this make a Drupal 6 site transaction-safe; i.e. allowing to have concurrent updates without conflict and maintaining data integrity?
Thanks
Jeff in Seattle
Jeff in Seattle
_
From the 1st paragraph on the transaction module project page:
It also requires the InnoDB engine as MyISAM tables do not support transactions._
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
Understand that MyISAM does not support transactions
Hi Joann
I already do understand that MyISAM does not support transactions.
My concern is modifying the MySQL engine from MyISAM to InnoDB behind Drupal 6's back.
Thanks
Jeff in Seattle
Jeff in Seattle
_
I believe it's ok-- d.o has some InnoDb tables I believe, but I'm not 100% sure (which is why I didn't respond to that comment). I was responding to this question:
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
Drupal's Schema API
Hi Joann
Do you know if when defining a $schema['table_name'] array, if it is possible to include declaration of MySQL engine type to be applied to the table to be created or updated?
For example:
$schema['schema_name'] = array(
'description' => t('----'),
'fields' => array( ... ),
'primary key' => array( ... ),
'unique keys' => array( ... ),
'indexes' => array( ... ),
/* Additional fields if using InnoDB */
'foreign keys' => array( ... ),
'mysql engine => t('InnoDB'),
);
Thanks
Jeff in Seattle
Jeff in Seattle
Drupal core uses an
Drupal core uses an abstraction layer, and how you tune your database server should not be in there.
There is talk of setting up innoDB as default in drupal 7.
If you are using a dedicated server and can tune your innoDB to have its indexes in separate files , I would do that for the whole site, and not just for some files.
Just my 2cents.
BobbyMods
Migrating for Drupal 6 to Drupal 7
Hi EC
If I were to migrate from Drupal 6 to Drupal 7, then am I not limited to what modules are ready for Drupal 7?
Thanks
Jeff in Seattle
Jeff in Seattle
_
Yes-- and d7 is not suitable for use yet, it's still being very actively developed.
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
Yep, thats the other elephant
Yep, thats the other elephant in the room.
You can be only sure of core modules and some other highly used modules (like views, cck). If you look into the issue queues and statistics you'll be able to determine what modules are not really maintained/popular, and you can always update them yourself.
But it is wise to allow for non-core modules to 'lag' behind core.
But you can switch to innoDB now with drupal 6.
BobbyMods
a. Yesb. No, it is a
a. Yes
b. No, it is a database server configuration. You could just set it in phpMyAdmin.
c. .install files should not configure a lower layer db server. My gut tells me YES for a new install and NO for an update. Personally I would like to see this set at a different level.
BobbyMods
I feel that the best you can
I feel that the best you can do is just make a test environment and switch that db to innodb, and run some tests with the modules you want to use.
Then you will know for sure if your modules will work, if your transactions will work, and you can also test your transaction recovery.
The only real problems I have seen is when combining MyISAM and innoDB in one DB and not thinking it out completely, you can then have rollback id problems, but if you go totally innoDB (and optimize your indexes for it), the most you will have is resource usage problems (in general not really fit for shared hosting).
BobbyMods