change default Database driver to allow transactional DB
Currently the only option we have when installing is using mysqli driver through MDB2 and this driver generates MyIsam tables which do not support transaction.
This is probably not a problem in most cases but that would be great if there was an option to select InnoDB tables when running a test environment so that each testcase could be run in DB isolation inside a transaction which would be rolled back after the test case (and never committed if the test case ends up in a Fatal Error )
Putting this option in test environment is not really a refactoring as we do not yet have different environments (...) but why not using InnoDb by default for dev and prod environment also ?
Please give your opinion on that idea.
Updated by Hans De Bisschop over 8 years ago
- Category set to 21
- Target version set to 7
Actually, MDB2 doesn't really care whether it's InnoDB or MyISAM. It even has support for transactions. It's MySQL that actually defaults to MyISAM out of the box. As soon as you change that default to InnoDB, Chamilo 2 will "mysteriously" start using InnoDB.
MyISAM vs. InnoDB has been discussed in length all over the internet. There's things to be said for both. None takes the upper hand IMO. From personal experience I find that InnoDB takes a lot more tweaking of the MySQL server to actually be useable then MyISAM. One of the most basic things that bothers me about InnoDB is it's default to store all data in one huge (set) of files, instead of splitting things up per database and per table. That behaviour is easy enough to change, but it requires access to the MySQL configuration.
At any rate there's no real problem with using transactions somewhere in the future ... MDB2 should just emulate them (somehow, haven't looked at the mechanics) if the storage engine doesn't support it. We should definately look at the how and what though. I'm not excluding the possibility of an alternative database driver (I even think it's a logical evolution), but taking into account the whole picture right now, I'd label that rather long term ... :)
Updated by Goulwen Reboux over 8 years ago
My main concern about MyISAM are:
- Data integrity with the lack of both transactions and foreign keys;
- Scalability as MyISAM locks a complete table when writing whereas InnoDB locks only the affected rows.
Both have more to do with big installs so I agree that InnoDB is more for people who can configure the server, because they will have to do this anyway.
On the opposite, the main problem with InnoDB is not technical: Oracle has a long standing FUD strategy with InnoDB. Now that it has purchased MySQL with Sun, it's not really the problem, but for the whole MySQL…
So my main concern becomes that we need a driver that support others databases and PostgresSQL comes as the first alternative. Once we have a DBAL, MyISAM or InnoDB would be more a matter of personal preference.
Updated by Anonymous over 8 years ago
I agree that InndoDB might not be THE solution and basically there is no silver bullet in that field. My point was mainly that in a very near future Transaction could be a very big improvement for making tests easier and much more performant.
According to this page - http://pear.php.net/manual/fr/package.database.mdb2.intro-transaction.php - MDB2 cannot emulate transaction if the underlying rdbms/engine does not support it :(
Hans could you elaborate on the disadvantages on using InnoDB as default engine ?