Common MySQL Problems with EPrints - Revision history

Th.lauke@arcor.de: link/category added

2024-02-19T13:51:51Z

link/category added

Drn@ecs.soton.ac.uk at 00:36, 6 March 2022

2022-03-06T00:36:34Z

Drn@ecs.soton.ac.uk: /* Resolution */

2021-09-11T09:55:02Z

‎Resolution

Drn@ecs.soton.ac.uk: Added extra advice with dealing with too many longtext fields.

2021-09-11T09:54:23Z

Added extra advice with dealing with too many longtext fields.

Th.lauke@arcor.de: formatting/spelling corrected

2021-08-16T18:20:43Z

formatting/spelling corrected

Show changes

Drn@ecs.soton.ac.uk: Updated reason for error to explain about different fields types and whether sql_index is set to 0 or 1 by default.

2021-08-12T09:28:26Z

Updated reason for error to explain about different fields types and whether sql_index is set to 0 or 1 by default.

Drn@ecs.soton.ac.uk: Finished resolution for Too many keys.

2021-08-12T09:00:08Z

Finished resolution for Too many keys.

Drn@ecs.soton.ac.uk: Added advice about altering ordervalues_en tables as well as the data object (e.g. eprint) table

2021-08-12T07:46:17Z

Added advice about altering ordervalues_en tables as well as the data object (e.g. eprint) table

Drn@ecs.soton.ac.uk: /* Resolution */

2021-08-12T07:29:24Z

‎Resolution

Drn@ecs.soton.ac.uk: /* Resolution */

2021-08-12T01:00:05Z

‎Resolution

@@ Line 1: / Line 1: @@
 {{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.}}
@@ Line 22: / Line 24: @@
   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 <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:
+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

@@ Line 54: / Line 54: @@
   ALTER TABLE eprint MODIFY COLUMN volume VARCHAR(9) DEFAULT NULL;
-Ideally running <code>epadmin update ARCHIVE_NAME</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].
+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 ==
@@ Line 61: / Line 61: @@
 === Reason for error ===
-The above error message will occur when running <code>EPRINTS_PATH/bin/epadmin update ARCHIVE_NAME</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 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.

@@ Line 36: / Line 36: @@
   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 eprints_unused;
+  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 eprints_unused;
+  DROP TABLE eprint_unused;
   ALTER TABLE eprint__ordervalues_en DROP COLUMN unused_subfield1;
   ALTER TABLE eprint__ordervalues_en DROP COLUMN unused_subfield2;

@@ Line 29: / Line 29: @@
   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.
 == Data length too long for column ==

@@ Line 48: / Line 48: @@
 === Reason for error ===
-The above error message will occur when running <code>EPRINTS_PATH/bin/epadmin update ARCHIVE_NAME</code>.  By default current versions of MySQL installed by Linux package manager (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 for most types of field this is the default value for <code>sql_index</code> a lot of keys can be used, particularly by the <code>eprint</code> MySQL table.
+The above error message will occur when running <code>EPRINTS_PATH/bin/epadmin update ARCHIVE_NAME</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 most cases is appropriate, as it 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> typically has a 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, as they may not appreciate the reasons for doing so and therefore a lot of fields that would benefit from an index would not get one.  However, particularly with highly bespoke EPrints archives, the number of fields that require indexes in the <code>eprint</code> MySQL tabel has grown close if not beyond the limit of 64 keys, prompting the error message shown above.
+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 there 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 the 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 that information:
+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 there 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 with out an index.  Generally, those that store numerical values where comparison 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 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.
+Then there are a number of considerations about which fields could do without an index.  Generally, those that store numerical values where comparison 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 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.
+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:

@@ Line 19: / Line 19: @@
 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 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 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;
-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 <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:
+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 <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

@@ Line 17: / Line 17: @@
 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 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 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.
+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 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 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;