Editing Common MySQL Problems with EPrints (section)

=== Reason for error ===
The above error message will occur when running <code>EPRINTS_PATH/bin/epadmin update ARCHIVEID</code>.  By default current versions of MySQL installed by Linux package managers (e.g. YUM, APT, etc.) only allow 64 keys per MySQL database table. A key is added when a <code>CREATE INDEX</code> command is run and EPrints will do this for each new field added to a data object where the <code>sql_index</code> attribute is set to 1.  However, as the generic default value for this attribute is 1, only if a specific type of field sets this default value to 0, will a field be created without an index.  This is the case for <code>text</code>, <code>longtext</code>, <code>keywords</code>, <code>secret</code> and <code>storable</code> type fields and their sub-types.  However, this is not the case for other types, which is in most cases appropriate, as it cannot take into account in what way and how frequently the field will be used and therefore if the MySQL column would really benefit from an index.

The reason <code>sql_index</code> has the generic default value of 1 is that having a field indexed should speed up database queries, particularly when results need to be ordered.  It was felt it was unlikely that repository system administrators would explicitly set the <code>sql_index</code> to 1 for new fields they would be creating, as they may not appreciate the reasons for doing so.  Therefore, a lot of fields that would benefit from an index could have ended up not getting one.  However, particularly with highly bespoke EPrints archives, the number of columns that require indexes in the <code>eprint</code> MySQL table has grown close if not beyond the limit of 64 keys, prompting the error message shown above.