Editing Common MySQL Problems with EPrints (section)

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