EPrints Technical Mailing List Archive

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

Message: #09820

< 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 Yuri,

That is what I would do. (i.e. Dump out the database run a sed over the dump and write into a new file.  Then drop and recreate the database before reimporting using the new file).  I would also recommend using these options (in /etc/my.cnf or under the directory: /etc/my.cnf.d/) whilst you import InnoDB tables.  As large tables (e.g. access and eprint__rindex) can take some time to import:
[mysqld]
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
Assuming you are not doing anything else on the server (you will have presumably stopped Apache or just having it serving a static maintenance page), you can up innodb_buffer_pool_size to 1GB less that the total RAM on the server.  Obviously once you have changed these settings you will need to restart MySQL (or MariaDB) before importing and once the database is imported, you should remove these settings and restart MySQL.

Regards

David Newman

On 21/08/2024 1:42 pm, Yuri wrote:
CAUTION: This e-mail originated outside the University of Southampton.
CAUTION: This e-mail originated outside the University of Southampton.

Hi!

 instead of "convert", can't you just dump the sql and change the sql engine? Something like this:

https://support.severalnines.com/hc/en-us/articles/212426983-Migrate-MyISAM-tables-to-INNODB-using-mysqldump

Il 06/08/24 11:09, James Kerwin ha scritto:
CAUTION: This e-mail originated outside the University of Southampton.
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/



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



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