Common MySQL Problems with EPrints
There are a number of common problems that EPrints can have with MySQL, which often result from the increasing size and complexity of an EPrints repository.
Row size too large
Example error message
DBD::mysql::st execute failed: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. at /opt/eprints3/bin/../perl_lib/EPrints/Database.pm line 1185.
Reason for error
Many of the
eprint data object fields are set to type text, which leads to then being created as
VARCHAR(255) columns in the
eprint table in the archive's MySQL database. A
VARCHAR(255) 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
VARCHAR 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
eprint 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
eprint for bespoke requirements of the archive.
The reason for EPrints creating so many
VARCHAR(255) MySQL columns is due to fields of type
text added to a data object being created as
VARCHAR(255) MySQL columns rather than
TEXT. There are various differences between the types of MySQL columns, most noticeably
TEXT can store up to 65535 characters rather than just 255. To faciltate 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 VARCHAR(255) if this contains a long string of characters. However, there are a number of downsides to using a
TEXT MySQL column:
- No default value other than
NULLcan 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 effect 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
sql_indexattribute 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.
Currently the only way to fix this problem is to manually modify various
VARCHAR(255) columns in the
eprint and make them
TEXT or possibly
TINYTEXT that would make them the same length as the original
VARCHAR(255) column. The columns you should choose are those most likely to use a large number of characters. This may include fields like
One potential issue with doing this is that if you were to recreate the database (say on a test instance of your repository), these fields would be recreated as
VARCHAR(255) rather than
TEXT. Currently, there is no way to define a field in EPrints that will that will create a MySQL
TEXT column. Therefore, for simplicity it may be easier to change the attribute type of these field to
type => 'longtext' in the EPrints configuration for this field, even though this allow text strings over 4 million characters long (although UTF-8 encoding would only allow strings up to a third of this length). To complete this change you would also need to manually run the following MySQL command to update the MySQL table column, e.g.
ALTER TABLE eprint MODIFY COLUMN publication LONGTEXT DEFAULT NULL;
Data length too long for column
DBD::mysql::st execute failed: Data too long for column 'volume' at row 1 at /usr/share/eprints/perl_lib/EPrints/Database.pm line 1187.