At the moment, Moodle has an API for database transactions, but you are not allowed to rely on it because we have to support MySQL with MyISAM storage engine.
It occurs to me that since we have just released a long-term support release, then if we are going to drop support ofr MyISAM, now might be the best time to do it.
What do other people think?
If there is enough support for the idea, I will make a tracker issue.
+1
But bear in mind this will not solve some of the developer difficulty with transactions - the ONLY thing it will solve is that there are a few places in core where we do 'if something fails, then delete all the other stuff we've done up until now', which we no longer need (if in a transaction and you throw an error this happens automatically).
In particular it won't mean that we are able to rollback transactions as part of normal operation (i.e. normally you will only be able to do a rollback by throwing an error to cancel the entire page). This is because of nested transactions, which cannot be rolled back. I.e. you can't make a function that relies on rollback working just within that function, because whoever called the function might have already started a transaction, and then you can't rollback the inner transaction on its own.
Tim knows this already, but just saying for public benefit or something.
There's nothing stopping us doing this already, but this change might possibly also be an impetus to add transactions to a few more bits of Moodle that might currently be missing them, to improve database integrity (&, slightly, performance), which would be good. And transactions could be included as a requirement in the coding standards (i.e. any action which includes more than one related database write should be wrapped in a transaction).
--sam
+1 to Sam's post!
Not to mention the Files API too, where rolling back means rolling a transaction on the database and an action on the "file system", wherever the pool is stored. The design of the pool hosted in the file system can live with files no more referenced in the logic representation within the database but not always the opposite: the Files API implementation is quite mature and stable but it could be useful to review that code too.
HTH,
Matteo
var $foreign_keys=false
lib\ddl\sql_generator.php
+1
MyISAM has so many limitations (like table-level locking only, poor system failure integrity, etc), it just plain lost the game.
Death to MyISAM.
+1
+1 sort of...
My outstanding concerns are that...
When InnoDB breaks it breaks properly. You're usually reloading from backups. MyISAM quite often responds to a 'REPAIR'. As we know, lots and lots of users don't make backups.
A proper job is done with enforcing key relationships. I'm thinking of Mahara where the database design is a bit more advanced and it can be quite a headache to 'repair' any database issues as some of the references are very convoluted.
putting aside the pros and cons for MyISAM & InnoDB outlined here, my simple understanding being InnoDB sounds more accurate-(able to work with rows/as opposed to the restriction of bulky table stuff- and what-not) then I suppose the question remains: is the time right?
Given Marcus's point:
'When you are using innodb it breaks the install, and even if that is fixed, turning on Foreign Keys might break a bunch of plugins unexpectedly.'
&
Atto's affordance for the creation of sub-plugins
&
In view of Howard's point: users need to be encouraged to back-up
If all this squares up nicely
then why not?
D
+1 ...but anyone who posts on this forum saying anything otherwise should unsubscribe themselves to from this forum!
I'm being facetious, but my point is serious - as developers we're often very aggressive to break things for our convenience at the detriment to the number of users who will be able to continue to use our software. This is still causing a problem with the number of sites who have moved off Moodle 1.9...
Having said that, it looks like we've been warning about MyISAM since 2010 (MDL-23682). I think thats sufficient warning for sites really and in this case, it brings significant benefits to our users too.
This is not just breaking things for the convenience of developers. It is more like when we forced people to turn of register_globals and magic quotes.
+1
you can't run a real site with MyISAM and MariaDB driver is already not compatible with MyISAM.
Just to note:
I've found https://tracker.moodle.org/browse/MDL-30799 that is about better/fix foreign keys support, and https://tracker.moodle.org/browse/MDL-41310 that is about dropping oracle support ;)
I created MDL-46064 to move this forward.
+1
I expected some people voting -1 here, the lack of -1 should count as extra ++
It's also possible that people who will suffer from this change (most of them not technically-savvy enough to update their software infrastructure or totally relying on shared hosting) absolutely don't know the consequences and thus can't vote -1 at all.
1. The main questions are still "Will this change allow for faster processing speeds for data requests?" and "Will this change reduce immediate resource demands?" One may be able to offset the other, but if both are true, then why not?
2. Support for MariaDB certainly seems to be growing recently, and as MyISAM is no longer the default driver for MySQL then it will become increasingly problematic as a useful tool in any app that uses older versions MySQL. Users will have to update their DB at some point and as we have seen with the demands on updating PHP in the transition from v1.9 to v2.0 this is not always easily accomplished- usually by Users on shared servers.
3. While hdd space is marvelously cheap, and is still declining, devs should still be mindful of it. My understanding is that deletes in MyISAM are left as blank spaces until the DB is optimized, which takes time and processing power. As Moodles do not get smaller over time, this can become somewhat problematic if an overnight cron is not properly set up- which leads to yet another problem with MyISAM.
4. MyISAM may be OK for organizations that have large numbers of reads and smaller numbers of writes, like schools and the like, but increasingly, the peaks and valleys of demand that traditionally flow with the day/night cycle are slowly being eroded by the nature of the changing learning world. I am certain that international enrollments, say at something like the OU for example, are increasing, so the time window when the optimize code can be run to refresh the db is, without significant impact on User experience, becoming smaller. This may not be an issue today, but I strongly suggest it will in the not too distant future. Internationalized MOOCs may or may not be becoming more popular, but Moodle should be able to accommodate them so can it do so while retaining support for MyISAM?
Please Note: I don't know if any of this is relevant to Moodle, not being a dev it is hard for me to say, but these are the sorts of issues that I would be considering I suspect, if for no other reason that to ensure that my app is remaining constant, or consistent, with identifiable technical, social, financial developments that will impact upon my app.
