EPrints Technical Mailing List Archive

See the EPrints wiki for instructions on how to join this mailing list and related information.

Message: #09802

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] MyISAM to InnoDB

HI James,

This is an inherent issue with having an EPrints repository which has been heavily customised with lots of plugins and extra fields.  The eprint table gets lots of columns in it and you hit up against issues like exceeding the number of indexes you can have per table or the row length being too big in the eprint__ordervalues_en table.  The reason that a lot of these fields are long text is because they compress several sub-fields in the eprint table or multiple rows form other eprint_... tables ( e.g. eprint_creators_name).  Therefore it is almost impossible to know what fields would and would not fit into a regular database text field, so it is safer to use longtext.

I don't think changing the field from longtext to something else will make a huge amount of difference.  As a longtext field is just a reference, so the amount of space it takes up in the rowsize is quite small and that the difference between this and a text field is only a couple of bytes AFAIK.  

I have several question before I can advise further.

1. What version of MySQL or MariaDB are you running?

2. How many columns/fields are their in your eprint__ordervalues_en table? 

3. When you get an error about the rowsize being too large what size does it claim in is bigger than.  More recently the rowsize can go to a maximum of 64K but older databases this is restricted to 8KB. 

Regards

David Newman

On 01/08/2024 12:42, James Kerwin wrote:
CAUTION: This e-mail originated outside the University of Southampton.
CAUTION: This e-mail originated outside the University of Southampton.
Hi,

I'm attempting to convert my test repository database tables from MyISAM to InnoDB. I used the bash script:


It all went well, aside from one table: eprint__ordervalues_en. I'm using this as a dry run for doing the same on Live in the near future.

The error I get is about the rowsize being too large. Usually for this I would alter some column types to something smaller, but still suitable. All the columns in this table are of type "longtext".

I assume I'll eventually change enough fields that it allows me to change the database engine. Is there any danger in this? I'm not sure what this table does compared with the standard "eprint" table. Obviously I'm being careful to change to suitable types based on the data in those columns (e.g. a yes/no field doesn't require longtext, I'd use varchar(10) as that's enough to capture yes/no values).

Thanks,
James

*** Options: https://wiki.eprints.org/w/Eprints-tech_Mailing_List
*** Archive: https://www.eprints.org/tech.php/
*** EPrints community wiki: https://wiki.eprints.org/