Editing Common MySQL Problems with EPrints (section)

=== Reason for error ===
Many of the <code>eprint</code> data object fields are set to type text, which leads to then being created as <code>VARCHAR(255)</code> columns in the <code>eprint</code> table in the archive's MySQL database.  A <code>VARCHAR(255)</code> MySQL column can take up to 768 bytes of the maximum permitted 8126 bytes.  768 bytes are potentially required as each character in standard UTF-8 encoding takes three bytes.  However, if a <code>VARCHAR</code> is empty it will only require 1 byte and more often than not it will only sue a fraction of the characters available.  Unfortunately, this means if there is an unusual <code>eprint</code> record that uses a lot of characters in a lot of fields then it can result in an error like the one above.  This becomes more likely to occur as additional fields are added to the <code>eprint</code> for bespoke requirements of the archive.

The reason for EPrints creating so many <code>VARCHAR(255)</code> MySQL columns is due to fields of type <code>text</code> added to a data object being created as <code>VARCHAR(255)</code> MySQL columns rather than <code>TEXT</code>.  There are various differences between the types of MySQL columns, most noticeably <code>TEXT</code> can store up to 65535 characters rather than just 255.  To facilitate storing more characters it stores these as a reference rather than in the row itself and therefore only use two bytes for this reference, using up a lot less space in the row than a <code>VARCHAR(255)</code> if this contains a long string of characters.  However, there are a number of downsides to using a <code>TEXT</code> MySQL column:
# No default value other than <code>NULL</code> can be set.  This does not affect EPrints as any requirement for default values is captured in its Perl-based configuration.
# The column is not fully indexed.  This may affect how efficiently EPrints can carry our MySQL queries that are determinant on the value of this column.  However, when EPrints creates new fields it typically creates an independent index, unless the field's <code>sql_index</code> attribute is set to 0.
# A slightly increased latency on recovering data via the reference.  This would have no noticeable effect on the responsiveness of EPrints.