EPrints Technical Mailing List Archive

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

Message: #09805

< 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

CAUTION: This e-mail originated outside the University of Southampton.
Morning David,

It certainly has a lot of custom/plugin fields. We were discussing the value of purging those we no longer use recently. From what you've said, I should probably switch those fields back to longtext! Currently it's a freshly built database with no data in it, so easily remedied.

To answer your questions:

1) MySQL version 8.0.39
2) There are 251 columns
3) I think it's telling me that the size is bigger than 8kb. The exact error is this:

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

I have managed to change it, but I feel very uneasy about how I've done it. I temporarily turned off "innodb_strict_mode". I turned it off, changed the engine for the table with no warnings/errors and then changed it back. I don't feel like this is the best way to do it as I'm concerned it might be broken when I turn Apache etc back on and try to use the repository. I'm just reading about increasing page size etc. this morning.

Thanks,
James

On Thu, Aug 1, 2024 at 4:52 PM David R Newman <drn@ecs.soton.ac.uk> wrote:

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/