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:
- The INDEX_MAX_BYTES is set to the lower of our Database engines:
- 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-48228we 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. - 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 nonclustered indexes because we create indexes with:CREATE INDEX ...
- Oracle The maximum key length is 6,398 bytes
- 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
- 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
- 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 1700 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 425 characters, a slight increase from 255.