Editing Common MySQL Problems with EPrints (section)

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