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
    • Rank:
      38653

      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

        Issue Links

          Activity

          Hide
          Petr Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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: