Editing Common MySQL Problems with EPrints

[[Category:Work_in_Progress]]
[[Category:Documentation Needed‏‎]]
{{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 <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.

=== Resolution ===
Currently the only way to fix this problem is to manually modify various <code>VARCHAR(255)</code> columns in the <code>eprint</code> and make them <code>TEXT</code> or possibly <code>TINYTEXT</code> that would make them the same length as the original <code>VARCHAR(255)</code> column.  The columns you should choose are those most likely to use a large number of characters.  This may include fields like <code>publication</code>, <code>book_title</code> <code>id_number</code>, <code>event_title</code>, <code>publisher</code>, <code>contact_email</code>, <code>series</code>, <code>department</code>, <code>institution</code>, <code>place_of_pub</code> and <code>event_dates</code>.

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 <code>VARCHAR(255)</code> rather than <code>TEXT</code>.  Currently, there is no way to define a field in EPrints that will create a MySQL <code>TEXT</code> column.  Therefore, for simplicity it may be easier to change the attribute type of these field to <code>longtext</code> (e.g. <code>type => 'longtext'</code>) in the EPrints configuration for this field, even though this allows 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;
If you modify the type of MySQL column in the <code>eprint</code> table it would also be sensible to make sure that the equivalent column in the <code>eprint__ordervalues_en</code> (and other <code>eprint__ordervalues</code> tables if your archive supports multiple languages) is also updated to use <code>LONGTEXT</code>. (Although this is likely to be the case, unless the particular field was added to the archive some time ago).
 ALTER TABLE eprint__ordervalues_en MODIFY COLUMN publication LONGTEXT DEFAULT NULL;

As the example error messages says above, an alternative resolution to this problem could be to change the <code>ROW_FORMAT</code> to <code>DYNAMIC</code> or <code>COMPRESSED</code>.  However, (and assuming you are using [[Converting_MyISAM_tables_to_InnoDB|<code>InnoDB</code>]] as the MySQL table engine), to do this you need to have the MySQL configuration parameters <code>innodb_file_format</code> and <code>innodb_file_format_max</code> needs to be set to <code>Barracuda</code> rather than <code>Antelope</code>.  <code>Barracuda</code> does not become the default until MySQL 5.7 but earlier versions of MySQL should still support MySQL 5, so you can change to <code>Barracuda</code> by setting the following configuration settings under the <code>[mysqld]</code> section of <code>/etc/my.cnf</code> and restart MySQL:
 innodb_file_format=Barracuda
 innodb_file_format_max=Barracuda
 innodb_large_prefix=1
You can then alter the table to change the <code>ROW_FORMAT</code> as follows:
 ALTER TABLE eprint ROW_FORMAT=DYNAMIC;
Using <code>COMPRESSED</code> rather than <code>DYNAMIC</code> maybe useful for particularly large (multi-gigabyte) tables but there may be a performance trade-off to doing so. Therefore, if there is not a disk space constraint, <code>DYNAMIC</code> is probably the better choice.

If you use <code>ROW_FORMAT=DYNAMIC</code> you may still get the original "Row size too large" error message if you have more than 196 <code>LONGTEXT</code> columns whilst using certain versions of MySQL (e.g. 5.7).  If your repository has added a lot of bespoke fields for the eprint data object, there is a fair chance your <code>eprint__ordervalues_en</code> will be close to this 196 limit on <code>LONGTEXT</code> columns.  

It is likely that the eprint database tables will still include a number of fields that were created, never used and their definitions removed from the repository's configuration.  If this is the case, this may help alleviate the problem of hitting this 196 <code>LONGTEXT</code> columns limit, as these fields can be manually removed from the eprint database tables.  It is probably best to focus on removing single and non-compound rather than multiple compound fields, as the process to remove these is more straightforward.  In the examples below the field to remove (or column to drop) is called '''unused''':
 ALTER TABLE eprint DROP COLUMN unused;
 ALTER TABLE eprint__ordervalues_en DROP COLUMN unused;
If you want to remove a multiple (non-compound) field the MySQL commands are more like:
 DROP TABLE eprint_unused;
 ALTER TABLE eprint__ordervalues_en DROP COLUMN unused;
If it is a multiple and compound field the MySQL commmands will probably be something like:
 DROP TABLE eprint_unused;
 ALTER TABLE eprint__ordervalues_en DROP COLUMN unused_subfield1;
 ALTER TABLE eprint__ordervalues_en DROP COLUMN unused_subfield2;

== 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 to a field for a data object having its <code>maxlength</code> attribute increased but this will not have updated the size of the <code>VARCHAR</code> 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 <code>maxlength</code> attribute value for a field.  In the example error message above this is where the <code>volume</code> field for the <code>eprint</code> data object has been increased from its default <code>maxlength</code> 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 <code>maxlength</code> attribute for the <code>volume</code> field of the <code>eprint</code> data object from 6 to 9, you will 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 <code>epadmin update ARCHIVEID</code> would update fields to effect changes to attributes like <code>maxlength</code>.  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 inconsistent, 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 [https://github.com/eprints/eprints3.4/issues/163 is being investigated].

== Too many keys specified ==
=== Example error message ===
 #1069 - Too many keys specified; max 64 keys allowed

=== 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.

=== Resolution ===
There is a two-fold approach to this.  One part if to change the EPrints field configuration and set the <code>sql_index</code> attribute to 0 for types of field that have this set to 1 by default.  However, this will only be useful on the fields that are complained about when running <code>epadmin update</code>, as their indexes will not have been created due to the limit being reached and therefore you would just be removing the need for them being created.  However, some of these new fields may benefit from having indexes but some existing fields may not or at least benefit significantly less.  The best way to determine this is to look at the existing indexes for the MySQL table for the particular data object, typically <code>eprint</code>.  The following MySQL command can retrieve this information:
 SHOW INDEX FROM eprint;
Then there are a number of considerations about which fields could do without an index.  Generally, those that store numerical values where comparisons are required greatly benefit from an index, so date/time fields like <code>datestamp</code>, <code>lastmod</code> and <code>date</code> greatly benefit from an index, especially as it combines the six separate MySQL columns that make up these EPrint fields (i.e. year, month, day, hour minute and second).  The fields that are less important to have index are the ones that are sparsely used, so fields specific to a particular type of eprint that is not commonly used.  A rough guide is that if the field is used in your archive's advanced search or staff search for eprints, then you would not want to remove its index.  

The following standard <code>eprint</code> fields fit into the category of non-numeric and/or sparsely used, roughly in ascending order or use: <code>gscholar_cluster</code>, <code>gscholar_impact</code>, <code>template</code>, <code>pedagogic_type</code>, <code>num_pieces</code>, <code>succeeds</code>, <code>importid</code>, <code>monograph_type</code>, <code>sword_depositor</code>, <code>thesis_name</code>, <code>thesis_type</code>, <code>event_type</code>, <code>pres_type</code> and <code>contact_email</code>.  However, the reason you will have hit your limit of keys is due to non-standard fields that will have been added to the archive so it is worth reviewing which of these are used sparsely and do not contain numeric values that are likely to have comparators or other mathematical operations applied to them.

Once you have identified the fields that least need indexes as well as setting the <code>sql_index</code> attribute for the field to 0 (and reloading the webserver and EPrints indexer), you will need to make sure the index has been dropped from MySQL itself. Using a MySQL command like the following:
 DROP INDEX gscholar_cluster_0 ON eprint;
You will note that the index will have a slightly different name to the field.  You should the <code>SHOW INDEX</code> MySQL command above to determine the correct name for the index.  After doing this you should be able to add new fields without seeing the "Too many keys" error message.  However, be sure to set <code>sql_index</code> to 0 on new fields you add if they are of a type that creates an index but you expect them to be sparsely used.

== Duplicate entry for key ==
=== Example error message ===
 DBD::mysql::st execute failed: Duplicate entry '99288-1' for key 'PRIMARY' at /opt/eprints3/perl_lib/EPrints/Database.pm line 1287.

=== Reason for error ===
'''To be written'''
=== Resolution ===
'''To be written'''

== Deadlock found when trying to get lock ==
=== Example error message ===
'''To be added'''
=== Reason for error ===
'''To be written'''
=== Resolution ===
'''To be written'''

== MariaDB order without limit on subquery ==
=== Symptom of error ===
'''To be written'''
=== Reason for error ===
'''To be written'''
=== Resolution ===
'''To be written'''