[EP-tech] enlarging of text field fails?

[David R Newman]

Hi Thomas,

The problem you have is that there are too many VARCHAR columns in the 
eprint table.  A lot of highly bespoke repositories have started 
suffering from this issue as more and more fields have been added to the 
eprint data object.  The easiest way to deal with this is to either 
shrink down the size of existing VARCHAR columns or to convert VARCHAR 
columns (particularly any VARCHAR(255)) to LONGTEXT fields.  Both of 
these require manually running "ALTER TABLE" MySQL commands.  If you do 
the latter, which I would choose, you should also change the field in 
the EPrints configuration to type => 'longtext'. This will ensure if you 
ever have to recreate the eprint table using "epadmin create_db" you 
will get the same types of columns for those that you have changed.

This is not an ideal situation and it has something we have been looking 
at fixing for a while but there are many potential solutions.  Some are 
more difficult to implement than others but the main problem would be 
making sure existing repositories could be updated to resolve this issue 
and stop it occurring as more and more new fields are added in the 
future.  Feel free to ask for more details about how to modify the 
columns for the eprint table in MySQL.  However, my first piece of 
advice is make sure you backup the database before making any changes.

Regards

David Newman

On 10/08/2021 18:03, th.lauke--- via Eprints-tech wrote:
> *CAUTION:* This e-mail originated outside the University of Southampton.
> Dear all,
>
> after enlarging of 'volume' to
> {
>         name => 'volume',
>         type => 'text',
>         maxlength => 9,
> },
> updating the repository and restarting apache I got the following 
> error (even for a length of 7 ...):
>
> DBD::mysql::st execute failed: Data too long for column 'volume' at 
> row 1 at /usr/share/eprints/perl_lib/EPrints/Database.pm line 1187.
> UPDATE `eprint` SET `volume`=?, `date_year`=?, `date_month`=?, 
> `date_day`=?, `lastmod_year`=?, `lastmod_month`=?, `lastmod_day`=?, 
> `lastmod_hour`=?, `lastmod_minute`=?, `lastmod_second`=?, 
> `edit_lock_until`=? WHERE `eprintid`='9795': 1406 at 
> /usr/share/eprints/perl_lib/EPrints/Database.pm line 1595.
> EPrints::Database::update(EPrints::Database::mysql=HASH(0x55ca085bf8b0), 
> EPrints::DataSet=HASH(0x55ca03701e78), HASH(0x55ca0865be00), 
> HASH(0x55ca08669a80)) called at 
> /usr/share/eprints/perl_lib/EPrints/DataObj.pm line 720
> EPrints::DataObj::commit(EPrints::DataObj::EPrint=HASH(0x55ca086699a8), 
> undef) called at /usr/share/eprints/perl_lib/EPrints/DataObj/EPrint.pm 
> line 1028
> EPrints::DataObj::EPrint::commit(EPrints::DataObj::EPrint=HASH(0x55ca086699a8)) 
> called at /usr/share/eprints/perl_lib/EPrints/Workflow/Stage.pm line 221
> EPrints::Workflow::Stage::update_from_form(EPrints::Workflow::Stage=HASH(0x55ca0865b848), 
> EPrints::ScreenProcessor=HASH(0x55ca03298358)) called at 
> /usr/share/eprints/perl_lib/EPrints/Workflow.pm line 401
> EPrints::Workflow::update_from_form(EPrints::Workflow=HASH(0x55ca08675878), 
> EPrints::ScreenProcessor=HASH(0x55ca03298358)) called at 
> /usr/share/eprints/perl_lib/EPrints/Plugin/Screen/EPrint/Edit.pm line 152
> EPrints::Plugin::Screen::EPrint::Edit::action_save(EPrints::Plugin::Screen::EPrint::Edit=HASH(0x55ca0865bba8)) 
> called at /usr/share/eprints/perl_lib/EPrints/Plugin/Screen.pm line 240
> EPrints::Plugin::Screen::from(EPrints::Plugin::Screen::EPrint::Edit=HASH(0x55ca0865bba8)) 
> called at 
> /usr/share/eprints/perl_lib/EPrints/Plugin/Screen/EPrint/Edit.pm line 117
> EPrints::Plugin::Screen::EPrint::Edit::from(EPrints::Plugin::Screen::EPrint::Edit=HASH(0x55ca0865bba8)) 
> called at /usr/share/eprints/perl_lib/EPrints/ScreenProcessor.pm line 339
> EPrints::ScreenProcessor::process("EPrints::ScreenProcessor", 
> "session", EPrints::Repository=HASH(0x55ca06c5b660), "template", 
> undef, "url", "/cgi/users/home") called at 
> /usr/share/eprints/cgi/users/home line 43
> ModPerl::ROOT::ModPerl::Registry::usr_share_eprints_cgi_users_home::handler(Apache2::RequestRec=SCALAR(0x55ca067ce228)) 
> called at 
> /usr/lib/x86_64-linux-gnu/perl5/5.28/ModPerl/RegistryCooker.pm line 207
>         eval {...} called at 
> /usr/lib/x86_64-linux-gnu/perl5/5.28/ModPerl/RegistryCooker.pm line 207
> ModPerl::RegistryCooker::run(ModPerl::Registry=HASH(0x55ca02a9c850)) 
> called at 
> /usr/lib/x86_64-linux-gnu/perl5/5.28/ModPerl/RegistryCooker.pm line 173
> ModPerl::RegistryCooker::default_handler(ModPerl::Registry=HASH(0x55ca02a9c850)) 
> called at /usr/lib/x86_64-linux-gnu/perl5/5.28/ModPerl/Registry.pm line 32
>         ModPerl::Registry::handler("ModPerl::Registry", 
> Apache2::RequestRec=SCALAR(0x55ca067ce228)) called at -e line 0
>         eval {...} called at -e line 0
>
> What went wrong? What did I miss to adapt?
>
> Many thanks for any hint in advance
> Thomas
>
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cab74b42e08db4ac742b808d95c2f9edf%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637642181861390769%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=6urydcGb5AruAeoOPKd78tQLi%2Fypq03DMXdbcsDK0Hc%3D&reserved=0
> *** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cab74b42e08db4ac742b808d95c2f9edf%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637642181861390769%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=3GIoYvtTZHZYoCvQ9eaSqRMCjrj3CJ0iGwHhqF5DOEQ%3D&reserved=0
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20210810/c7613fc9/attachment-0001.html