xbmc/xbmc — pull request #3967 by Montellese (2014-01-05 22:00:10+00:00)
Title: mysql: set engine to InnoDB to ensure the use of constraints

This sets the database engine used for created MySQL tables to InnoDB. I'm lazy so I'll just copy the reasoning from http://trac.xbmc.org/ticket/14814 :

> It seems when auto-creating the MySQL database schemas the option "ENGINE=" isn't explicitly set in the CREATE TABLE statements. Therefor MySQL uses the default engine set on the MySQL server.
>
> This is bad for several reasons:
>
> 1) You cannot know which engine is the default on every server; Might be MEMORY or even BLACKHOLE. Either of these will break XBMC completely.
>
> 2) Both DB schemas rely heavily on constrains, wich is a good thing (MyVideo75 has 380 constraints, MyMusic32 has 159). But constraints are only possible with the InnoDB engine.
>
> 3) Many home users use the MySQL DBMS running on their NAS. Many of these boxes still have MySQL 5.1 running (example: Synology), even with the latest firmware. In MySQL 5.1 the default engine was still MyISAM which cannot make use of the 539 constraints xbmc uses in its two schemas.
>
> 4) Falling back to MyISAM is bad for performance reasons. With every INSERT/UPDATE/DELETE operation the whole table gets locked; so whenever there is a write operation on a table, every read operation has to wait. This is bad for performance, especially when you have multiple HTPCs which is what having a central MySQL database is all about.
>
> 5) Having 539 constraints in the two schemas suggests that they are made use of; i.e. relying upon the "ON DELETE CASCADE" statements to cascade DELETE operations into referencing tables. If so, falling back to MyISAM silently descards the constraints and therefor could leave the database corrupt as reference integrity is no longer ensured.

The only issue I see is that existing MySQL databases won't necessarily have InnoDB set as the engine.