Moodle
  1. Moodle
  2. MDL-29516 DB layer improvements 2.3 META
  3. MDL-31985

remove text and binary field size from XMLDB - always use big size

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.2
    • Fix Version/s: 2.3
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Testing Instructions:
      Hide

      1/ run all tests for mysql
      2/ run functional DB tests for all database
      3/ try upgrade from 2.2 in mysql
      4/ try install on mysql
      5/ edit some TEXT columns with the XMLDB Editor. Verify that no length is available for them, and XML is saved and later loaded ok.

      Show
      1/ run all tests for mysql 2/ run functional DB tests for all database 3/ try upgrade from 2.2 in mysql 4/ try install on mysql 5/ edit some TEXT columns with the XMLDB Editor. Verify that no length is available for them, and XML is saved and later loaded ok.
    • Affected Branches:
      MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w11_MDL-31985_m23_lobsizes

      Description

      There are constant problems with 64k texts running out of available space in MySQL, the solution is to use only big text fields

      Benefits:

      • no more running out of space in text fields
      • all databases the same behaviour (only MySQL created different sizes)
      • simple upgrade with no extra space requirements

      Potential problems:

      • mysql is painfully slow when modifying tables, it is hard to predict the time necessary because each row may take different amount of space

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            Petr Skoda added a comment -

            My test upgrade benchmarks for different tables:
            1/ 1 000 000 records / 40MB - 5 seconds
            2/ 26 000 records / 1.4GB - 33 seconds
            3/ 260 000 records / 14.2GB - 430 seconds

            Show
            Petr Skoda added a comment - My test upgrade benchmarks for different tables: 1/ 1 000 000 records / 40MB - 5 seconds 2/ 26 000 records / 1.4GB - 33 seconds 3/ 260 000 records / 14.2GB - 430 seconds
            Hide
            Eloy Lafuente (stronk7) added a comment -

            Here we go... integrated!

            Two side comments:

            1) As far as the main timing problem is that mysql has to make one full copy of the table to perform the change... I was thinking if it won't be better to accumulate all the changes within one table together. If I'm not wrong the ALTER TABLE should support multiple MODIFY (comma-separated). Other tables do that. Note that this is applicable both to the "UNSIGNED" changes the other day and also to the "TEXT/BINARY LENGTHs".

            More yet... my not, being ambitious... we put together both the unsigned and the text sizes? I think it's a really easy way to reduce times drastically (2x guaranteed and more depending of the number of columns changed together).

            For your consideration but I think it's worth thinking a bit about that.

            2) Minor detail: We marked the unsigned thing to be out in 2.4 and have marked this to be out in 2.5... surely the same is ok both both IMO.

            Anyway, this has landed...ciao

            Show
            Eloy Lafuente (stronk7) added a comment - Here we go... integrated! Two side comments: 1) As far as the main timing problem is that mysql has to make one full copy of the table to perform the change... I was thinking if it won't be better to accumulate all the changes within one table together. If I'm not wrong the ALTER TABLE should support multiple MODIFY (comma-separated). Other tables do that. Note that this is applicable both to the "UNSIGNED" changes the other day and also to the "TEXT/BINARY LENGTHs". More yet... my not, being ambitious... we put together both the unsigned and the text sizes? I think it's a really easy way to reduce times drastically (2x guaranteed and more depending of the number of columns changed together). For your consideration but I think it's worth thinking a bit about that. 2) Minor detail: We marked the unsigned thing to be out in 2.4 and have marked this to be out in 2.5... surely the same is ok both both IMO. Anyway, this has landed...ciao
            Hide
            Petr Skoda added a comment -

            hmmm, good idea to change more columns at the same time, I did not realise it could work, I will test it and rewrite the upgrade code if necessary.

            Show
            Petr Skoda added a comment - hmmm, good idea to change more columns at the same time, I did not realise it could work, I will test it and rewrite the upgrade code if necessary.
            Hide
            Eloy Lafuente (stronk7) added a comment -

            Adding the docs_required here as far as we need to warn about the long-upgrade process for big sites running under mysql. I think it's ok to do that in the "Upgrading to Moodle xxxx" page (and perhaps too a brief comment in release notes).

            (suggested by SamH) Ciao

            Show
            Eloy Lafuente (stronk7) added a comment - Adding the docs_required here as far as we need to warn about the long-upgrade process for big sites running under mysql. I think it's ok to do that in the "Upgrading to Moodle xxxx" page (and perhaps too a brief comment in release notes). (suggested by SamH) Ciao
            Hide
            Sam Hemelryk added a comment -

            Ok I've completed all tests except for running the functional DB tests on sqlsrv and oracle.
            I'm stopping for lunch but will continue trying to get those two installed and tested afterwards.

            Cheers
            Sam

            Show
            Sam Hemelryk added a comment - Ok I've completed all tests except for running the functional DB tests on sqlsrv and oracle. I'm stopping for lunch but will continue trying to get those two installed and tested afterwards. Cheers Sam
            Hide
            Sam Hemelryk added a comment -

            Passing this now (Eloy ran the functional tests for me on oracle and mssql)

            Show
            Sam Hemelryk added a comment - Passing this now (Eloy ran the functional tests for me on oracle and mssql)
            Hide
            Eloy Lafuente (stronk7) added a comment -

            FCT (fixed, closing, thanks). Ciao

            "I feel a very unusual sensation - if it is not indigestion, I think it must be gratitude!"
            ~ Benjamin Disraeli

            Show
            Eloy Lafuente (stronk7) added a comment - FCT (fixed, closing, thanks). Ciao "I feel a very unusual sensation - if it is not indigestion, I think it must be gratitude!" ~ Benjamin Disraeli
            Hide
            Helen Foster added a comment -

            Please could anyone advise me on what exactly should be added to the user docs. I added the issue to http://docs.moodle.org/dev/Moodle_2.3_release_notes

            Show
            Helen Foster added a comment - Please could anyone advise me on what exactly should be added to the user docs. I added the issue to http://docs.moodle.org/dev/Moodle_2.3_release_notes
            Hide
            Petr Skoda added a comment -

            Hi Helen, everything is automatic, developers just open the install.xml file in editor are resave it, admins do not need to know anythings. If anybody already increased the sizes in previous versions no problem either.

            Show
            Petr Skoda added a comment - Hi Helen, everything is automatic, developers just open the install.xml file in editor are resave it, admins do not need to know anythings. If anybody already increased the sizes in previous versions no problem either.
            Hide
            Helen Foster added a comment -

            Thanks Petr, removing the docs_required label then.

            Show
            Helen Foster added a comment - Thanks Petr, removing the docs_required label then.

              People

              • Votes:
                3 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: