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

Expand Database Identifier Sizes (Column names, Table names)

XMLWordPrintable

    • MOODLE_39_STABLE, MOODLE_400_STABLE, MOODLE_401_STABLE, MOODLE_402_STABLE, MOODLE_403_STABLE
    • MOODLE_311_STABLE, MOODLE_39_STABLE, MOODLE_400_STABLE, MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-76459_401
    • Hide

      Practically everything (editor, xmldb, dml and ddl) is covered by automated tests already. So automated testing will cover a lot.

      Only non-automated testing is about the new environmental check. Here there are the instructions.

      Note

      • This test needs to be performed with all branches (39, 311, 400, 401, 402 and master).
      • This can be tested with any database but with Oracle.

      Testing stable branches (39, 311, 400, 401 & 402).

      1. Install a MOODLE_XXX_STABLE site, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: "very_long_prefix".
      2. Verify that the installation ends ok.
      3. Verify (with phpadmin, adminer or any other tool) that the database tables have been created with the very_long_prefix prefix.
      4. Go to Admin -> Server -> Environment.
      5. Verify that there aren't errors.
      6. In the "Moodle version" drop down, select "Moodle 4.3 and upwards".
      7. Verify that an error (like the image attached to this issue) with text "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used "very_long_prefix". It can be different if another prefix has been used).
      8. If there are other versions in the "Moodle version" drop down... verify that only "4.3 and upwards" shows the error.
      9. Repeat all the steps above for the other branches.

      Testing master branch install.

      1. Install a master site via CLI, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: "very_long_prefix".
      2. Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used "very_long_prefix". It can be different if another prefix has been used).
      3. Install a master site via WEB, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: very_long_prefix.
      4. Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used "very_long_prefix". It can be different if another prefix has been used).

      Testing upgrade from stable to master.

      1. Install a 400_STABLE site via WEB, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: "very_long_prefix".
      2. Verify that installation is allowed and ends ok.
      3. Upgrade the site to 402_STABLE via WEB.
      4. Verify that installation is allowed and ends ok.
      5. Upgrade the site to master via WEB.
      6. Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used "very_long_prefix". It can be different if another prefix has been used).

      Verify environment.xml file is the same in all branches.

      1. Ensure that your local integration.git clone is updated and all the local branches contain the latest changes.
      2. Run the following commands, verifying that no differences are reported in any of them. 

        git diff MOODLE_39_STABLE master -- admin/environment.xml
        		 
        git diff MOODLE_311_STABLE master -- admin/environment.xml
        		 
        git diff MOODLE_400_STABLE master -- admin/environment.xml
        		 
        git diff MOODLE_401_STABLE master -- admin/environment.xml
        		 
        git diff MOODLE_402_STABLE master -- admin/environment.xml

      Show
      Practically everything (editor, xmldb, dml and ddl) is covered by automated tests already. So automated testing will cover a lot. Only non-automated testing is about the new environmental check. Here there are the instructions. Note This test needs to be performed with all branches (39, 311, 400, 401, 402 and master). This can be tested with any database but with Oracle. Testing stable branches (39, 311, 400, 401 & 402). Install a MOODLE_XXX_STABLE site, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: " very_long_prefix ". Verify that the installation ends ok. Verify (with phpadmin, adminer or any other tool) that the database tables have been created with the very_long_prefix prefix. Go to Admin -> Server -> Environment. Verify that there aren't errors. In the "Moodle version" drop down, select "Moodle 4.3 and upwards". Verify that an error (like the image attached to this issue) with text "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used " very_long_prefix ". It can be different if another prefix has been used). If there are other versions in the "Moodle version" drop down... verify that only "4.3 and upwards" shows the error. Repeat all the steps above for the other branches. Testing master branch install. Install a master site via CLI, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: " very_long_prefix ". Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used " very_long_prefix ". It can be different if another prefix has been used). Install a master site via WEB, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: very_long_prefix . Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used " very_long_prefix ". It can be different if another prefix has been used). Testing upgrade from stable to master. Install a 400_STABLE site via WEB, ensuring that the $CFG->prefix used is very long (more than 10 characters). For example: " very_long_prefix ". Verify that installation is allowed and ends ok. Upgrade the site to 402_STABLE via WEB. Verify that installation is allowed and ends ok. Upgrade the site to master via WEB. Verify that install doesn't happen and the "Your site's database prefix ($CFG->prefix) is too long (16 characters). The maximum number of characters allowed is 10." (note that the "16" is the number if you've used " very_long_prefix ". It can be different if another prefix has been used). Verify environment.xml file is the same in all branches. Ensure that your local integration.git clone is updated and all the local branches contain the latest changes. Run the following commands, verifying that no differences are reported in any of them. git diff MOODLE_39_STABLE master -- admin/environment.xml git diff MOODLE_311_STABLE master -- admin/environment.xml git diff MOODLE_400_STABLE master -- admin/environment.xml git diff MOODLE_401_STABLE master -- admin/environment.xml git diff MOODLE_402_STABLE master -- admin/environment.xml

      As of MDL-71747, requirements for Oracle have increased to 19c, which may remove the technical limitation of 30 characters for many identifiers. danielneis raised this possibility in MDL-65809. I don't know whether relaxing this limitation is strictly necessary, but a discussion should be had and hopefully a decision can be made.

      Current status of identifier limits is as follows:

      Database Tables name limit Columns name limit Documentation link
      MySQL 64 64 link
      MariaDB 64 64 link
      PostgreSQL 63 63 link
      SQL*Server 128 (116 for temp tables) 128 link
      Oracle 128 128 link

      Note: Some are chars, others are bytes/ascii, let's assume we won't be using emojis for table names, but just plain english.

      Once this is implemented:

      TODO 1: Update all the doc pages making reference to the 28 (table/index names) or 30 (column names) limits:

      TODO 2: Comment @ MDL-76724 (Moodle 4.3 requirements) about the new prefix limit.

      TODO 3: Add the new prefix max length to the Moodle 4.3 requirements page.

      TODO 4: (from 39 to 402) - Add the details about the check @ https://docs.moodle.org/401/en/admin/environment/custom_check/check_db_prefix_length (it's linked from the env page).

        1. env_check_db_prefix.png
          env_check_db_prefix.png
          35 kB
        2. env_check_db_prefix_take2.png
          env_check_db_prefix_take2.png
          31 kB
        3. (IV) Passed -- MDL-76459.png
          (IV) Passed -- MDL-76459.png
          102 kB
        4. (III) Passed -- MDL-76459.png
          (III) Passed -- MDL-76459.png
          195 kB
        5. (II) Passed -- MDL-76459.png
          (II) Passed -- MDL-76459.png
          107 kB
        6. (I) Passed -- MDL-76459.png
          (I) Passed -- MDL-76459.png
          227 kB

            stronk7 Eloy Lafuente (stronk7)
            jrchamp Jonathan Champ
            Andrew Lyons Andrew Lyons
            Ilya Tregubov Ilya Tregubov
            Kim Jared Lucas Kim Jared Lucas
            Votes:
            3 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 day, 2 hours, 55 minutes
                1d 2h 55m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.