EPrints Technical Mailing List Archive

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

Message: #09824

< 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 Matthew,
OK, I had loss track of the question.  I was just advising on how to most quickly achieve a re-import.
EPrints has an inherent problem with row width but this only usually impacts repository that have added all the "bells and whistles" over many years.  My best advice would be to cull fields (i.e. remove from all config/code) from your EPrints repository that are unused and then manually remove these from all the eprint... tables they appear.  This could be dangerous, so I would make sure you have good backups and ideally test this on a clone (e.g. test server) of your EPrints repository server.
There are different types of InnoDB table engines and different installations/configurations of MySQL / MariaDB will allow different row widths.  I am by no means an expert on this, so I will have to defer to Google, as I would not want to take responsibility for a solution that may work in a narrow case I have used it for in the past but may be unsuitable in your case.  Here may be a good place to start your investigations: 

https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Finnodb-parameters.html%23sysvar_innodb_page_size&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=pFzsFrL9SAc3Q2zdnKTAgvd72Ff%2FtvN48BLtV19PRsI%3D&reserved=0

Regards

David Newman

On 22/08/2024 4:09 am, Matthew Kerwin wrote:

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

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

Hi all,

Surely a table that's too wide for the engine will be too wide no
matter how you define it.

I recently converted our eprint__ordervalues_en table (the last MyISAM
table in the database) to InnoDB by manually identifying unused
fields/columns/etc. and purging them from the DB schema.

Cheers

On Wed, 21 Aug 2024 at 23:46, David R Newman <drn@ecs.soton.ac.uk> wrote:

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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsupport.severalnines.com%2Fhc%2Fen-us%2Farticles%2F212426983-Migrate-MyISAM-tables-to-INNODB-using-mysqldump&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=zhXHzrF0VkBVo%2FII34wvlFzsElV9zN6GIwBLvZz5u6k%3D&reserved=0

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:

https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FConverting_MyISAM_tables_to_InnoDB&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=sMs6gaA6R%2BNM0LrkByKyYdaLZGU6V0nxYrKaeOS%2Bi3o%3D&reserved=0

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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FEprints-tech_Mailing_List&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=2aQ%2FX72IioqHBoS4jRTAhQK2gO25DNCvR6P9aq3aAAk%3D&reserved=0
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=DDsW51HwEi6CM%2BLImzBUmJB1rubSp7%2BUt8WMh98x0%2Bg%3D&reserved=0
  /tech.php/
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=0giQDxuxl2D%2B%2BvjdYZlNpRYGUicFR8BSZkvaEZVKP%2Fk%3D&reserved=0


*** Options: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FEprints-tech_Mailing_List&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538314506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=2aQ%2FX72IioqHBoS4jRTAhQK2gO25DNCvR6P9aq3aAAk%3D&reserved=0
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=4gzltWvh87GWFUgeTacRBVBQFb4hPtlbd%2B4C1Ln4wHI%3D&reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=o5yAyA5wAADTfllwgCkqO9Bmjx0q%2FqyncWqwqaZQQCY%3D&reserved=0


*** Options: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FEprints-tech_Mailing_List&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=6OA56VcTZ9fE%2Bu1CFRp0ex%2FdZlN2E2pBp5Ze4nOaAqc%3D&reserved=0
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=4gzltWvh87GWFUgeTacRBVBQFb4hPtlbd%2B4C1Ln4wHI%3D&reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=o5yAyA5wAADTfllwgCkqO9Bmjx0q%2FqyncWqwqaZQQCY%3D&reserved=0


*** Options: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FEprints-tech_Mailing_List&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=6OA56VcTZ9fE%2Bu1CFRp0ex%2FdZlN2E2pBp5Ze4nOaAqc%3D&reserved=0
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=4gzltWvh87GWFUgeTacRBVBQFb4hPtlbd%2B4C1Ln4wHI%3D&reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=o5yAyA5wAADTfllwgCkqO9Bmjx0q%2FqyncWqwqaZQQCY%3D&reserved=0



--
   Matthew Kerwin [he/him]
   https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmatthew.kerwin.net.au%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=mno9ScndUPQPw%2FA76gCh20nRC4Od7GF3vzvx9QsQf%2F4%3D&reserved=0

*** Options: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FEprints-tech_Mailing_List&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=6OA56VcTZ9fE%2Bu1CFRp0ex%2FdZlN2E2pBp5Ze4nOaAqc%3D&reserved=0
*** Archive: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=4gzltWvh87GWFUgeTacRBVBQFb4hPtlbd%2B4C1Ln4wHI%3D&reserved=0
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2F&data=05%7C02%7Ceprints-tech%40ecs.soton.ac.uk%7C44d15193323d40d1d3f108dcc28267cb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C638599112538470794%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=o5yAyA5wAADTfllwgCkqO9Bmjx0q%2FqyncWqwqaZQQCY%3D&reserved=0