Difference between revisions of "EPrints and MariaDB 10"
m (→Data Too Long for Field) |
m (typo removed) |
||
| (2 intermediate revisions by 2 users not shown) | |||
| Line 2: | Line 2: | ||
== Not Null without Default Value == | == 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 | + | 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 MetaFields 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. [[EPrints_and_MySQL_8 | MySQL 8]] also has the same issue. |
| − | If you are running an earlier version of EPrints, you could manually alter your database to make sure any | + | If you are running an earlier version of EPrints, you could manually alter your database to make sure any MetaField 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 ''; | 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: | 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 10: | Line 10: | ||
== Data Too Long for Field == | == Data Too Long for Field == | ||
| − | EPrints often uses <tt>VARCHAR(255)</tt> 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 MariaDB 10 (as well as MySQL 8) it now just throws | + | EPrints often uses <tt>VARCHAR(255)</tt> 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 MariaDB 10 (as well as [[EPrints_and_MySQL_8 | MySQL 8]]) it now just throws an error message. |
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. | 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. | ||
| Line 16: | Line 16: | ||
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ||
| − | Like this other issue it is <tt>STRICT_TRANS_TABLES</tt> that will | + | 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 long are unlikely ever to be those searched. |
[[Category:Installation]] | [[Category:Installation]] | ||
Latest revision as of 08:47, 8 February 2024
Various changes have been made to MariaDB between version 5.5 and 10 and these can have some impact on EPrints.
Not Null without Default Value
In EPrints a MetaField can set the attribute allow_null => 0. This means strictly it has has to have a default value. Prior to EPrints 3.4.4, default values were not set for MetaFields created with allow_null => 0 and not until EPrints 3.4.5 were the default values tailored depending on the type of database field. MySQL 8 also has the same issue.
If you are running an earlier version of EPrints, you could manually alter your database to make sure any MetaField 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_AUTO_CREATE_USER,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 MariaDB 10 (as well as MySQL 8) it now just throws an 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_AUTO_CREATE_USER,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 long are unlikely ever to be those searched.
