EPrints and MySQL 8

Various changes have been made to MySQL between version 5.7 and 8 and these can have some impact on EPrints.

Slow Performance

By default EPrints now uses InnoDB as its table engine. As EPrints can make a lot of transactions to update a eprint record whilst in the workflow, as well as complex background tasks this means the default configuration of MySQL makes EPrints rather slower than before. This can be fixed by adding the following line to /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_flush_log_at_trx_commit=2

What this change does is ensure the logs are flushed once a second rather than after each transaction commit. This is not ideal as your bin logs could potential miss transactions if the database was to crash but does significantly improve the performance of EPrints. Further investigation is required to determine how serious an impact lost transactions from the bin logs would be for EPrints. At worse this would likely affect only a small number of eprints.

Not Null without Default Value

In EPrints a MetaField can set the attribute allow_null => 0. This means strictly it has to have a default value. Prior to EPrints 3.4.4, default values were not set for MetaField's created with allow_null => 0 and not until EPrints 3.4.5 were the default values tailored depending on the type of database field. MariaDB 10 also has the same issue.

If you are running an earlier version of EPrints, you could manually alter your database to ensure that any MetaFields that have allow_null => 0 and set a default value something like:

ALTER TABLE `eprint` MODIFY COLUMN `not_null_field` VARCHAR(255) NOT NULL DEFAULT ;

Otherwise, you could change your MySQL server's configuration to overlook this by adding the following to /etc/mysql/mysql.conf.d/mysqld.cnf (and affectively removing the part of STRICT_TRANS_TABLES that causes this to error). Once done remember to restart MySQL:

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Data Too Long for Field

EPrints often uses VARCHAR(255) fields. This can sometime present a problem especially if the text contains special characters which in affect makes the column even less than 255 characters. Previously MySQL has just truncated data that has got to long but in MySQL 8 (as well as MariaDB 10) it now just throws and error message.

The main place in EPrints this is a problem is indexing metadata and the full text of documents. Since EPrints 3.4.5, a restriction on the max length of a word that can be indexed has been added to restrict this to 128 characters, this ensures that even if special characters are used the total space required to store the string does not exceed that available to a VARCHAR(255) by some margin.

However, if you are running an earlier version of EPrints, the only solution for now is the same as for the "Not Null without Default Value" issue and setting the SQL mode in /etc/mysql/mysql.conf.d/mysqld.cnf as follows and then restarting MySQL:

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Like this other issue it is STRICT_TRANS_TABLES that will throw an error rather than quietly truncate, which although also not ideal is generally less problematic. As terms that are too longer are unlikely ever to be those searched.

Deprecation Warnings

Some of the more recent versions of MySQL state that some of the options in sql_mode have been deprecated. In particular: NO_ZERO_IN_DATE, NO_ZERO_DATE and ERROR_FOR_DIVISION_BY_ZERO. Therefore, you should additional remove these to leave SQL mode in /etc/mysql/mysql.conf.d/mysqld.cnf looking like:

 sql_mode=ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION