Common MySQL Problems with EPrints

Beware
If you plan to follow any of the resolutions described below, make sure you have backed up the database so you can restore it if things go wrong.

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:

1. No default value other than NULL can be set. This does not affect EPrints as any requirement for default values is captured in its Perl-based configuration.
2. 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_index attribute is set to 0.
3. A slightly increased latency on recovering data via the reference. This would have no noticeable effect on the responsiveness of EPrints.

Resolution

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 publication, book_title id_number, event_title, publisher, contact_email, series, department, institution, place_of_pub and event_dates.

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 longtext (e.g. 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

Example error message

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.

Reason for error

This is due an field for a data object having its maxlength attribute increased but this will not have updated the size of the VARCHAR column in the MySQL table for the data object. The error message will appear in the webserver (e.g. Apache) error logs when a user sets a value that is longer than the original maxlength attribute value for a field. In the example error message above this is where the volume field for the eprint data object has been increased from its default maxlength of 6 to a higher value.

Resolution

To fix this issue you will need to manually modify the affected MySQL column. In the example error message above, if we assume you updated the maxlength attribute for the volume field of the eprint data object from 6 to 9, you would need to manually run the following MySQL command to update the database to correspond with the EPrints configuration for this field:

ALTER TABLE eprint MODIFY COLUMN volume VARCHAR(9) DEFAULT NULL;

Ideally running epadmin update ARCHIVE_NAME would update fields to effect changes to attributes like maxlength. Unfortunately, due to the extensive number or scenarios of how fields could be changed, implementing robust ways that will reliably modify MySQL columns, whilst ensuring data is not lost or worse tables becoming corrupted or inconisistent, is a very complex task. However, writing a script to do a consistency check between the field configuration for EPrints data objects and the structure of these tables in the MySQL database for the archive is being investigated.