Search code examples
mysqlinnodbmyisam

Is it normal to have a mixture of table types stored in a single MySQL DB?

Whilst debugging daily MySQL crashes I noticed that my database has a mixture of table types.

This seems odd to me but I don't know either how it happened or whether it would cause any issues. Is it something I need to fix?

enter image description here

Solution

  • Well, this is not exactly an error, since you may choose whatever table engine you wish for each table. But you might get into serious trouble with this combination. InnoDB supports transactions, while MyISAM does not!

    In case you don't know what transactions are used for:
    Transactions make sure, that every query in a group of queries are executed successful or none of them are. A typical example is, that a client orders something and his balance is adjusted accordingly. So you'd do something like this on the database:

    START TRANSACTION;
INSERT INTO orders (item, client, price) VALUES ('whatever', 1, 100.00);
UPDATE clients_credit SET balance = balance - 100.00 WHERE client = 1;
COMMIT;

    Putting those two queries inside the START TRANSACTION; and COMMIT; (which ends the transaction), makes sure, that the INSERT is undone should the UPDATE fail.

    In your case however, when one of the tables is of type MyISAM, the change in this table will not be undone and your database is eventually in an inconsistent state.

    Therefore my suggestion is, that you change all MyISAM tables to InnoDB. You can do this easily with

    ALTER TABLE table_name ENGINE=InnoDB;

    Apart from above mentioned problem, MyISAM is slowly dying anyway. Performancewise there's not much difference nowadays and it even has certain disadvantages, like table-locking instead of row-level locking. You can find plenty of information on this online.

    The reason for your mixture is most likely that some table creators specified the engine on table creation with 

    CREATE TABLE (
...
) ENGINE=whatever;

    and others did not or it was forgotten.

    You can however adjust the default engine with 

    SET GLOBAL default_storage_engine = InnoDB;

    Then new tables will be created as InnoDb when an engine is not specified. (You can also put this option in your my.cnf)

    When you test this, note that there's also a SESSION variable. Either adjust that too or start a new session.