[EP-tech] enlarging of text field fails?

[drn at ecs.soton.ac.uk (David R Newman)]

Hi Thomas,

I am going to create a wiki page to explain about this issue and others 
that I have encountered that directly relate to issues with EPrints 
working with MySQL.

I was just looking closer at your issue.? I was conflating the "row size 
too large" issue which would occur when when you added one too many 
VARCHAR ( type => 'text') fields and would prevent you from adding the 
new field and the "data length too long for column", which is where the 
fields are in place but the data inserted into one particular field, in 
this case volume, is too large.? This is because changing maxlength to 9 
will not update the database but will let EPrints allow you to enter a 
value longer value that the the VARCHAR size which has not been 
updated.? Therefore 7 characters would still be longer than the 6 
character length VARCHAR that would have been created in the eprint 
table, assuming you were using the default volume field definition 
originally.? Therefore, you will need to manually update the database to 
change from a VARCHAR(6) to a VARCHAR(9), something like:

ALTER TABLE eprint MODIFY COLUMN volume VARCHAR(9) DEFAULT NULL;

"epadmin update" will not modify fields/columns that have already been 
created.? I certainly remember having to increase the length of volume 
and similar fields but I did not recall at the time of your email, that 
once I had done this I then went and manually updated the eprint table 
like above.? I am not sure if there is a robust way to facilitate 
modification of existing fields using epadmin, as I can see this getting 
very complicated very quickly. The more complicated it get the more 
likely it is to either fail to carry out these field/column updates or 
worse still, break the table as a whole, potentially leading to data loss.

Regards

David Newman

On 10/08/2021 22:46, th.lauke at arcor.de wrote:
> CAUTION: This e-mail originated outside the University of Southampton.
>
> Hi David,
>
>> The problem you have is that there are too many VARCHAR columns in the eprint table.
> many thanks for your immediate response, although I would expecting this information from an "epadmin update" call ... :)
>
>> ... or to convert VARCHAR columns (particularly any VARCHAR(255)) to LONGTEXT fields.
> okay, no problem to manipulate the database in general, but ...
>
>> Both of these require manually running "ALTER TABLE" MySQL commands.
> ... I am more familiar with the MySQL commands than the database scheme.
> Please list in brief
> - which tables are typically involved
> - which commands do you recommend in which order
>
>> you should also change the field in the EPrints configuration to type => 'longtext'.
> for sure, this should be done simultaneously ...!
>
>> Feel free to ask for more details about how to modify the columns for the eprint table in MySQL.
> Maybe _we_ should add these instructions to the wiki?!
>
>> However, my first piece of advice is make sure you backup the database before making any changes.
> fortunately we have a daily backup and a separate server for tests :)
>
> Thanks in advance
> Thomas
>

-- 
This email has been checked for viruses by AVG.
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avg.com%2F&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cf16457837e6d4996b33308d95c5b72ba%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637642370093607958%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=czw%2F%2BZCTcZImzg1bzqs3hQl9917Rjr0gaFs4bIPlZwg%3D&reserved=0