Moodle
  1. Moodle
  2. MDL-23682

encourage admins to not use myisam

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Administration
    • Labels:
      None
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Rank:
      36086

      Description

      myisam is a not a good choice for moodle, it will thrash your data sooner or later - this is usually discovered much later during upgrades when it is too late.

        Activity

        Hide
        Eloy Lafuente (stronk7) added a comment -

        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

        Show
        Eloy Lafuente (stronk7) added a comment - 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
        Hide
        Petr Škoda added a comment -

        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.

        Show
        Petr Škoda added a comment - 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.
        Hide
        Petr Škoda added a comment -

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

        Show
        Petr Škoda added a comment - Proposal: 1/ use patch with more subtle warning 2/ tweak docs.moodle.org pages that deal with mysql installation
        Hide
        Petr Škoda added a comment - - edited

        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

        Show
        Petr Škoda added a comment - - edited 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
        Hide
        Petr Škoda added a comment -

        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.

        Show
        Petr Škoda added a comment - 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.
        Hide
        Petr Škoda added a comment - - edited

        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

        Show
        Petr Škoda added a comment - - edited 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
        Hide
        Jordan Tomkinson added a comment -

        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 ?

        Show
        Jordan Tomkinson added a comment - 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 ?
        Hide
        Petr Škoda added a comment -

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

        Show
        Petr Škoda added a comment - yes, we look for enabled engines in this order: InnoDB, XtraDB and only then switch to something else than MyISAM
        Hide
        Nadav Kavalerchik added a comment -

        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?

        Show
        Nadav Kavalerchik added a comment - 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?
        Hide
        Petr Škoda added a comment -

        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.

        Show
        Petr Škoda added a comment - 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.
        Hide
        Marc Grober added a comment -

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

        Show
        Marc Grober added a comment - Why isn't the notice, which is pretty scary, live linked to the innodb.php page with a full explanation?

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: