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

Maximum index length is incorrect

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.8.4, 3.9.1, 3.10
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE

      Description

      This issue relates to the following code lib/xmldb/xmldb_index.php::validateDefinition():
      https://github.com/moodle/moodle/blob/master/lib/xmldb/xmldb_index.php#L364-L370

      The following constant is set to indicate the maximum bytes size of any created index:

          const INDEX_MAX_BYTES = 765;
      

      And it is used accordingly:

                      case XMLDB_TYPE_CHAR:
                          if ($field->getLength() > self::INDEX_MAX_BYTES / 3) {
                              return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_CHAR field "'.$field->getName().'" can not be indexed because it is too long.'
                                      .' Limit is '.(self::INDEX_MAX_BYTES/3).' chars.';
                          }
                          $total += ($field->getLength() * 3); // the most complex utf-8 chars have 3 bytes
                          break;
      

      There are a couple of things going on here:

      1. The INDEX_MAX_BYTES is set to the lower of our Database engines:
        1. MySQL: historically the maximum size of an index in MySQL was 767 bytes (https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html). That's because we used the COMPACT/REDUNDNAT row formats. In MDL-48228 we moved from Antelope file format to Barracuda as a requirement, and moved tables to use the DYNAMIC/COMPRESSED row formats instead. This means that the index key prefix limit in MySQL is now 3,072 bytes.
        2. MS SQL Server: The maximum bytes per index key is 900 bytes for a clustered index, or 1,700 for a non-clustered index.
          We use clustered indexes because we create indexes with:

          CREATE INDEX ...
          

        3. Oracle The maximum key length is 6,398 bytes
        4. Postgres uses a B-tree index, and the maximum length of ra key is 1/3 the buffer page size (8192) making the maximum index size 2,730 bytes
      2. UTF8 requires 3 bytes per character, but we now require utf8mb4 which requires 4 bytes per character
        So the math is wrong.

      Our new lowest index length is with sqlsrv at 900 bytes.
      Our new encoding is utf8mb4 at up-to 4 bytes per character

      Therefore we need to update the index validation to these values, giving a maximum key length of 225 characters, a reduction from 255.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            dobedobedoh Andrew Nicols
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: