Moodle

Do you think we need to be so "radical"?

I can see the point about recommending to switch to InnoDB (or any other ACID engine), but Moodle has worked under MyISAM for ages and IMO should continue working, at least until 2.1 (I think we agreed about that in the past).

Also note that some of the weirdest effects of MyISAM like silent truncations/castings are already avoided by setting the connection in STRICT_ALL_TABLES sql mode.

So, I'm not sure if the message is "myisam is a really bad choice" or "innodb is better for concurrency and other advanced features" instead.

So +-0 here. Ciao

the worst thing is that it is loosing data and breaking upgrades, for example ppl on qa site were reporting various strange problems caused by table corruptions, repairing tables does not fix stuff because the data can be borked a lot in the meantime.

most crashes were in text cache table, but imagine problems if the new files table crashes? you would easily loose a lot of files...

the text should be improved of course, that is why I did not put it into lang pack.

Proposal:
1/ use patch with more subtle warning
2/ tweak docs.moodle.org pages that deal with mysql installation

What is wrong with MyISAM? Pretty much everything if you care about your data:

1/ it does not support transactions - webservices rely on transactions to clean up after invalid requests, only because of MyISAM we did not fix those ugly double click problems yet, ...
2/ it is not atomic - mysql can fail or be stopped in the middle of UPDATE or DELETE statement - our code is not designed to deal with this, you have to recover manually
3/ mysqldump is a joke - it can not produce any reasonable backup without stopping apache or blocking access to all tables (please note you MUST use --single-transaction for innodb tables!); even with innodb you can not run upgrades at the same time
4/ repair tables does NOT fix any data - because it is not ACID engine you can not actually fix the data, there is no way to validate that the "repaired" tables have any meaningful information, the repair only makes your tables readable/writable again, it does not guarantee reasonable state of your data

New code is in CVS, new installations default to InnoDB instead of MyISAM. New tables are created with the same engine type as the config table - this should finally solve the InnoDB migration issue.

Going to add CLI innodb conversion tool and imrpove docs now.

New CLI migration tool is in CVS, I have searched through the docs, the InnoDB is already recommended. I think we do not need to change the docs because if the MyISAM is the default database engine, we actively search for InnoDB and use it unless admin specifies the MyISAM in dbobptions explicitly.

Summary:
1/ old sites with at least one MyISAM table are warned on the admin/index.php page
2/ there are two tools for migration - the web UI for innodb only, the CLI script that handles any engine migration and list current engine for each table
3/ new installations ignore MyISAM as default DB and use INNODB or XtraDB instead
4/ when adding new tables during upgrade we always use "current" engine detected from the config table
5/ mysql transaction support detection is more intelligent now
6/ you can for override engine and transaction detection via dboptions - no queries used == better perf
7/ the recommendation is a lot less radical than in the patch above, feel free to tweak it as necessary

I hope this will make the transition away from MyISAM as painless as possible.

Thank you for the feedback and ideas!

Petr Skoda

regarding 3/ new installations ignore MyISAM as default DB and use INNODB or XtraDB instead

what happens if someone has disabled other engine via my.cnf, leaving only option as myisam - is the install going to catch this or fail to install ?

yes, we look for enabled engines in this order: InnoDB, XtraDB
and only then switch to something else than MyISAM

FYI

Just upgraded Moodle 1.9.x DB from using Mysql 5.0 to 5.1 (on CentOS 5.4 64bit)
a few of my tables were using InnoDB, since i really need it (like mdl_user)
and while upgrading... all the data in the tables were corrupted. (lost or deleted)
(yes. the InnoDB tables not the MyISAM tables)

I think, that we should have some serious check on several scenarios before
using InnoDB fully. Don't you think?

MyISAM is not ACID compliant, sorry.
The solution is very simple, use ACID compliant database that does not thrash your data. I personally prefer using PostgreSQL.

Why isn't the notice, which is pretty scary, live linked to the innodb.php page with a full explanation?