Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-23682

encourage admins to not use myisam

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: 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

      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.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            stronk7 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
            stronk7 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            skodak Petr Skoda added a comment -

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

            Show
            skodak Petr Skoda added a comment - Proposal: 1/ use patch with more subtle warning 2/ tweak docs.moodle.org pages that deal with mysql installation
            Hide
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            jtomkinson 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
            jtomkinson 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
            skodak Petr Skoda added a comment -

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

            Show
            skodak Petr Skoda added a comment - yes, we look for enabled engines in this order: InnoDB, XtraDB and only then switch to something else than MyISAM
            Hide
            nadavkav 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
            nadavkav 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
            skodak Petr Skoda 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
            skodak Petr Skoda 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
            net-buoy 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
            net-buoy 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:
                  Fix Release Date:
                  24/Nov/10