EPrints and MySQL 8 - Revision history

Th.lauke@arcor.de: link added

2024-02-19T13:26:07Z

link added

Th.lauke@arcor.de: typos removed

2023-12-28T14:40:22Z

typos removed

Drn@ecs.soton.ac.uk: Added section about deprecation warnings

2023-08-23T21:13:53Z

Added section about deprecation warnings

Drn@ecs.soton.ac.uk: Removed NO_AUTO_CREATE_USER

2023-08-22T20:41:37Z

Removed NO_AUTO_CREATE_USER

Drn@ecs.soton.ac.uk at 20:12, 22 August 2023

2023-08-22T20:12:04Z

Drn@ecs.soton.ac.uk: Added category

2023-06-06T11:46:45Z

Added category

Drn@ecs.soton.ac.uk: Added Data Too Long

2023-06-06T11:44:18Z

Added Data Too Long

Drn@ecs.soton.ac.uk: Added Not Null without Default Value issue

2023-06-06T11:18:41Z

Added Not Null without Default Value issue

Drn@ecs.soton.ac.uk: Added info about innodb_flush_log_at_trx_commit

2023-06-06T10:51:12Z

Added info about innodb_flush_log_at_trx_commit

New page

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

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 <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt>:
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.

@@ Line 7: / Line 7: @@
 == Not Null without Default Value ==
-In EPrints a MetaField can set the attribute <tt>allow_null => 0</tt>.  This means strictly it has has to have a default value.  Prior to EPrints 3.4.4, default values were not set for MetaField's created with <tt>allow_null => 0</tt> and not [https://github.com/eprints/eprints3.4/issues/196 until EPrints 3.4.5] were the default values tailored depending on the type of database field.  MariaDB 10 also has the same issue.
+In EPrints a MetaField can set the attribute <tt>allow_null => 0</tt>.  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 <tt>allow_null => 0</tt> and not [https://github.com/eprints/eprints3.4/issues/196 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 make sure any MetaFields that have <tt>allow_null => 0</tt> and set a default value something like:
+If you are running an earlier version of EPrints, you could manually alter your database to ensure that any MetaFields that have <tt>allow_null => 0</tt> 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 <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt> (and affectively removing the part of <tt>STRICT_TRANS_TABLES</tt> that causes this to error).  Once done remember to restart MySQL:

@@ Line 22: / Line 22: @@
   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 <tt>STRICT_TRANS_TABLES</tt> 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.
 [[Category:Installation]]

@@ Line 12: / Line 12: @@
   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 <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt> (and affectively removing the part of <tt>STRICT_TRANS_TABLES</tt> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+  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 ==
@@ Line 19: / Line 19: @@
 The main place in EPrints this is a problem is indexing metadata and the full text of documents.  Since [https://github.com/eprints/eprints3.4/issues/247 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 <tt>VARCHAR(255)</tt> 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|Not Null without Default Value]]" issue and setting the SQL mode in <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt> as follows and then restarting MySQL:
+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|Not Null without Default Value]]" issue and setting the SQL mode in <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+  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 <tt>STRICT_TRANS_TABLES</tt> 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.
 [[Category:Installation]]

@@ Line 21: / Line 21: @@
 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|Not Null without Default Value]]" issue and setting the SQL mode in <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-Like this other issue it is <tt>STRICT_TRANS_TABLES</tt> that will through 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.
+Like this other issue it is <tt>STRICT_TRANS_TABLES</tt> 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.
 [[Category:Installation]]

← Older revision		Revision as of 11:46, 6 June 2023
Line 22:		Line 22:
	sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
	Like this other issue it is <tt>STRICT_TRANS_TABLES</tt> that will through 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.		Like this other issue it is <tt>STRICT_TRANS_TABLES</tt> that will through 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.
		+
		+	[[Category:Installation]]

@@ Line 2: / Line 2: @@
 == 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 <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt>:
+By default EPrints now uses [[Converting_MyISAM_tables_to_InnoDB|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 <tt>/etc/mysql/mysql.conf.d/mysqld.cnf</tt>:
   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.