Database engines - mixing them.
Moderator: General Moderators
Database engines - mixing them.
Would it be bad practise to mix DB engines in the same database? For example, in some tables (a shopping cart for example) I'd rather like to have access to transactions so InnoDB is a logical choice. On the other hand, in other tables (my audit table that tracks what people do on the site for example) I'd like it to be speedy, not be held in memory and have access to delayed inserts so MyISAM is the obvious choice. But both in the same database? Is that asking for trouble?
Re: Database engines - mixing them.
Depends.onion2k wrote: Is that asking for trouble?
A problem can arise when mixing transactional and non-transactional tables. Data is not committed to a transactional table inside a transaction until you call commit on that transaction. Data is committed to non-transactional tables before the INSERT/UPDATE query return. If you insert/update both transactional and non-transactional tables inside a transaction and then you call rollback or your transaction fails there is a good chance that your non-transactional tables reference data that does not exist.
An example ...
Code: Select all
START TRANSACTION;
/*Transactional table with AUTO_INCREMENT field. */
INSERT INTO TransactionalTable SET var='value';
/*
** Your php code calls mysql_insert_id or you use other
** method to fetch the last AUTO_INCREMENT id
*/
/*Non-transactional table */
INSERT INTO NonTransactionalTable SET idFromTransactionalTable = 5;
COMMIT
/*
** WOOPS. Your commit fails but the non transactional table
** still references an id that does not refer to a row
** or refers to a row that was unintended
** (due to the way MySQL deals with AUTO_INCREMENT it is most
** likely the former )
*/